cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MJNiven
Frequent Visitor

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 formatting is applied based on that value. However, in my implementation, the formatting did not adjust with filters.

 

My data has thousands of rows, one column is called "Name" and there are names that can appear there many times. I have a table visualisation where I use slicers to filter those names for a specific use. I only want to see duplicate names highlighted after the slicers have been applied. For example, if a name appears 10 times in the raw data, but only once in the filtered table, it should not be highlighted.

 

Thank you

8 REPLIES 8
v-zhangti
Community Support
Community Support

Hi, @MJNiven 

 

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] ) )
)

vzhangti_0-1637826585287.png

 

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.

vzhangti_1-1637826920303.png

The settings are shown in the figure.

vzhangti_2-1637827114850.png

The highlighted results are shown in the figure.

vzhangti_3-1637827221652.pngvzhangti_4-1637827260101.png

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).

MJNiven_1-1637834243770.png

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:

MJNiven_2-1637834293690.png

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
Community Support
Community Support

Hi, @MJNiven 

 

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] ) )
)

vzhangti_0-1637917434252.png

 

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

vzhangti_1-1637917944959.png

 

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.

Your dax has a filter ALL( 'Table') so your condition filter does not affect it.

 

Maybe have a look at ALLEXCEPT or REMOVEFILTERS?

Gabriel_Walkman
Responsive Resident
Responsive Resident

What was your implementation?

 

The example with the name should work if you use a measure in the conditional settings, ie: distinctcount( [name]), and adjust so that hilighting occurs when it is bigger than 1, smaller than 100000

I have implemented a calculated column as provided as a solution in this thread:

https://community.powerbi.com/t5/Desktop/Conditional-Formatting-Duplicate-Records-in-Rows/m-p/690739

 

NameDistinctCount = 
VAR NameDistinctCount = Table[Name]
RETURN

CALCULATE(
    COUNTROWS('Table'),
    ALL('Table'),
   Table[Name] = NameDistinctCount
)

 

How do I implement this as a measure?

If I used the same code within a measure, I get an error as follows: "A single value for column 'Name# in the table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min,max, count, or sum to get a single result."

 

Seems like an easy fix, I just need to know what it is!

Sorry if I distracted you by talking about a measure. I'd guess you can use that dax column in the conditional formatting options just fine.

 

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors