Finding duplicate rows using DAX

[cwollett]

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.

[tamerj1]

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

[cwollett]

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!

[tamerj1]

@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

[Fowmy]

@cwollett 

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

[cwollett]

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.