DAX to Return list of duplicate customers

[TomEnns]

I have a table called CHA_Customer that looks something like this: 

CustID	Name
12	Apple
23	Google
54	Microsoft
59	Microsoft
65	Apple
78	Apple

I want to add a measure called "DuplicateCustIDs" to the table to get the following output: 

CustID	Name	DuplicateCustIDs
12	Apple	65,78
23	Google
54	Microsoft	59
59	Microsoft	54
65	Apple	12,78
78	Apple	12,65

Essentially, I want to search through the Name column on the customer table to determine which of the customer IDs have duplicate names and then make a list of those duplicates in a measure. I very much want to do this in DAX rather than M if it's possible. I have been playing with the "Earlier" function and can't see to get it to work even for a count of duplicate much less a list. Hoping someone here can help me out 

[v-yueyunzh-msft]

Hi , @TomEnns 

According to your description, you want to show the duplicate customers' ID in visual. Right?

Here are the steps you can refer to :

(1)This is my test data:

(2)We ca  create a measure:

Measure = var _current_name = SELECTEDVALUE('Sheet2'[Name])
var _current_ID=SELECTEDVALUE('Sheet2'[CustID])
var _mul_t =SELECTCOLUMNS( FILTER( ALLSELECTED('Sheet2') , 'Sheet2'[Name]=_current_name && 'Sheet2'[CustID] <>_current_ID) ,"ID" , [CustID])
return
CONCATENATEX(_mul_t,[ID],",")

(3)Then we put the measure and the field you need in visual , and we will meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[TomEnns]

@tackytechtom  That seems to do the trick with on caveat, for some reason this is now my output: 

As you can see EPIC06 shows three times for EPICF but there are not three matches. 

[tackytechtom]

Hi @TomEnns ,

At this point, I think it's better if you provide a sample of your data. I have the feeling that there are other things that the formular needs to take into consideration due to the apparent data.

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @TomEnns ,

How about this:

And here the code in DAX:

Measure = 
VAR _custID = SELECTEDVALUE ( 'Table'[CustID] )
RETURN
CONCATENATEX ( 
    CALCULATETABLE ( 
        VALUES ( 'Table'[CustID]), 
        'Table'[CustID] <> _custID
    ), 
    'Table'[CustID], ", "
)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[TomEnns]

Hey @tackytechtom Thankyou for the solution, it looks to do exactly what I want if those two columns are the only columns in my table. Of course I didnt post that I have additional columns in my table such as: 

Company

Address

City

Country

SalesPerson

TaxCode

and so on. 

When I add any of these columns to the table visualization it appears to break the measure and it doesnt display properly. As an exmaple this is with only the CustID, Name, and Measure: 

If I add company I get nothing: 

If I add address it filters out the data to only 2 rows: 

Any Idea on how to modify the measure to ensure it will always output the duplicates even if there are other rows in the query? 

[tackytechtom · Accepted solution]

Hi @TomEnns 

Trying adding an ALLEXCEPT (Table, Table[Name] ) to your function:

Measure = 
VAR _custID = SELECTEDVALUE ( 'Table'[CustID] )
RETURN
CONCATENATEX ( 
    CALCULATETABLE ( 
        VALUES( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Name] ),
        'Table'[CustID] <> _custID
    ), 
    'Table'[CustID], ", "
)

result without the ALLEXCEPT:

result with ALLEXCEPT:

Does this solve your query? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[TomEnns]

I did some more digging into my dataset. It was my fault. This works exactly as needed. Thankyou!