Create a Slicer based on data from 3 columns

[rb161604888]

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

[v-jingzhan-msft · Accepted solution]

Hi @rb161604888 

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!

[rb161604888]

Thank you!

[Chakravarthy]

@rb161604888 , 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.

[rb161604888]

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!