DAX to filter on rows and columns with values

[mlbo]

I have a data set requiring a matrix visual. I want to create a slicer where if i filter on a column, for example "A" i want to see all rows where there is data in "A", but also see the corresponding columns if they contain a value. 

E.g. if this is my starting point:

        A     B      C

J1            10  -10

J2    20   -20   

J3    12  -12

When i filter on "A" in the slicer, this is how I want it to look:

        A     B     

J2    20   -20   

J3    12  -12

If i filter on "C" in the slicer, then it should show this:

      B      C

J1  10  -10

Is there a way to do this? I have tried googling but I havent been able to find anything

[mlbo]

Thank you, that worked!

[Anonymous · Accepted solution]

Hi @mlbo ,

Based on your description, I created these data.

1. created the calculation table and used the fields in the calculation table as slicers.

Table 2 = 
DISTINCT('Table'[Column2])

2. create the MEASURE.

Measure = 
VAR _value = CALCULATE(MAX('Table'[Column3]),FILTER(ALL('Table'),'Table'[Column1] = MAX('Table'[Column1]) && 'Table'[Column2] = SELECTEDVALUE('Table 2'[Column2])))
RETURN
IF(_value = BLANK(),0,1)

3.Use the value of MEASURE equal to 1 as a filter for the matrix.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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