Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional formatting in visual for multiple duplicates

My question is related to this question: Solved: Conditional Formatting Duplicate Records in Rows - Microsoft Power BI Community.

 

Specifically, I want to mark those rows in a visual which are duplicates. However, the answer in that question only works if there is only one duplicate. I can have more than 2, hence to mark when distinct count is 2 will not be sufficient.

 

How can I achieve this?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    1. Create a measure as below to get the count of systems which per user logged into

    Countofsystem = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[System] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Current User] = SELECTEDVALUE ( 'Table'[Current User] )
    )
)

    2. Create a measure as below to make conditional formatting rule

    Conditional formatting = IF ( [Countofsystem] >= 2, "Red" )

    3. Configure conditional formatting: select the visual and click the field "Current User", Conditional formatting-->Background color

    Best Regards

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    1. Create a measure as below to get the count of systems which per user logged into

    Countofsystem = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[System] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Current User] = SELECTEDVALUE ( 'Table'[Current User] )
    )
)

    2. Create a measure as below to make conditional formatting rule

    Conditional formatting = IF ( [Countofsystem] >= 2, "Red" )

    3. Configure conditional formatting: select the visual and click the field "Current User", Conditional formatting-->Background color

    Best Regards

  • Pragati11's avatar
    Pragati11
    Super User
    4 years ago

    HI Anonymous ,

     

    I would suggest creating another column on top of the calculated column suggested in the above solution on the forum:

    countFlag = 
IF(CountDistinct > 1, 1, 0)

     

    Then use this column for conditional formatting. So, countFlag = 1 is taking into account all the duplicates with count more than 1.

     

    Thanks,

    Pragati