topic Re: [Question] Filtering columns in a matrix visual with DAX in DAX Commands and Tips

[Question] Filtering columns in a matrix visual with DAX

charonT — Wed, 04 Dec 2024 16:38:09 GMT

I am trying to use a DAX measure to filter the columns in a matrix visualization based on different conditions. The DAX works well if I don't add any fields in the "rows".

However, when I would like to show the breakdown, it will show all the columns.

This is the DAX I am working on, are there any suggestion to solve this issue? Thank you in advance.

year filter = VAR Selection = SELECTEDVALUE('Metrics'[Metrics]) VAR YearSelection = SELECTEDVALUE('Data release'[Year of data release]) VAR Mode = SELECTEDVALUE('STARTMODE'[Mode]) RETURN IF ( Selection = "A", IF ( Mode = "Part-time", IF ( MAX(YEAR[YEAR]) <= YearSelection - 4 && MAX(YEAR[YEAR]) >= YearSelection - 7, 1, 0 ), IF ( MAX(YEAR[YEAR]) <= YearSelection - 3 && MAX(YEAR[YEAR]) >= YearSelection - 6, 1, 0 ) ), IF ( Selection = "B", IF ( Mode = "Part-time", IF ( MAX(YEAR[YEAR]) <= YearSelection - 8 && MAX(YEAR[YEAR]) >= YearSelection - 11, 1, 0 ), IF ( MAX(YEAR[YEAR]) <= YearSelection - 6 && MAX(YEAR[YEAR]) >= YearSelection - 9, 1, 0 ) ), IF ( Selection = "C", IF ( MAX(YEAR[YEAR]) <= YearSelection - 3 && MAX(YEAR[YEAR]) >= YearSelection - 6, 1, 0 ) ) ) )

Re: [Question] Filtering columns in a matrix visual with DAX

lbendlin — Wed, 04 Dec 2024 20:54:57 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: [Question] Filtering columns in a matrix visual with DAX

charonT — Thu, 05 Dec 2024 14:00:28 GMT

https://drive.google.com/file/d/1SUiO9VjnHY5a3svdI58ElI8tuhcmnz7n/view?usp=sharing

Thank you. I have uploaded the sample data. The output I expected is the sex table can filter the year based on the matrix and baseyear selected in the filter, similar to the table on the top.

Re: [Question] Filtering columns in a matrix visual with DAX

lbendlin — Thu, 05 Dec 2024 15:31:08 GMT

I unpivoted the data table to make it usable.

Please explain what the year filter is supposed to achieve.

Re: [Question] Filtering columns in a matrix visual with DAX

charonT — Thu, 05 Dec 2024 15:46:44 GMT

Thank you! Here's what I'm aiming to achieve:

If Metric A is selected, the table should display YEAR ranging from BASEYEAR-3 to BASEYEAR-1.

If Metric B is selected, the table should display YEAR ranging from BASEYEAR-4 to BASEYEAR-2.

If Metric C is selected, the table should display YEAR ranging from BASEYEAR-5 to BASEYEAR-3.

The year filter works perfectly when I don’t include any rows in the matrix. However, it stops working when I add "SEX" as a row in the table.

Re: [Question] Filtering columns in a matrix visual with DAX

lbendlin — Thu, 05 Dec 2024 20:06:40 GMT

Your metric slicer would have to be single select for this to work.

Re: [Question] Filtering columns in a matrix visual with DAX

charonT — Fri, 06 Dec 2024 11:17:16 GMT

I’m not sure if this is the case. I’ve set the slicer to single select, but the issue persists.

Re: [Question] Filtering columns in a matrix visual with DAX

charonT — Fri, 06 Dec 2024 14:46:34 GMT

Thank you! I managed to sort this out on my own. The issue was that one of the DAX commands should have been set to MIN instead of MAX—a silly mistake on my part, haha. Interestingly, I’m not sure why it worked when there were no fields in the rows. The corrected DAX formula is provided below:

year filter = VAR Selection = SELECTEDVALUE('Metrics'[Metrics]) VAR YearSelection = SELECTEDVALUE('Data release'[Year of data release]) VAR Mode = SELECTEDVALUE('STARTMODE'[Mode]) RETURN IF ( Selection = "A", IF ( Mode = "Part-time", IF ( MAX(YEAR[YEAR]) <= YearSelection - 4 && MIN(YEAR[YEAR]) >= YearSelection - 7, 1, 0 ), IF ( MAX(YEAR[YEAR]) <= YearSelection - 3 && MIN(YEAR[YEAR]) >= YearSelection - 6, 1, 0 ) ), IF ( Selection = "B", IF ( Mode = "Part-time", IF ( MAX(YEAR[YEAR]) <= YearSelection - 8 && MIN(YEAR[YEAR]) >= YearSelection - 11, 1, 0 ), IF ( MAX(YEAR[YEAR]) <= YearSelection - 6 && MIN(YEAR[YEAR]) >= YearSelection - 9, 1, 0 ) ), IF ( Selection = "C", IF ( MAX(YEAR[YEAR]) <= YearSelection - 3 && MIN(YEAR[YEAR]) >= YearSelection - 6, 1, 0 ) ) ) )