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.
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.
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 .
Hi @Candersen ,
>>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)