Conditional formatting on a matrix based on duplicate values on multiple other columns

[suchomelb]

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.

[suchomelb · Accepted solution]

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. 

[amitchandak]

@suchomelb,

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

[suchomelb]

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

[suchomelb]

@amitchandak ,

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.