Match values in a column with itself

[SachinFG]

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 respective fields to get the output. How can i do this in Power BI with the help of Dax.

Thanks in advance. 

[amitchandak]

@SachinFG , Something like this

if(countx(filter(Table, [merchant_id] =earlier([merchant_id]) && [Day] =earlier([Day]) && [user_account_id] = earlier([user_account_id])),[id])+0 >1,"Yes","No")

[SachinFG]

@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]

@SachinFG 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])))

[SachinFG]

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]

@SachinFG 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?

[SachinFG]

@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]

@SachinFG 

Sure there is. Use Power Query.

[amitchandak]

@SachinFG , new column in table 2

if(countx(filter(Table, Table[merchant_id] =(Table2[merchant_id]) && Table[Day] =(Table2[Day]) && Table[user_account_id] = (Table2[user_account_id])),Table[id])+0 >1,"Yes","No")

Refer the way to move value from one table to another https://www.youtube.com/watch?v=czNHt7UXIe8

[SachinFG]

@amitchandak : Can you help me in understanding the purpose of Table[id])+0 >1? The id is just some random nos.

[amitchandak]

@SachinFG , you can use earlier. If you can give example what you want. We can help better

[SachinFG]

Hi Amit,

  

Thanks for your reply. PLease see below screen shot. I want to match the 3 fields and add a column in one of the the tables and update it with Yes or No. 

 

[wdx223_Daniel]

@SachinFG try this in a calculated column

= CONCATENATEX(FILTER(Table,Table[Filed1]=EARLIER(Table[Filed1])),Table.[Filed2],",")

[SachinFG]

Hi @wdx223_Daniel wdx223

  

Thanks for your reply. PLease see below screen shot. I want to match the 3 fields and add a column in one of the the tables and update it with Yes or No.