Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

LOOKUP VALUE FOR DUPLICATE IDs

Hey gurus,

 

Appreciate your help on the below.

 

I have a table that has IDs and flags

ID         Flag

888          Y

888          N

999          N

999          N

 

 

I want to return Y for the ID if it occured once that the Flag is Y.

 

so in my unique IDs table it will return the below:

 

 

888   Y

999   N

 

It is as if it searches if 888 has Y in the flag column and returns Y

 

Thanks,

Amr

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file, whether it works.

    It is for creating a new table.

     

     

     

    New ID Table =
ADDCOLUMNS (
    SUMMARIZE ( Data, Data[ID] ),
    "@Flag", IF ( "Y" IN CALCULATETABLE ( DISTINCT ( Data[Flag] ) ), "Y", "N" )
)

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file, whether it works.

    It is for creating a new table.

     

     

     

    New ID Table =
ADDCOLUMNS (
    SUMMARIZE ( Data, Data[ID] ),
    "@Flag", IF ( "Y" IN CALCULATETABLE ( DISTINCT ( Data[Flag] ) ), "Y", "N" )
)