topic Re: Highlighting Changes in a Matrix and hiding rows with no change in DAX Commands and Tips

Highlighting Changes in a Matrix and hiding rows with no change

FU — Thu, 12 Jan 2023 18:43:10 GMT

Hi all,

I have a matrix shown below and I am trying to create a measure or filter which compares each column and if there are no changes in my date columns then show blank for that specific row.

I ONLY want to be able to see rows that have different values across each date column.

EXAMPLE: the last row shows 'Brownish' for Dec-22 and 'Red' for the rest of the columns. I would want to keep this row visible as it has changes. The rest of the rows are identicial therefore can be blank.

Can someone explain how this could be done?

Happy to attach the data set if needed. Thank you. (the blue shape is blocking sensitive data)

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Thu, 12 Jan 2023 21:26:35 GMT

Hi there,

supposedly you have such data structure:

Create a measure:

DistinctCount = DISTINCTCOUNT(Table1[Value])

Place that measure onto the Visualisation Filter and set it to greater than 1 and there you have it.

Hope that helps.

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Thu, 12 Jan 2023 22:02:19 GMT

olgad
Thank you for the solution - that seemed to work!

How would I be able to create a Card to count the number of rows that contain changes?

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Thu, 12 Jan 2023 22:40:56 GMT

create a table:

Changes = SUMMARIZE(Table1, Table1[Category],"DistinctCount", [DistinctCount])

Then take the count of category and once again the distinct count is greater than one

I will appreciate if you give a like to my answers and mark the solution as accepted if that works for you and answers your question.

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Thu, 12 Jan 2023 23:05:23 GMT

olgad
That didn't quite work for me.

So using my table above I have only 1 row that has changes. It does't seem to be showing that. It's counting all the distinct rows (9 rows)

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Thu, 12 Jan 2023 23:10:49 GMT

Can you attach a snapshot? Where i can see the calculation you are doing and the result?

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Thu, 12 Jan 2023 23:13:40 GMT

Check if you are using a distinct count as a filter from the new table "summary", if you are summarizing there the correct column (the one with values)

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Thu, 12 Jan 2023 23:22:45 GMT

Hope thats clear - I am using the DistinctCount from the table 'changes'

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Thu, 12 Jan 2023 23:31:03 GMT

One cannot see the what you are defining as distinct count

let me send you my file may be you see the difference, because if step one worked then step 2 is based on that measure which i named as "distinct count" in first step and reused in summary table.

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Thu, 12 Jan 2023 23:35:18 GMT

the final word is [DistinctCount]) as per your table below.

Okay send over your file

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Thu, 12 Jan 2023 23:56:16 GMT

send your file over and ill take a look 🙂

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Fri, 13 Jan 2023 00:04:48 GMT

pbix

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Fri, 13 Jan 2023 00:11:29 GMT

I saw your file and did the same but still did not work.

Note: I have 9 unique rows as you can see in my first screenshot. But after I show only changes rows I have 1.

I cannot see 1 in the card I see 9

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Fri, 13 Jan 2023 00:17:00 GMT

Ok, then we have differences in data structure. It doesnt matter you have 9 i have 5. in the end i see 3 not 5. so, check whats different on your side in data structure. Or if you can send me your file then i can solve it.

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Fri, 13 Jan 2023 00:31:37 GMT

Okay I have sent you my file in a private message

Re: Highlighting Changes in a Matrix and hiding rows with no change

olgad — Fri, 13 Jan 2023 00:48:37 GMT

Changes = SUMMARIZE('Project Risks Query-xxx','Project Risks Query-xxx'[Risk ID],'Project Risks Query-xxx'[Attribute],"DistinctCount", [DistinctCount])

and delete the relationship if it gets created/adjust many to many.
Risk ID had to be included into the formula.

Re: Highlighting Changes in a Matrix and hiding rows with no change

FU — Fri, 13 Jan 2023 00:55:11 GMT

What happens when there is more than 1 row under the same Risk ID?