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!
