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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors