Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
62 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |