Forum Discussion
Create a Slicer based on data from 3 columns
This is an expansion of my earlier query, which was resolved for 2 columns, now I would like to create a slicer based on 3 columns:
https://community.fabric.microsoft.com/t5/Desktop/Create-a-Slicer-based-on-data-from-2-columns/m-p/1988123
I would like to create a slicer which will be based on 3 columns. For example sample data and how the slicer should look like is shown below.
Sample data:
| Type A | Type B | Type C | |
| Sydney | 100 | 10 | 3 |
| Tokyo | 200 | 80 | 2 |
| SF | 300 | 20 | 1 |
| NYC | 230 | 20 | 1 |
| LA | 500 | 5 | 4 |
Slicer: will be based on columns "Type A", "Type B" & "Type C" such that when I select "Type A" it will display the values for all the cities for type A. If I select "Type B" it should display values of cities for type B and if i select both "Type A" and "Type B" then it should sum the values. Scenarios outlined below:
Scenario 1: Type A selected
| Slicer |
| Type A - selected |
| Type B |
| Type C |
Table will display:
| Type | |
| Sydney | 100 |
| Tokyo | 200 |
| SF | 300 |
| NYC | 230 |
| LA | 500 |
Scenario 2: Same as scenario 1 when type B is selected.
Scenario 3: Same as scenario 1 when type C is selected.
Scenario 4: when Type A & Type B are selected
| Slicer |
| Type A - selected |
| Type B - selected |
| Type C |
Table shows:
| Type | |
| Sydney | 110 |
| Tokyo | 280 |
| SF | 320 |
| NYC | 250 |
| LA | 505 |
Scenario 5: when Type A & Type C are selected Same as scenario 4
Scenario 6: when Type B & Type C are selected Same as scenario 4
Scenario 7: When A, B and C are selected or nothing is selected
| Slicer |
| Type A - selected |
| Type B - selected |
| Type C - selected |
OR
| Slicer |
| Type A |
| Type B |
| Type C |
Output:
| Type | |
| Sydney | 113 |
| Tokyo | 282 |
| SF | 321 |
| NYC | 251 |
| LA | 509 |
So, based on the previous solution I exapanded the switch statement, however it works for all scenarios except for when you select A and B together. See the snapshot below for the logic:
Can someone please help me with the correct implementation for this?
Sample PBIX file: https://drive.google.com/file/d/1NgsS0Z0Ws1EWSLZjwLXHESWRS4DGrMno/view?usp=drive_link
Thanks in advance
Hi Anonymous
When you select A and B, SUM('Selection Table'[Value]) returns 3 rather than 7 you'd expect, that's why it doesn't work here. You can modify the Result measure like below.
Result = SWITCH ( SUM ( 'Selection Table'[Value] ), 1, [Sum of TypeA], 2, [Sum of TypeB], 3, IF ( COUNTROWS ( 'Selection Table' ) = 1, [Sum of TypeC], [Total of TypeAB] ), 4, [Total of TypeAC], 5, [Total of TypeBC], 6, [Total of Types] )Or you can try a new measure
Result = SWITCH( VALUE(CONCATENATEX('Selection Table','Selection Table'[Value])), 1 , [Sum of TypeA], 2 , [Sum of TypeB], 3 , [Sum of TypeC], 12, [Total of TypeAB], 13, [Total of TypeAC], 23, [Total of TypeBC], 123, [Total of Types] )Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
4 Replies
Hi Anonymous
When you select A and B, SUM('Selection Table'[Value]) returns 3 rather than 7 you'd expect, that's why it doesn't work here. You can modify the Result measure like below.
Result = SWITCH ( SUM ( 'Selection Table'[Value] ), 1, [Sum of TypeA], 2, [Sum of TypeB], 3, IF ( COUNTROWS ( 'Selection Table' ) = 1, [Sum of TypeC], [Total of TypeAB] ), 4, [Total of TypeAC], 5, [Total of TypeBC], 6, [Total of Types] )Or you can try a new measure
Result = SWITCH( VALUE(CONCATENATEX('Selection Table','Selection Table'[Value])), 1 , [Sum of TypeA], 2 , [Sum of TypeB], 3 , [Sum of TypeC], 12, [Total of TypeAB], 13, [Total of TypeAC], 23, [Total of TypeBC], 123, [Total of Types] )Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!- Chakravarthy
Resolver II
Anonymous , First Unpivot the Types columns and then pull the value and country on to table visual. Pull the Type column to Slicer.. In future if more types are added, then automatically slicer gets updated. Attached screenshots.
