Forum Discussion
Conditional formatting on a matrix based on duplicate values on multiple other columns
I cannot seem to find an answer to my exact issue anywhere. Hopefully somebody here will be able to help.
I have a matrix with the counts of user reviews as the values, user is rows and dates is columns. I want to highlight the values in the matrix that have a count of 2 or more based on 4 columns.
An example of the data in a table is as shown below. Because there is a count of 2, I would want the matrix value to highlight in red.
| Last Reviewer | Count | ID | Review Status | Submitted Date |
| Joe Shmoe | 2 | 947081 | In Progress | 1/25/2023 |
| Joe Shmoe | 1 | 947081 | Missing Info | 1/25/2023 |
| Joe Shmoe | 1 | 957125 | In Progress | 1/25/2023 |
| Joe Shmoe | 1 | 957125 | Missing Info | 1/25/2023 |
| Joe Shmoe | 1 | 968706 | In Progress | 1/25/2023 |
| Joe Shmoe | 1 | 968706 | Missing Info | 1/25/2023 |
| Joe Shmoe | 1 | n029281 | Pending | 1/25/2023 |
I have tried quite a few different options but they all just seem to highlight anything with a cound of 2 or more in my matrix and not that there are 2 or more duplicates within that value.
Thanks.
Well I managed to finally figure it out myself. I assumed this would be an easy one for the pro's on here, but most likely I didn't explain it properly, as I have a tough time laying out all the correct details. Unfortunately I don't even understand fully why it only worked for me this way, but here is what I did:
Instead of doing it all with one measure, I created a calculated column first like so...
Calc Column1= Calculate(countrows(Table), allexcept(Table, Table[Last Reviewer],[ID],[Review Status],[Submitted Date]))
Then I did a separate measure that looks at that column...
Measure1 = Calculate(Countrows(Table1), Table1(Calc Column1) > 1
Last I did a conditional formatting measure to make anything greater than 1 red...
IF ( Measure1 > 1, "Red", Blank () )Once again, I wish I fully understood why it took a column and a measure. And maybe there is a way to do it all in a measure by itself, but I could not figure it out for the life of me. Any help I could find was very similar to the first reponse above.
4 Replies
Create a measure like this
M1= Calculate(countrows(Table), allexcept(Table, Table[Last Reviewer],[ID],[Review Status],[Submitted Date]))
and use in conditional formatting, or use a color measure like
Color = if([m1] >1, "Red", blank())
and use in conditional formatting using field value option
If this do not help, then please share expected output and logic of color with example
I am getting all the values in my matrix that are greater than 1 being highlighted red, not just ones with duplicates.
A little more context: So if i drill down on those 8 on the 25th to a table visual with columns of Last Reviewer | ID | Review Status | Submitted Date | Count, I would see 7 rows, with a count of 2 for one row and a count of 1 for the others, totalling 8. All the measures I write seem to either highlight every value or like the instance above where its just highlighting everything over 1.
I just created an example PBIX with the same issue as my main file. It is highlighting anything over a count of 1 and not just the values with duplicates.
https://drive.google.com/file/d/1zG9X2Bd6hohORQbTSLzK4DkJza3DuzCt/view?usp=drive_link
Well I managed to finally figure it out myself. I assumed this would be an easy one for the pro's on here, but most likely I didn't explain it properly, as I have a tough time laying out all the correct details. Unfortunately I don't even understand fully why it only worked for me this way, but here is what I did:
Instead of doing it all with one measure, I created a calculated column first like so...
Calc Column1= Calculate(countrows(Table), allexcept(Table, Table[Last Reviewer],[ID],[Review Status],[Submitted Date]))
Then I did a separate measure that looks at that column...
Measure1 = Calculate(Countrows(Table1), Table1(Calc Column1) > 1
Last I did a conditional formatting measure to make anything greater than 1 red...
IF ( Measure1 > 1, "Red", Blank () )Once again, I wish I fully understood why it took a column and a measure. And maybe there is a way to do it all in a measure by itself, but I could not figure it out for the life of me. Any help I could find was very similar to the first reponse above.
