Dax formula returning duplicate items

[CMoneyMoore]

Hey there! 

Hoping I get some help with a measure I am working on. My ultimate goal is to get a concatenated list of contacts returned based on the data uploaded to a particular report. 

Getting more detailed, I have three tables uploaded in a report that is going to act as a tool for our team. The data file that is uploaded is provided by a customer, and one of the functions of the report is providing a list of providers that can service a particular customer based on the LANES of the shipments. 

My data is connected this way:

- Data file from the customer ('Data') > has a column called "Lane"

- 'Carrier Assignments (Master)' > has a column called "LANE" as well.

- 'Carrier Assignments (Master)' matches the LANE with a SCAC code (column called "SCAC")

- Finally a table called "Contacts" with a column called "SCAC"

Ideally what is being returned is a single string of contacts - however what is being currently returned is a list of contacts for every unique lane, resulting in many duplicates. I thought it would be easier, since I have a bar graph that shows the providers and what % of the shipments they can handle.

Here is the current measure:

Regional Providers =

CONCATENATEX(

    VALUES('Data'[Lane]),

    --Iterate over unique Lane values

    CALCULATE(

        CONCATENATEX(

            DISTINCT(

                FILTER('Contacts','Contacts'[SCAC] IN(

                SELECTCOLUMNS(

                    RELATEDTABLE('Carrier Assignments (Master)'),

                    "SCAC",

                    'Carrier Assignments (Master)'[SCAC]

                )

            )

            )),'Contacts'[Contact]

        )

    )

)

Any help is appreciated!

[CMoneyMoore]

Sure! 

Data Table ex:

Ship ID	Lane
1	AR_CA
2	CA_CA
3	ME_AR
4	AR_CA
5	ME_CA

SCAC Assignments ex:

Lane	Scac
AR_CA	ABCD
CA_CA	EFGH
ME_AR	IJKL
AR_OK	MNOP
ME_CA	QRST

Contacts ex:

Scac	Contact
ABCD	John.Smith@abcd.com
EFGH	Jack.Moore@efgh.com
IJKL	Jane.Smith@ijkl.com
MNOP	Jill.White@mnop.com
QRST	Leia.Organa@qrst.com

Sample return data:

Desired Result:

John.Smith@abcd.com;Jack.Moore@efgh.com;Jane.Smith@ijkl.com;Leia.Organa@qrst.com

No duplicates, only includes applicable contacts for lanes provided. 

Let me know if there's anything else I can provide!

[Ashish_Mathur · Accepted solution]

Hi,

I created this calculated column formula in the Data Table

Contact = related(Contacts[Contact])

and then wrote this measure

Measure = CONCATENATEX(VALUES(Data[Contact]),Data[Contact],", ")

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result in a simple Table format.