Forum Discussion

cwollett's avatar
cwollett
Advocate II
3 years ago

Finding duplicate rows using DAX

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...
CONCATENATEX()
DAX
tamerj1's avatar
tamerj1
Community Champion
3 years ago

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's avatar
    cwollett
    Advocate II
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

      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