Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Conditional Formatting Duplicate Rows in Table that Adjusts with Filters

Hi, I have looked at ways of implementing Conditional Formatting of duplicate table entries, where a calculated column in used to count the number of duplicates for each row and then conditional form...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for taking the time to respond.

When I create that column, the output is 1, even when there are duplicates. The first row gives an output of 8 but every row after that is a 1.

I think I can see the same error in your first screenshot where the 'Number of occurrences' is given as 1 when there are duplicates visible.

 

Here is an example with dummy data. I have a list of Names, and have a Column to count duplicates (using the code in my other reply in this thread).

In the above situation, with all the data, every row should be highlighted as every Name appears more than once.

 

If I apply a filter as below:

Only 'Laptop A' has any duplicates, and should be the only rows that remain highlighted. Essentially the NameDistinctCount column needs to update with the filters, which I believe is achieved with a measure?

 

 

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

Hi, Anonymous 

 

Based on your previous description, if the name appears 10 times in the original data, but only once in the filtered table, the name should not be highlighted. What I understand is that you need to highlight the name many times in different conditions. I don't know if I understand it correctly.

 

You can try the following methods.

NameDistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Condition] ),
    FILTER ( 'Table', [Name] = EARLIER ( 'Table'[Name] ) )
)

 

Highlight = 
IF ( MIN ( 'Table'[NameDistinctCount]) >= 2, "Red", BLANK () )

 

Best Regards,

Community Support Team _Charlotte

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I'm afraid this doesnt work - I can see in your first screenshot that the distinct count is not right - as it is giving a '1' value when there are duplicate names.

     

    I don't know how else to describe it other than how I did on my previous message with the example screenshots.

    Names need to be highlighted if it is a duplicate in the column. The highlighting needs to reflect what you can see with the filters applied - and not the original data.