Forum Discussion

AlessiaZak's avatar
AlessiaZak
Regular Visitor
2 years ago
Solved

Conditional formatting to higlightvduplicate values, excluding blank rows.

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.

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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!!

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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!!