Forum Discussion

uk17487's avatar
uk17487
Frequent Visitor
7 years ago
Solved

DAX Formula Help Needed - Retrive Non Slicer Values having same Measure Value as Slicer Value

Hello All,

 

We have a table in PBI with two columns C1 and C2 having data as below

C1 C2

1    A

1    B

2    B

2    C

3    C

3    D

4    D

4    A

 

Requirement -  Using a single select slicer on column C2, for a selected value, it is required to display all values from C2 that have same value of C1 except the slicer value.

 

Example :

If C2=A is selected in slicer, then B and D should be displayed as both have same C1 value (1,4) as A

If C2=D is selected in slicer, then C and A should be displayed as both have same C1 value (3,4) as C

 

We have to implement this in DAX. Request to please help or provide an approach. 

 

Thanks

Uday

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, uk17487 

    For your requirement, you could try this way:

    Step1:

    Add a fact C2 table as a slicer, (you could not use original C2 as a slicer)

    Step2:

    Use this formula to create a measure

    Measure = VAR _table=CALCULATETABLE(VALUES('Table'[C1]),FILTER(ALLSELECTED('Table'),'Table'[C2] = SELECTEDVALUE(C2[C2]))) return
CALCULATE(MAX('Table'[C2]),FILTER('Table','Table'[C1] IN _table))

    Step3:

    Drag this measure into visual level filter and set is not blank

    Result:

    and here is pbix file, please try it.

     

    Best Regards,

    Lin

     

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, uk17487 

    For your requirement, you could try this way:

    Step1:

    Add a fact C2 table as a slicer, (you could not use original C2 as a slicer)

    Step2:

    Use this formula to create a measure

    Measure = VAR _table=CALCULATETABLE(VALUES('Table'[C1]),FILTER(ALLSELECTED('Table'),'Table'[C2] = SELECTEDVALUE(C2[C2]))) return
CALCULATE(MAX('Table'[C2]),FILTER('Table','Table'[C1] IN _table))

    Step3:

    Drag this measure into visual level filter and set is not blank

    Result:

    and here is pbix file, please try it.

     

    Best Regards,

    Lin

     

    • uk17487's avatar
      uk17487
      Frequent Visitor
      7 years ago

      Hi Lin,

       

      It worked perfectly.

      Thanks for your help.

       

      • uk17487's avatar
        uk17487
        Frequent Visitor
        7 years ago

        Hi,

         

        If the main table is going to be connected to other supporting tables and the reports had to be created using the slicer of the main table column C2, then what do we tweak in the current solution to handle the same. As in current solution, we are using C2 table as the slicer and not the main table column. My apologies I did not forsee that model can have more than one table. Kindly advise.

         

        Please let me know if I should start a new post.

         

        Thanks

        Uday