Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register 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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
84 | |
76 | |
73 | |
42 | |
36 |
User | Count |
---|---|
109 | |
56 | |
52 | |
48 | |
42 |