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.
I've been searching for awhile but can't quite find a query to return what I'm hoping to get. To keep it simple, I'm trying to write a query that can have multiple correct returns and, if I'm not asking for the moon, returns them all in the same row. Example:
I have two tables like so:
Assets
ID Number | Target Viscosity |
1 | 5.9 |
2 | 8 |
3 | 23 |
Fluids
Fluid ID | Min Viscosity | Max Viscosity |
1 | 4.5 | 12 |
2 | 7 | 18 |
3 | 15 | 30 |
What I'd like to see is this:
Assets
ID Number | Target Viscosity | Fluid ID |
1 | 5.9 | 1 |
2 | 8 | 1, 2 |
3 | 23 | 3 |
This pseudo-logic in my head is something along the lines of
IF(AND(Assets[TargetViscosity] > Fluids[MinViscosity],Assets[TargetViscosity] < Fluids[MaxViscosity]), ...Return all possible matches in a single row with a deliminator..., 0)
Anyone got any ideas?
Solved! Go to Solution.
hi @denealst
try to add a column like this:
Columnn =
VAR _target = [Target]
VAR _table =
CALCULATETABLE(
VALUES(fluids[ID]),
fluids[Min]<=_target
&&fluids[Max]>=_target,
ALL(Assets)
)
RETURN
CONCATENATEX(
_table,
fluids[ID],
", "
)
it shall work like this:
Marvelous! Late in the evening it came to me that I was trying to build another array and would need an intermediate table to populate out the results of the query and then concaten it. But you have saved me a lot more time because I'd have actually created another table to do it. I didn't know you could create a table as a variable. Very cool! Thanks a ton for the input. I'm no data analyst, just a guy trying to get an edge on how I do my job so folks like you are heroes in my book.
hi @denealst
try to add a column like this:
Columnn =
VAR _target = [Target]
VAR _table =
CALCULATETABLE(
VALUES(fluids[ID]),
fluids[Min]<=_target
&&fluids[Max]>=_target,
ALL(Assets)
)
RETURN
CONCATENATEX(
_table,
fluids[ID],
", "
)
it shall work like this:
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |