Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating A Calculated Column with Dynamic Selection of Slicers

I have a source mapping of Employee Name, Place Of Birth, Place Of Work

 

My mandatory requirement is to have two datasets - PB (Employee Name,Place Of Birth) ; PW(Employee Name,Place Of Work)

and I need 2 slicers -
slicer1 -  showing  labels "Place Of Birth","Place Of Work"
slicer2 -  showing either PB[Place Of Birth] or PW[Place Of Work] column values depending on the dynamic select of slicer 1.

Please do help me out with a solution...Thanks in advance!

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

     

    Amit's solution should work. You can create a new table with DAX expression. The data in this new table are from your two source tables and will update automatically if you refresh the dataset. Take the following steps for reference.

     

    Sample data: 

    Create a new table with DAX code below:

    CombineTable =
UNION (
    SUMMARIZE (
        PB,
        PB[Name],
        "Place Type", "Place of Birth",
        "Place", MAX ( PB[Place of Birth] )
    ),
    SUMMARIZE (
        PW,
        PW[Name],
        "Place Type", "Place of Work",
        "Place", MAX ( PW[Place of Work] )
    )
)

    Then you will get a table like:

    Put two slicers on the report canvas, for one select 'CombineTable'[Place Type] in the field and for the other select 'CombineTable'[Place] in the field. Now you will find the values in slicer 2 will dynamically change according to the selection in slicer 1.

     

    Best Regards,

    Community Support Team _ Jing Zhang

    If this post helps, please consider Accept it as the solution to help other members find it.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , One of the Solution is create a table like this

    union(summarize(data1, [Place Of Birth] ,"Name" , "Place of birth"),

    summarize(data1, [Place Of Work] ,"Name" , "Place Of Work")

    ) //rename first colum

    Now you can use this table in the slicer, But prefer as independent filter. Else you need have a comined key

     

    example  = "Place Of Birth" &  " " & [Place Of Birth]

     

    same stuff in common table and second table and join on this key now

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak 
    but my mandatory requirement is to have 2 datasets i.e. "Place Of Birth" and"Place Of Work"  columns should come from two different datasets. I need to have slicer 2 dynamically change when slicer 1 is selected by user..

    If the user selects the word "Place Of Birth" then slicer 2 should show values from 'PB' dataset's column PB[Place Of Birth] .
    Again, If the user selects the word "Place Of Work" then slicer 2 should show values from 'PW' dataset's column PW[Place Of Work] .

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Anonymous 

       

      Amit's solution should work. You can create a new table with DAX expression. The data in this new table are from your two source tables and will update automatically if you refresh the dataset. Take the following steps for reference.

       

      Sample data: 

      Create a new table with DAX code below:

      CombineTable =
UNION (
    SUMMARIZE (
        PB,
        PB[Name],
        "Place Type", "Place of Birth",
        "Place", MAX ( PB[Place of Birth] )
    ),
    SUMMARIZE (
        PW,
        PW[Name],
        "Place Type", "Place of Work",
        "Place", MAX ( PW[Place of Work] )
    )
)

      Then you will get a table like:

      Put two slicers on the report canvas, for one select 'CombineTable'[Place Type] in the field and for the other select 'CombineTable'[Place] in the field. Now you will find the values in slicer 2 will dynamically change according to the selection in slicer 1.

       

      Best Regards,

      Community Support Team _ Jing Zhang

      If this post helps, please consider Accept it as the solution to help other members find it.