Conditional Filtering Based on Slicer Selection

[grkm]

Hi Everyone! 

I have a slider(single selection) which shows city_name. Based on selection it shows branches. That's perfectly fine.

I would like to add a "L Aggregate" into my slider as thitd option and I want it to filter branch_size = 'L'. 

In other words; 

IF:
C1 -> it shows branch_id 1,2,3   (this is fine)

C2 -> it shows branch_id 4         (this is fine)

L Aggregate -> it should show branch_id 2,4  (this is something I want)

Thanks for your help!

city data table

city_id	city_name
1	C1
2	C2

branch data table

branch_id	branch_name	branch_size	city_id
1	B1	S	1
2	B2	L	1
3	B3	S	1
4	B4	L	2

[Jai-Rathinavel · Accepted solution]

Hi @grkm , I have achieved your requirement using a measure and a calculated table to make it dynamic. Have attached the DAX and the file along with this reply.  If you don't want to display the measure you can hide by shrinking that column in a table visual.

//Calculated Table
Slicer Table = 
var AllCities = SELECTCOLUMNS('City Data',"city_id",'City Data'[city_id],"city_name",'City Data'[city_name])
var LAggregate = DATATABLE("city_id",INTEGER,"city_name",STRING,{{0,"L Aggregate"}})
RETURN
UNION(AllCities,LAggregate)

//Measure
Measure = IF(
    SELECTEDVALUE('Slicer Table'[city_name]) = "L Aggregate" , 
    CALCULATE(COUNT('Branch Data'[branch_id]),'Branch Data'[branch_size] = "L"),
    CALCULATE(COUNT('Branch Data'[branch_id]),TREATAS(VALUES('Slicer Table'[city_id]),'Branch Data'[city_id])))

Thanks,
Jai Rathinavel

[grkm]

@Jai-Rathinavel Thank you very much. Perfect 👍

[amitchandak]

@grkm  Create a new table and do many to many join with Branch , and the new table should filter branch (Single Direction) 

Branch Id, City 
1,C1

2,C1

3,C1

4,C2

2,L

4,L

Used similar logic here
Single Select with Select All : https://youtu.be/plGqCEZRfXU

[grkm]

Thanks for your reply @amitchandak .

So this is not possible dynamically i.e. with measure? 

[amitchandak]

@grkm , You can have measure that show you value of L but it will not combine with C1 and C2

I can not create a measure that will give 
C1 - <>
C2 - <>
L  - <> 

The best we can do is a calculation group 
with items 
C1=  calculate([Measure], filter(Table, Table[City] = "C1")

C2=  calculate([Measure], filter(Table, Table[City] = "C2")

L=  calculate([Measure], filter(Table, Table[branch_size] = "L")

Here, I can display the calculation group's item name on the axis 
Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0