Forum Discussion

grkm's avatar
grkm
New Member
8 months ago
Solved

Conditional Filtering Based on Slicer Selection

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_idcity_name
1C1
2C2

 

branch data table

branch_idbranch_namebranch_sizecity_id
1B1S1
2B2L1
3B3S1
4B4L2
Need Help
  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    8 months ago

    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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    8 months ago

    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's avatar
      grkm
      New Member
      8 months ago

      Thanks for your reply amitchandak .

       

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

      • amitchandak's avatar
        amitchandak
        Super User
        8 months ago

         

        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

  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    Super User
    8 months ago

    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