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

[uk17487]

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

[v-lili6-msft · Accepted solution]

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]

Hi Lin,

It worked perfectly.

Thanks for your help.

[uk17487]

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

[v-lili6-msft]

hi, @uk17487 

Just for your current requirement(Retrive Non Slicer Values having same Measure Value as Slicer Value), It could not use the main table column C2 as a slicer.

And for your other requirement, you could share your sample report for us to have a test or start a new post and add the other conditional.

Best Regards,

Lin