Flag Duplicates as Yes/No, But Not Including Blanks

[sv98917n]

Hello Everyone, 

I am trying to flag duplicate values with the following measure since I need it do work with my page filters: 

Critical Segment ID (Y/N) = IF (COUNTROWS ( FILTER ( allselected(Table1), Table1[SEGMENT_ID] = MAX( Table1[SEGMENT_ID] ) ) ) >=2, "YES", "NO" )

My only problem is that it is flagging my blank segment IDs as a duplicate and returning "Yes."

Anyway for it to ignore the blanks?

Thank you! 

[v-jayw-msft · Accepted solution]

Hi @sv98917n ,

Filter blank value before count rows, check the formula below.

Column =
VAR countid =
    CALCULATE (
        COUNTROWS ( FILTER ( 'Table', 'Table'[value] <> BLANK () ) ),
        ALLEXCEPT ( 'Table', 'Table'[id] )
    )
RETURN
    IF ( countid > 1, "yes", "no" )

Best Regards,

Jay

[amitchandak]

@sv98917n , Try like

Critical Segment ID (Y/N) = IF (COUNTROWS ( FILTER ( allselected(Table1), Table1[SEGMENT_ID] = MAX( Table1[SEGMENT_ID] ) ) ) >=2 && not(isblank(Table1[SEGMENT_ID])), "YES", "NO" )

[sv98917n]

It won't let me use the column [SEGMENT_ID] after the not(isblank at the end. Says that has to be a measure in order to work. 

[darentengmfs]

Hi @sv98917n 

Try adding ISBLANK() in your formula

IF(ISBLANK(Table1[SEGMENT_ID]), "No"),IF (COUNTROWS ( FILTER ( allselected(Table1), Table1[SEGMENT_ID] = MAX( Table1[SEGMENT_ID] ) ) ) >=2, "YES", "NO" ))