Forum Discussion
Conditional formatting on a matrix based on duplicate values on multiple other columns
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.
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.