Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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/1...
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
Solved! Go to Solution.
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!
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!
Thank you!
@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.
Hey, Thank you for the reply. This will not work for me since my actual dataset has DAX columns and measures.
Is there way to do this using Switch statement or maybe by other techniques using DAX? Please let me know, Thanks!
User | Count |
---|---|
141 | |
71 | |
70 | |
54 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
61 | |
57 |