Highlight matrix rows when column values are different

[Anonymous]

I have a scenario where I would like to highlight the values in a matrix visual where they are different in the same row. My source data looks like this:

Category	SubCategory	Product	Key
A	AA	1	X1
A	AA	2	X2
B	BB	1	Y1
B	BB	2	Y1

My desired output (matrix):

Category	SubCategory	Product 1	Product 2
A	AA	X1	X2
B	BB	Y1	Y1

With my real dataset, I have many more products than 2, so I would like to avoid doing any hard-coding of the product names in DAX (as I've seen in other solutions to similar problems).

I wrote a measure to to compare the min and max of the keys by category and subcategory, but it doesn't seem to work as expected when I try to use it for conditional formatting. That code looks like this:

diff_flag = 
var minKey = 
    CALCULATE(
        MINX(my_table,my_table[Key]),
       ALLEXCEPT(my_table,my_table[Category],my_table[SubCategory]))
var maxKey = 
    CALCULATE(
        MAXX(my_table,my_table[Key]),
        ALLEXCEPT(my_table,my_table[Category],my_table[SubCategory]))
var keyMatchBool = IF(minKey = maxKey , 1, 0)
return keyMatchBool

I tried to follow the solution outlined here, but ran into trouble because my Key field is no numeric.

Any help is appreciated!

[DataInsights · Accepted solution]

@Anonymous,

Try this measure:

diff_flag = 
// get row count for each Category/SubCategory
VAR vRowCount =
    CALCULATE (
        COUNTROWS ( my_table ),
        ALLSELECTED ( my_table ),
        VALUES ( my_table[Category] ),
        VALUES ( my_table[SubCategory] )
    )
// get row count for each Category/SubCategory/Key
VAR vRowCountKey =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( my_table, my_table[Category], my_table[SubCategory], my_table[Key] ),
            // remove filter context from matrix columns
            ALLSELECTED ( my_table[Product] )
        )
    )
VAR vResult =
    IF ( vRowCount = vRowCountKey, 0, 1 )
RETURN
    vResult

[Anonymous]

This works! Can you help me understand a little bit better what the code is doing? So we're counting the number rows for each category/subcategory group, then counting the number of rows for each category/subcategory/key group and comparing the two?

[DataInsights]

@Anonymous,

Glad to hear that works. That's correct, it's a row count comparison of category/subcategory vs. category/subcategory/key. The SUMMARIZE function performs a Group By of category/subcategory/key (ALLSELECTED removes product filter context coming from the matrix). If the Group By reduces the row count, it means the key repeats in multiple rows.