cancel
Showing results for 
Search instead for 
Did you mean: 
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
Resolver III
Resolver III

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
Resolver III
Resolver III

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
Resolver III
Resolver III

Is client inscope always 3 letters at all times?

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors