March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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 |
Solved! Go to 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 ) )
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!!!
No - sometimes it will be 200 values in that field, hence wanting the dax [Client Locations] to not repeat locations that it looks up
Is client inscope always 3 letters at all times?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |