Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Need help to accomplish the following.
I have the following tables:
Reference Table
Group | Criteria 1 | Criteria 2 | Criteria 3 |
HD01 | MR | HR | K, L |
HD02 | MZ | RC | A, B, C |
HD03 | MS | DC | F, M |
Production (Results) Table
Results Device | Results 1 | Results 2 | Results 3 |
DVC1 | MR | HR | L |
DVC2 | MZ | RC | A |
DVC3 | MS | DC | F, M |
DVC4 | MR | HR | K |
DVC5 | MS | DC | F |
Need to match device to group based on all criteria columns.
End result required:
Group | Results Device |
HD01 | DVC1 |
HD02 | DVC2 |
HD03 | DVC3 |
HD01 | DVC4 |
HD03 | DVC5 |
Solved! Go to Solution.
Hi, @chapmanbry
Based on your description, you may create a calculated column or a measure as below. The pbix file is attached in the end.
Calculated column:
Re Column =
var tab =
FILTER(
Reference,
IF(
SEARCH(Production[Results 1],Reference[Criteria 1],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(Production[Results 2],Reference[Criteria 2],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(Production[Results 3],Reference[Criteria 3],,-1)>0,
TRUE(),FALSE()
)
)
return
CONCATENATEX(
tab,
[Group],
","
)
Measure:
Re Measure =
var tab =
FILTER(
Reference,
IF(
SEARCH(SELECTEDVALUE(Production[Results 1]),Reference[Criteria 1],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(SELECTEDVALUE(Production[Results 2]),Reference[Criteria 2],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(SELECTEDVALUE(Production[Results 3]),Reference[Criteria 3],,-1)>0,
TRUE(),FALSE()
)
)
return
CONCATENATEX(
tab,
[Group],
","
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @chapmanbry
Based on your description, you may create a calculated column or a measure as below. The pbix file is attached in the end.
Calculated column:
Re Column =
var tab =
FILTER(
Reference,
IF(
SEARCH(Production[Results 1],Reference[Criteria 1],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(Production[Results 2],Reference[Criteria 2],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(Production[Results 3],Reference[Criteria 3],,-1)>0,
TRUE(),FALSE()
)
)
return
CONCATENATEX(
tab,
[Group],
","
)
Measure:
Re Measure =
var tab =
FILTER(
Reference,
IF(
SEARCH(SELECTEDVALUE(Production[Results 1]),Reference[Criteria 1],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(SELECTEDVALUE(Production[Results 2]),Reference[Criteria 2],,-1)>0,
TRUE(),FALSE()
)&&
IF(
SEARCH(SELECTEDVALUE(Production[Results 3]),Reference[Criteria 3],,-1)>0,
TRUE(),FALSE()
)
)
return
CONCATENATEX(
tab,
[Group],
","
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |