Forum Discussion
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 | Amount |
| 1 | Federal | $100 |
| 2 | Retail | $150 |
| 3 | Federal | $125 |
| 4 | Healthcare | $75 |
| 5 | Finance | $250 |
If you chose the Include Federal part of the slicer then you get this:
| Industry | Count | Total |
| Federal | 2 | $225 |
| Retail | 1 | $150 |
| Healthcare | 1 | $75 |
| Finance | 1 | $250 |
If you chose exclude you would get this:
| Industry | Count | Total |
| Retail | 1 | $150 |
| Healthcare | 1 | $75 |
| Finance | 1 | $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.
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=0Regards,
Jimmy Tao
5 Replies
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=0Regards,
Jimmy Tao
Thank you. This is what I was looking for but couldn't figure the simplest way to do this.
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.
