Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

"NOT" Slicer

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.

DimUnitDimUnitOption DimOption
unitkeyunitkey  
123123 35B
456123 12A
789456 66F
925456 77X
 789 35B
 789 12A
 925 89K
 925 5FR
    
    
4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.