Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So lets say I have 3 tables a DimUnit, DimUnitOption and a DimOption. I now want to find all of the unit that dont have option 12A. Unit 123 has options 35B and 12A, unit 456 has options 66F and 77X, Unit 789 has options 35B and 12A and unit 925 has options 89K and 5FR. In my results set I want to see units 456 and 925 as neither of these units has option12A. Thank You in advance.
DimUnit | DimUnitOption | DimOption | |
unitkey | unitkey | ||
123 | 123 | 35B | |
456 | 123 | 12A | |
789 | 456 | 66F | |
925 | 456 | 77X | |
789 | 35B | ||
789 | 12A | ||
925 | 89K | ||
925 | 5FR | ||
HI @Anonymous,
You can add a measure to compare with selected keys, then you can filter not matched records. Drag it to visual level filter to enable filter effect.
Seleceted = VAR _keylist = CALCULATETABLE ( VALUES ( DimOption[Unitkey] ), ALLSELECTED ( DimOption[Option] ) ) RETURN IF ( SELECTEDVALUE ( DimUnin[unitkey] ) IN _keylist, "Y", "N" )
Notice: break relationship to ignore filter effect or you will see blank result in visuals.
Regards,
Xiaoxin Sheng
I think this is going to work great. I have a little more testing to do though. So what if they wanted to select multiple options they did not want in a unit. Say they wanted all units that did not have 12A and did not have 5FR that would leave unit 456 .
Thank You
Hi @Anonymous ,
>>So what if they wanted to select multiple options they did not want in a unit. Say they wanted all units that did not have 12A and did not have 5FR that would leave unit 456 .
Can you explain these more detail?(share some sample data and expected result)
Regards,
Xiaoxin Sheng
Seleceted = VAR _keylist = CALCULATETABLE ( VALUES ( DimOption[Unitkey] ),
ALLSELECTED ( DimOption[Option] ) )
RETURN IF ( SELECTEDVALUE ( DimUnin[unitkey] ) IN _keylist, "Y", "N" )
This is so very close to working but it is ignoring the other slicers I need. For example "Series" or "Model". It is returning all of those that do not have the selected option and Ignoring all of the slicers. I tried to use AllExcept and Filter but neither worked. Thank You for your help
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |