Conditional formatting to higlightvduplicate values, excluding blank rows.

[AlessiaZak]

Hi all, 

I'm using the below with conditional formatting to highlight duplicate values.

Rowcount : =

IF (

COUNTROWS (

CALCULATETABLE (

'Table',

ALLSELECTED ( 'Table' ),

VALUES ( 'Table'[Customer] )

)

) = 1,

0,

1

)

This works, however, it's also highlighting null rows as they return as matching.

Any suggestions on how to give for example value 2 to all null values, or excluding null from above measure completely?

Many thanks.

[rajendraongole1 · Accepted solution]

Hi @AlessiaZak - You can modify the DAX measure to exclude rows where the Customer value is null. This will ensure that null values are not counted or highlighted as duplicates.

The NOT ISBLANK ( 'Table'[Customer] ) condition ensures that the measure only evaluates non-null customer values.
The condition 'Table'[Customer] <> BLANK() inside the CALCULATETABLE function excludes null values from being counted as duplicates.

Rowcount =
IF (
NOT ISBLANK ( 'Table'[Customer] ) &&
COUNTROWS (
CALCULATETABLE (
'Table',
ALLSELECTED ( 'Table' ),
'Table'[Customer] <> BLANK()
)
) = 1,
0,
1
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!