Select, Return and Conectate multiple fields based on criteria

[denealst]

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?

[denealst]

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. 

[FreemanZ · Accepted 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: