Forum Discussion

Dominok123's avatar
Dominok123
Frequent Visitor
7 years ago
Solved

Finding if Multiple unique ID's share the same values across two columns

Hey,

 

So I'm trying to figure out how to create the fourth column 'ResultMatch' as seen in the table below. What I need this column to return is whether or not multiple ID's share the same 'Value1' and 'Value2' combination. So because ID 1 and 4 share the same Value 1 + Value 2 combination, 1 is returned, and for the ID's whose values don't match anyone else, they receive a 0.

 

Any help would be much appreciated.

 

Table1:

IDValue1Value2ResultMatch
110003501
110003501
212006000
212006000
37201200
37203500
37203500
410003501
410003501

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    This should work as a column or measure:

     

    Column = 
VAR __table = FILTER(ALL('Table6'),[Value1]=EARLIER([Value1]) && [Value2]=EARLIER([Value2]) && [ID]<>EARLIER([ID]))
VAR __count = COUNTROWS(__table)
RETURN
IF(ISBLANK(__count),0,1)

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    This should work as a column or measure:

     

    Column = 
VAR __table = FILTER(ALL('Table6'),[Value1]=EARLIER([Value1]) && [Value2]=EARLIER([Value2]) && [ID]<>EARLIER([ID]))
VAR __count = COUNTROWS(__table)
RETURN
IF(ISBLANK(__count),0,1)