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

sqlguru448's avatar
sqlguru448
Helper III
8 years ago

Create a Calculated column "group by" and assign a value 1 for federal and 0 for others using switch operator/function.

 

Create a slicer on that newly created column "group by", give it a try if you need further help I can try at my end and provide you step by step solution.

 

Thanks

  • 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.