how to highlight the duplicate cells in Power BI table in Desktop

[caiying100126]

Hi all, I created a table in Power bi desktop, I want to highlight duplicate values in some columns and fill in the background color. Here I want to highlight  "Material","Description","Vendor name" and "Plant" columns.

[Anonymous]

Hi @caiying100126,

Any availed columns that can be used as index? If not I'd like to suggest you add it in query editor.
Then you can try to use the following measure formula to calculate row count and return color and use on conditional formatting features to highlight specific rows.

Measure =
VAR currIndex =
    MAX ( Table[Index] )
VAR currItem =
    SELECTEDVALUE ( Table[Column] )
VAR dc =
    COUNTROWS (
        FILTER ( ALLSELECTED ( Table ), [Column] = currItem && [Index] < currIndex )
    )
RETURN
    IF ( dc > 1, "Red", "Black" )

Regards,

Xiaoxin Sheng

[danextian · Accepted solution]

Hi @caiying100126 

First create a measure to calculate the row count based on your criteria.

Row Count =
//returns 0 if there's no duplicate, otherwise 1
IF (
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Column1], 'Table'[Column2], 'Table'[Column3] )
    ) = 1,
    0,
    1

Add the columns to a table. 

In fields in visualizations pane,

• click the arrow down next to the field name then click Conditional Formatting then Background Color.
• Select Rules from Format by. 
• Search the newly created measure in Based on field.
• Select is then enter 1 into the value field and then select Number. Choose the desired color.

Only those with 1 Row Count will be highlighted. 

Note: Conditional formatting is to be done for each field.

[Linnil]

Is this "create a Measure" or "create a Column"?

It's not clear sorry. I think you mean create a Column / New Column

[amitchandak]

Create a measure like this. You have to do it for each column.

Color sales = if(countrown(Table)>1,"red","green")

Go to conditional formatting. Based on font or background choose advance control. There choose the option of field and use this measure

[amitchandak]

Also refer: https://www.mssqltips.com/sqlservertip/6265/power-bi-conditional-formatting-for-matrix-and-table-vis...

Refer to section: Power BI Field Value Conditional Formatting