Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Highlight matrix rows when column values are different

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:

 

CategorySubCategoryProductKey
AAA1X1
AAA2X2
BBB1Y1
BBB2Y1

 

My desired output (matrix):

CategorySubCategoryProduct 1Product 2
AAAX1X2
BBBY1Y1

 

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!

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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

     

     

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        DataInsights
        Super User
        3 years ago

        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.