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
shep123
Helper I
Helper I

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_IDIndustryAmount
1Federal$100
2Retail$150
3Federal$125
4Healthcare$75
5Finance$250

 

If you chose the Include Federal part of the slicer then you get this:

IndustryCountTotal
Federal2$225
Retail1$150
Healthcare1$75
Finance1$250

 

If you chose exclude you would get this:

IndustryCountTotal
Retail1$150
Healthcare1$75
Finance1$250

 

I know I could pseudo do this by grouping the values into Federal and everything else. Then the user would have to click both if they wanted everything but was hoping I could do this with a Slicer along the lines of Exclude or Include Federal.

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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 ()
        )
    )
)

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

 

Regards,

Jimmy Tao

View solution in original post

5 REPLIES 5
v-yuta-msft
Community Support
Community Support

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 ()
        )
    )
)

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

 

Regards,

Jimmy Tao

Thank you. This is what I was looking for but couldn't figure the simplest way to do this.

sqlguru448
Helper III
Helper III

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

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?

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.

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.