Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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/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 AType BType C
Sydney100103
Tokyo200802
SF300201
NYC230201
LA50054

 

rb161604888_0-1704930162192.png

 

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
Sydney100
Tokyo200
SF300
NYC230
LA500



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
Sydney110
Tokyo280
SF320
NYC250
LA505

 

 

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
Sydney113
Tokyo282
SF321
NYC251
LA509



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:

rb161604888_1-1704930655113.png



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

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

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!

View solution in original post

4 REPLIES 4
v-jingzhan-msft
Community Support
Community Support

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!

Anonymous
Not applicable

Thank you!

Chakravarthy
Resolver II
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.

Chakravarthy_1-1704941553402.png

 

 

Chakravarthy_0-1704941392106.png

Chakravarthy_2-1704941612247.png

 

 

 

 

Anonymous
Not applicable

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!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.