topic Re: Filter a bar chart based on selected matrix cell in DAX Commands and Tips

Filter a bar chart based on selected matrix cell

Dimitris_Kats — Fri, 10 May 2024 18:55:03 GMT

Hi dear members.

I need your help.

I have a matrix with categories in the rows and multiple measures in the values.

For example in the rows I have:

Category 1

Category 2

Category 3

And in the columns:

Measure 1

Measure 2

Measure 3

I want to create a bar chart next to the matrix enabling users to select a specific cell(for example Measure 3, category 1) and display only that measure to the bar chart. Currently the bar chart display all measures for the selected row.

Any idea how to filter not only the rows but the .measure as well??

Thank you so much

Re: Filter a bar chart based on selected matrix cell

xifeng_L — Sat, 11 May 2024 07:39:38 GMT

Hi Dimitris_Kats ,

Chart interaction can only pass filters for dimension fields to other objects. So, it is necessary to use the column labels of the matrix to achieve filter passing.

But if the column labels of the matrix are used, only one measure can be added, so this measure needs to be able to calculate different indicators based on different column label items.

The Solution as follow:

#1. Create an auxiliary table to use as a matrix column label. Such as:

//New Table Expression ColumnTable = DATATABLE( "Item",STRING, "Index",INTEGER, { {"Measure1",1}, {"Measure2",2}, {"Measure3",3} } )

#2. Create a dynamic measure that can calculate different indicators based on different column labels.

//Measure Expression AutoIndicator = VAR CurIndicator = SELECTEDVALUE('ColumnTable'[Item]) RETURN SWITCH( CurIndicator, "Measure1",[Measure1], "Measure2",[Measure2], "Measure3",[Measure3] )

#3. Place the measure or column label fields created above into matrices and bar charts.

#4. Edit interaction settings to filter.

After completing the above settings, you can meet your needs. Such as:

#Default

# Selected

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Filter a bar chart based on selected matrix cell

Dimitris_Kats — Sat, 11 May 2024 11:25:00 GMT

Thank you so much xifeng_L for your time and help. Is it possible to share with me the pbix file??

It's a little hard for me to follow your instructions based on the images.

Thank you very much 🙏

Re: Filter a bar chart based on selected matrix cell

xifeng_L — Sat, 11 May 2024 12:16:18 GMT

FYR.

Demo - Filter a bar chart based on selected matrix cell.pbix

Re: Filter a bar chart based on selected matrix cell

Dimitris_Kats — Sat, 11 May 2024 13:54:48 GMT

This is great. Thank you so much.

I was wondering if it is possible when there isn't selected any cell to display only one measure instead of all measures??

For example by default the bar chart to display measure 1 if no cell is selected.

I hope this isn't a crazy request 🙈

Re: Filter a bar chart based on selected matrix cell

xifeng_L — Sat, 11 May 2024 14:07:18 GMT

Sure, it can be achieve.

You can create a new measure and use for bar chart. Pls refer to follow pic:

Use of Bar Chart = IF(COUNTROWS(ALLSELECTED(ColumnTable[Item]))>1, CALCULATE([AutoIndicator],KEEPFILTERS('ColumnTable'[Item]="Measure1")), [AutoIndicator] )

Then, when you select any cells, it will display the corresponding indicators.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Filter a bar chart based on selected matrix cell

Dimitris_Kats — Sat, 11 May 2024 14:21:07 GMT

Oh that's amazing!! Thank you so much!!

One last question, I promise 😅

If I want to add in the rows of bar chart instead of the category the region and when no cell is selected in the matrix to display specific category and measure ( for example category 1 and measure 1) but when a cell is selected in the matrix (the matrix remains the same) to filter the bar chart based on the selected cell ( the corresponding category and measure)

I hope I am not asking for too much.

I want to thank you very much for your help. I really appreciate that 🙏

Re: Filter a bar chart based on selected matrix cell

xifeng_L — Sat, 11 May 2024 14:35:54 GMT

Let's slightly modify the expression of the measure, pls try again.

Use of Bar Chart = IF(COUNTROWS(ALLSELECTED(ColumnTable[Item]))>1, CALCULATE( [AutoIndicator], KEEPFILTERS('ColumnTable'[Item]="Measure1"), KEEPFILTERS('Fact'[Category]="Category1") ), [AutoIndicator] )

Re: Filter a bar chart based on selected matrix cell

Dimitris_Kats — Sat, 11 May 2024 14:47:28 GMT

This is amazing!!! It worked perfectly!!!

Thank you so so much 😊

Re: Filter a bar chart based on selected matrix cell

Dimitris_Kats — Mon, 13 May 2024 11:15:38 GMT

xifeng_L

The solution has worked perfectly. The only problem is that I can't use conditional formatting to the bar chart. I want to highlight the bar with max value but no matter what I try it's be overwritten 😞

Any idea how to solve it?

Re: Filter a bar chart based on selected matrix cell

xifeng_L — Mon, 13 May 2024 14:31:07 GMT

Sorry, I have no idea about this. Because after using the legend field, it is not possible to continue with the conditional formatting feature.

Re: Filter a bar chart based on selected matrix cell

Dimitris_Kats — Mon, 13 May 2024 15:12:30 GMT

No worries. Thank you very much for your help