topic Re: Finding duplicate rows using DAX in DAX Commands and Tips

Finding duplicate rows using DAX

cwollett — Fri, 05 May 2023 22:02:33 GMT

My scenario is a much larger dataset with longer strings for the two columns, but this should be generalizable so for simplicity, let's say I have a base table with the following data:

Group Name	Member Name
Group A	Alex
Group A	Betty
Group A	Charles
Group B	Alex
Group B	Betty
Group C	Alex
Group C	Betty
Group C	Charles

I know how to make a measure that concatenates the members into a single string:

Member List = CONCATENATEX( VALUES(Example[Member Name]), Example[Member Name], ", ", Example[Member Name], ASC )

Group Name	Member List
Group A	Alex, Betty, Charles
Group B	Alex, Betty
Group C	Alex, Betty, Charles

What I would like to be able to do is have a way to do a distinct on that Member List column so that I can find duplicates. I'd also like to do it using a temp table and not one in storage since I actually have something that's thousands of rows and the Member List string is going to be thousands of characters, hence the DAX preference.

So I'm looking for something like:

Group Name	isDuplicate
Group A	1
Group B	0
Group C	1

Any suggestions? I've tried to use ADDCOLUMNS(SUMMARIZE( ... where I add the concatenatex as an additional column, but when I try to do something like DISTINCTCOUNT on that new column, I get errors.

Re: Finding duplicate rows using DAX

Fowmy — Sat, 06 May 2023 07:28:01 GMT

cwollett

Can you explain you did you caculate 1 for Group A and 0 for Group B?

Re: Finding duplicate rows using DAX

tamerj1 — Sat, 06 May 2023 19:15:34 GMT

Hi cwollett

I hope this works. However, I followed the same methodology of using CONCATENATEX but didn't have the chance to test it. My guess is that even if it works it won't be so efficient. I think other approaches are possible but I need to be on my PC to test.
Duplicate =
VAR MemberList =
CONCATENATEX (
VALUES ( Example[Member Name] ),
Example[Member Name],
", ",
Example[Member Name], ASC
)
RETURN
SUMX (
ALL ( Example[Group Name] ),
INT (
CONCATENATEX (
CALCULATETABLE (
VALUES ( Example[Member Name] ),
ALLEXCEPT ( Example, Example[Group Name] )
),
Example[Member Name],
", ",
Example[Member Name], ASC
) = MemberList
)
) - 1

Re: Finding duplicate rows using DAX

cwollett — Mon, 08 May 2023 12:46:57 GMT

I would like to be able to return a way to say the Member List column from the table above that one is a duplicate. So since both A & C are "Alex, Betty, Charles" they are duplicates of one another. Since Group B is unique, it should not be shown as a duplicate.

Re: Finding duplicate rows using DAX

cwollett — Mon, 08 May 2023 14:04:38 GMT

Hello! So this does work perfectly on the smaller example scenario. But, as you mentioned, using the larger real data, performance is bad. I might have to make a related table with the list in a column and see what that does to the model size.

Thanks for trying!

Re: Finding duplicate rows using DAX

tamerj1 — Mon, 08 May 2023 15:55:46 GMT

cwollett

This is probably faster

Duplicate =
VAR MemberList =
VALUES ( Example[Member Name] )
RETURN
SUMX (
ALL ( Example[Group Name] ),
INT (
ISEMPTY (
EXCEPT (
CALCULATETABLE (
VALUES ( Example[Member Name] ),
ALLEXCEPT ( Example, Example[Group Name] )
),
MemberList
)
)
)
) - 1