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

[E_K_]

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

Client	Primary
a	AMRS
b	EMEA
c	APAC
d	EMEA

'Data' table

Item	Clients in scope	Client locations	Client
1	a,b,c	AMRS, EMEA, APAC	a
1	a,b,c	AMRS, EMEA, APAC	b
1	a,b,c	AMRS, EMEA, APAC	c
2	b,c,d	APAC, EMEA	b
2	b,c,d	APAC, EMEA	c
2	b,c,d	APAC, EMEA	d

[ahmedoye · Accepted solution]

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 ) )

[E_K_]

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_]

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]

Is client inscope always 3 letters at all times?