How to return a value from a different table based on several conditions

[oliverL]

Hi,

I have a problem that I don't manage to fix. I have to tables similar as the following ones:

TABLE1

Value	Start Interval	End Interval
House	1	5
Door	6	10
Window	11	15

TABLE 2

Asset	Number
Sky	7
Cloud	90

What I am trying to achieve is TABLE2 to end up like this:

TABLE2 Updated

Asset	Number	Value
Sky	7	Door
Cloud	90

Edit: I am trying to see if the Number from TABLE2 falls under any of the intervals defined in TABLE1 (including both ends)

I have tried using the following DAX query but I only receive blank values:

Value = 

CALCULATE(FIRSTNONBLANKVALUE('TABLE1'[Value], TRUE()), FILTER('TABLE1', AND([Number] >= 'TABLE1'[Start Interval], [NUMBER] <= 'TABLE1'[End Interval]))

I really appreciate the help.

Thanks

[harshnathani]

Hi @oliverL ,

Not sure how tables are related, you can try the following DAX.

Create a Calculated Column

Look = LOOKUPVALUE('Table 1'[Value],'Table 1'[Start Interval],Table2[Number])

You can also try Merge Queries in Power Query

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[oliverL]

Hi @harshnathani ,

I am really looking to see if the number from Table2 falls into any of the intervals (including both ends). The lookupvalue function only works if the values are equal.

Thanks

[harshnathani · Accepted solution]

HI @oliverL ,

Create a Calculated Column

Look21 = 
VAR SearchValue =CALCULATE( MAX('Table2'[Number]))
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table1'[Value], "aa" ),
        FILTER (
            ALLNOBLANKROW ( 'Table1'[Start Interval] , Table1[End Interval]),
            'Table1'[Start Interval] <= SearchValue && Table1[End Interval] >= SearchValue      
        ),
        ALL ( Table1 )        
    )
    //SearchValue

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[oliverL]

Thanks! For some reason when I adapt this code to my real tables all I get is "aa". I will take a look to the transformations to see where the error may be coming, but the code clearly works. So I will mark it as solution.

Thanks again!