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...
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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Anonymous 

         

        Sure there is. Use Power Query.