Finding duplicate values in a table

[Evan0211]

I have a table and I am looking for duplicate ID values to change the background color from green to red.  I have written the following in my background expression:

=if(count(Trim([Req ID]))>1,red(),green())

But the duplicate values remain green.  

I am sure I am missing something obvious.  Any ideas?

[MendyS]

Hi @Evan0211 

if [Req ID] is a key field it can make some problems.
Do you have the option to make a flag in ETL?

[Evan0211]

I do not have access to the load scripts unfortunately.  I only have access to design the charts.  I do believe Req ID is a key field in the data store, however I don't have access to that.

Any other ideas of how to color duplicates in the table if it is a key field?

[salonicdk28]

Try this-

=if(count( Total <[Req ID]> [Req ID]) > 1  ,red(),green())

[Evan0211]

Thanks, but it still remains green.  

[Or]

You'd possibly need to adjust this depending on your dimensions, but:

if(Count(total <Number> Number) >1,Red(), Green())

[Evan0211]

That is what I tried above but it still remains green, despite being a duplicate. 

[Evan0211]

Also, I created a measure of: =count( Total <[Req ID]> [Req ID])  and I am getting 1 for the duplicates instead of 2.  Any idea what I am doing wrong?

[MendyS]

@Evan0211 

try - 

if(
count(aggr(count(trim([Req ID]),SomeFields))>1,Red(),Green())

[Or]

Sounds like your values aren't actually duplicates...?

Note that the trim() isn't doing anything here. It doesn't matter if there are leading or trailing spaces since you're just counting the field.