Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Match values in a column with itself

Hi All,   I have a table with few columns. I want match fields in one column to match with itself and generate output. I can do it easily in Access by creating a copy of table and joining the respe...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak : It doesn't seems to work. The two tables in the screenshot are identical tables. I just created a copy of one of the tables.  

Yes/No is based on where:

Merchant_Number = Merchant_Number(from 2nd table)

and

date = date(from 2nd table)

and

account_user_id = account_user_id(from 2nd table)

 

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous in DAX, no need to duplicate the table, just add a calculated column in the original one. the code might be

=COUNTROWS(FILTER(Table,Table[Filed1]=EARLIER(Table[Filed1])&&Table[Filed2]=EARLIER(Table[Filed2])&&Table[Filed1]=EARLIER(Table[Filed2])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Daniel,

     

    The formula will count the no of rows. I want to add a condition that when all the 3 fields macthed are matched then it will update the column to yes otherwise no.

     

     

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous confused why this code did not work.

      =IF(COUNTROWS(FILTER(Table,Table[Filed1]=EARLIER(Table[Filed1])&&Table[Filed2]=EARLIER(Table[Filed2])&&Table[Filed3]=EARLIER(Table[Filed3]))),"Yes","No")

      could you show the snapshot?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        wdx223_Daniel Apologies, couldn't reply earlier as I was away. I tried using the above code but it is taking too much time and power bi runs out of memory as I have 3 millions rows. Is there any other way around to solve this?