Forum Discussion

denealst's avatar
denealst
New Member
3 years ago
Solved

Select, Return and Conectate multiple fields based on criteria

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 NumberTarget Viscosity
15.9
28
323

 

Fluids

Fluid IDMin ViscosityMax Viscosity
14.512
2718
31530

 

What I'd like to see is this:

 

Assets

ID Number

Target Viscosity

Fluid ID
15.91
281, 2
3233

 

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?

CALCULATE
CONCATENATEX
IF AND with multiple conditions
question
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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:

  • denealst's avatar
    denealst
    New Member
    3 years ago

    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.