Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.