Forum Discussion

cmckinney's avatar
cmckinney
Helper IV
5 years ago

Clustered Column Chart Filtering a Matrix

What I want to do is when I click a certain row in a column chart it filters a matrix to only show the values that are related to it on a matrix.   In the following example I would like the matrix ...
cmckinney's avatar
cmckinney
Helper IV
5 years ago

Here I have attached a mock up file. What needs to happen is when a bar is selected on the bar chart the matrix filters only series columns. It is currently showing both series and model options. When you click the bar on the bar chart it filters by date. 

 

Here is a link to the report: https://www.dropbox.com/s/cvwxrr00pmn0hwq/Series%20Select.pbix?dl=0

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi  cmckinney ,

 

First create a date-dimension table:

Table = VALUES(Test[Date])

Then create 2 measures as below:

_Category = CALCULATE(MAX('Test'[Category Element]),FILTER(ALL('Test'),'Test'[Date]=MAX('Table'[Date])))
_Series = 
var _date=CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]=SELECTEDVALUE(Test[Date])))
Return
 IF(SELECTEDVALUE(Test[Date])<>BLANK(),CALCULATE(MAX('Test'[Series Elements]),'Test'[Date]=_date))

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 

  • cmckinney's avatar
    cmckinney
    Helper IV
    5 years ago

    v-kelly-msft 

     

    Thank you so much for the reply. This is a huge step in the right direction but still doesn't quite capture the desired result. When any bar in the "series" chart is selected the matrix must show ONLY series values and NO models values. It must show all of the curently selected series values, not just the ones on that selected date. I will upload another pbix file and some screenshots that might clarify more what I am trying to say. 

     

    You will see in the next screenshots that there is now a slicer and an extra column chart visual for the Models. I have also pasted a table that I cut from an excell file of the desired results in the place of the matrix. 

     

     

    On the first one if you click either bar in the category table it should display all category elements from the days specified on the slicer. The second is the same but with series. Please let me know if I need to further clarify. Thank you again.

     

    Here is the pbix file that you sent with my changes added: https://www.dropbox.com/sh/inxv8sknh9xeidq/AACA669TnOvsEuUMD3I9w4mha?dl=0