Highlight mismatch Data in Power BI Matrix in multiple rows for the same Id

[patilpoonam21]

Hi,

I have below data in Power BI. I need to highlight the cells for which data is not matching for the same Id for each column.

Is there any way to compare rows for same Id?

Thanks,

Poonam

[Irwan · Accepted solution]

Hello @patilpoonam21 

Please check if this can work for your needs.

1. Create three measure because there are three column to be checked.

for example this is for C1 duplicate check and yellow-coloring :

C1 Color =

var _key = SELECTEDVALUE('Table'[Key])

var _C1 = SELECTEDVALUE('Table'[C1])

var _Value = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Key]=_key&&'Table'[C1]=_C1))

Return

IF(

    _Value=1,

    "#FFFF00", //yellow color code

    "")

_Value is for checking duplicate of Key column and C1 column while conditional if for coloring every non-duplicate value.

2. go to column field then select the arrow and choose conditional formating.

3. in conditional formating, choose Field Value in format style then select the measure that made before.

and do the same for C2 and C3.

Hope this will help you.

Thank you.

[patilpoonam21]

Both the solutions are working. Thank you so much @Irwan & @Anonymous 

[Anonymous · Accepted solution]

Hi,@patilpoonam21. Hello,@Irwan,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
I am glad to help you.

According to your description, you want to mark values in a table in power bi that are grouped by Key and are not the same in each column.

If I understand you correctly, you can refer to my test below.
You can also achieve the goal by constructing the measure.

I have constructed the corresponding measure for each column.

M_C1 = 
CALCULATE(DISTINCTCOUNT('case02'[C1]),FILTER(ALL(case02),'case02'[Key]=MAX('case02'[Key])))

M_C2 = 
CALCULATE(DISTINCTCOUNT('case02'[C2]),FILTER(ALL(case02),'case02'[Key]=MAX('case02'[Key])))

M_C3 = 
CALCULATE(DISTINCTCOUNT('case02'[C3]),FILTER(ALL(case02),'case02'[Key]=MAX('case02'[Key])))

Subsequently set the background for the columns in visual (changing the background color based on the value of the constructed measure)

like this:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irwan · Accepted solution]

Hello @patilpoonam21 

Please check if this can work for your needs.

1. Create three measure because there are three column to be checked.

for example this is for C1 duplicate check and yellow-coloring :

C1 Color =

var _key = SELECTEDVALUE('Table'[Key])

var _C1 = SELECTEDVALUE('Table'[C1])

var _Value = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Key]=_key&&'Table'[C1]=_C1))

Return

IF(

    _Value=1,

    "#FFFF00", //yellow color code

    "")

_Value is for checking duplicate of Key column and C1 column while conditional if for coloring every non-duplicate value.

2. go to column field then select the arrow and choose conditional formating.

3. in conditional formating, choose Field Value in format style then select the measure that made before.

and do the same for C2 and C3.

Hope this will help you.

Thank you.