topic Match values in a column with itself in DAX Commands and Tips

Match values in a column with itself

Anonymous — Thu, 12 Nov 2020 04:21:15 GMT

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.

Re: Match values in a column with itself

wdx223_Daniel — Thu, 12 Nov 2020 04:45:52 GMT

Anonymous try this in a calculated column

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

Re: Match values in a column with itself

amitchandak — Thu, 12 Nov 2020 05:09:57 GMT

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

Re: Match values in a column with itself

Anonymous — Thu, 12 Nov 2020 05:10:36 GMT

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.

Re: Match values in a column with itself

Anonymous — Thu, 12 Nov 2020 05:12:11 GMT

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.

Re: Match values in a column with itself

amitchandak — Thu, 12 Nov 2020 05:21:17 GMT

Anonymous , 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")

Re: Match values in a column with itself

Anonymous — Thu, 12 Nov 2020 05:48:31 GMT

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)

Re: Match values in a column with itself

amitchandak — Thu, 12 Nov 2020 06:01:13 GMT

Anonymous , 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

Re: Match values in a column with itself

wdx223_Daniel — Thu, 12 Nov 2020 06:41:30 GMT

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

Re: Match values in a column with itself

Anonymous — Fri, 13 Nov 2020 02:01:06 GMT

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

Re: Match values in a column with itself

Anonymous — Sun, 15 Nov 2020 23:48:07 GMT

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.

Re: Match values in a column with itself

wdx223_Daniel — Mon, 16 Nov 2020 00:36:11 GMT

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?

Re: Match values in a column with itself

Anonymous — Fri, 20 Nov 2020 02:55:36 GMT

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?

Re: Match values in a column with itself

Anonymous — Fri, 20 Nov 2020 16:36:54 GMT

Anonymous

Sure there is. Use Power Query.