Requirement is that I must provide two reason dropdowns from same reason column and when you select first drop down and have three reasons A,B,C and say vehicle 1 had accident due to B and C, vehicle 2 due to A and C and vehicle 3 due to C and I select B in dropdown 2, i should only have vehicle 2 and 3.
That is count when reasons A,B,C are selected in dropdown 1 is 3 right, but after selecting C in dropdown 2, count should be 2.
-------
For further clarity,
The second excel file shows the filtered data for the following explanation.
I need to calculate a metric that show count of vehicles.
So if you refer the excel file consisting of dummy data, when I filter fault A (say I select Fault A from the multi select accident reason/ fault dropdown, then I will get count of vehicles as 3 which are V,AA and EE.
Now if I select Fault C from second drop down, I should see count as 1 and only AA must appear since rest of vehicles have fault C also in addition to faultA.
I need the methodology for this bit urgently. Please help
@Vaishnavi Kilambi When providing sample data, you need to verify that there are no errors. As uploaded, there was only one vehicle that had 'Fault A' ('AA'). 'V' and 'EE' have 'FaultA'
In the future, you should also provided a packaged workbook (twbx) with some attempt to solve the question yourself instead of just providing Excel files.
You can accomplish this with sets and a couple FIXED Level of Detail calculations (LoDs).
Create two sets using [Accident Fault]. I used the names [S_Accident Cause Include] and [S_Accident Cause Exclude]
Create two LoDs to determine which vehicles to Include and which to Exclude
//C_Cars to Include
{ FIXED [Vehicle Name]:MAX([S_Accident Cause Include])}=TRUE
//C_Cars to Exclude
{ FIXED [Vehicle Name]:MIN([S_Accident Cause Exclude])}=FALSE
You could finish two different ways. The simplest is with a calculation filter with both the LoD values
//F_Cars (Final)
[C_Cars to Exclude]=FALSE
AND
[C_Cars to Include]=TRUE
//the '=TRUE' is optional because TRUE is assumed
Alternatively, you could create some more sets.
Each set would use one of the LoDs as a condition. Then you would create a combined set that includes members of INCLUDE except those shared with EXCLUDE.
