Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
E_K_
Helper II
Helper II

Concatenate a list of values based on a related list of values

Hi

 

I have 2 tables that are related - 'Client Locations' and 'Data' using the [Client] column that is in both datasets. For the purposes of my requirement, I want a measure (or calculated column) than will return the list of client primary locations based on the values in the [Clients in scope] column in 'Data'.

 

How do I do this? I want the DAX to return the column in bold below in the second table . Can we also have the DAX not return duplicate locations like in the sample I show?

 

'Client Locations' table

ClientPrimary 
aAMRS
bEMEA
cAPAC
dEMEA

 

'Data' table

 

ItemClients in scopeClient locationsClient
1a,b,cAMRS, EMEA, APACa
1a,b,cAMRS, EMEA, APACb
1a,b,cAMRS, EMEA, APACc
2b,c,dAPAC, EMEAb
2b,c,dAPAC, EMEAc
2b,c,dAPAC, EMEAd
1 ACCEPTED SOLUTION
ahmedoye
Responsive Resident
Responsive Resident

You can create a calculated column with the formula below: If the formula works for you, kindly mark as solution to allow others who may have similar issues find it easily.

Column =
VAR ClientsInScope = Data[Clients in scope]
VAR ComputeTable =
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                ALL ( 'Client Locations'[Client], 'Client Locations'[Primary] ),
                "Check", IF ( CONTAINSSTRING ( Data[Clients in scope], [Client] ), [Primary], "" )
            ),
            "Primary", [Check]
        )
    )
VAR Extraction =
    CONCATENATEX ( ComputeTable, [Primary], ",", [Primary], ASC )
RETURN
    MID ( Extraction, 2, LEN ( Extraction ) )

View solution in original post

4 REPLIES 4
ahmedoye
Responsive Resident
Responsive Resident

You can create a calculated column with the formula below: If the formula works for you, kindly mark as solution to allow others who may have similar issues find it easily.

Column =
VAR ClientsInScope = Data[Clients in scope]
VAR ComputeTable =
    DISTINCT (
        SELECTCOLUMNS (
            ADDCOLUMNS (
                ALL ( 'Client Locations'[Client], 'Client Locations'[Primary] ),
                "Check", IF ( CONTAINSSTRING ( Data[Clients in scope], [Client] ), [Primary], "" )
            ),
            "Primary", [Check]
        )
    )
VAR Extraction =
    CONCATENATEX ( ComputeTable, [Primary], ",", [Primary], ASC )
RETURN
    MID ( Extraction, 2, LEN ( Extraction ) )

This works - had to edit the code slightly to make it work but that was because of the data types I supplied in my original post. Thank you!!!

E_K_
Helper II
Helper II

No - sometimes it will be 200 values in that field, hence wanting the dax [Client Locations] to not repeat locations that it looks up

ahmedoye
Responsive Resident
Responsive Resident

Is client inscope always 3 letters at all times?

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.