Forum Discussion
Conditional Formatting Duplicate Rows in Table that Adjusts with Filters
Hi, Anonymous
I simply simulated some data, hoping to solve your problem.
1. Create a new column to calculate the number of times the name appears.
Number of occurrences =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Use] ),
FILTER ( 'Table', [Name] = EARLIER ( 'Table'[Name] ) )
)
2. Create a new measure to determine if a name appears multiple times to highlight it.
Highlight =
IF ( MIN ( 'Table'[Number of occurrences] ) >= 2, "Red", BLANK () )
Turn on the background color option in the Conditional formatting section.
The settings are shown in the figure.
The highlighted results are shown in the figure.
Does this solve your problem? If it doesn't solve it what kind of results do you expect? Is it possible to provide a short version of the PBIX file for testing? Looking forward to your response, thank you!
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.
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?
Your dax has a filter ALL( 'Table') so your condition filter does not affect it.
Maybe have a look at ALLEXCEPT or REMOVEFILTERS?
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.
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.
