Forum Discussion

shep123's avatar
shep123
Helper I
8 years ago
Solved

Exclude vs All Slicer

Admittedly this is a very simplified view of the data set but ultimately what I want to do is create a slicer that either includes Federal in the visuals or does not.   Order_ID Industry Amoun...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi shep123,

     

    You can create a Flag table which has only one column containing two values "Federal" and "NonFederal", then create a measure using DAX below:

    Count = 
IF (
    SELECTEDVALUE ( Flag[Flag] ) = "Federal",
    CALCULATE ( COUNT ( Table1[Order_ID] ), ALLEXCEPT ( Table1, Table1[Industry] ) ),
    IF (
        SELECTEDVALUE ( Flag[Flag] ) = "NoneFederal",
        IF (
            MAX ( Table1[Industry] ) <> "Federal",
            CALCULATE ( COUNT ( Table1[Order_ID] ), ALLEXCEPT ( Table1, Table1[Industry] ) ),
            BLANK ()
        )
    )
)


    PBIX here: https://www.dropbox.com/s/iqicxwd1ucu1zaq/Exclude%20vs%20All%20Slicer.pbix?dl=0

     

    Regards,

    Jimmy Tao

shep123's avatar
shep123
Helper I
8 years ago

That just groups it to Federal and Not Federal though. So my slicer options are 1 (Federal) or 0 (Not Federal). But what I want is 0 or all. Is that possible to do seamlessly?

sqlguru448's avatar
sqlguru448
Helper III
8 years ago

Create a Custom grouping and include Federal in one single group and rest of the Order ID in other group then use it in the table as well as slicer.

 

Selecting Both grouping will give you the federal as well as other industry, selecting other in slicer will exclude Federal Industry.