topic Select, Return and Conectate multiple fields based on criteria in DAX Commands and Tips

Select, Return and Conectate multiple fields based on criteria

denealst — Tue, 03 Jan 2023 22:40:25 GMT

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?

Re: Select, Return and Conectate multiple fields based on criteria

FreemanZ — Wed, 04 Jan 2023 00:18:38 GMT

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:

Re: Select, Return and Conectate multiple fields based on criteria

denealst — Wed, 04 Jan 2023 13:58:51 GMT

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.