Forum Discussion

CMoneyMoore's avatar
CMoneyMoore
New Member
2 years ago
Solved

Dax formula returning duplicate items

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!
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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],", ")

     

3 Replies