cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oliverL
Frequent Visitor

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

Hi,

 

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

 

TABLE1

ValueStart IntervalEnd Interval
House15
Door610
Window1115

 

TABLE 2

AssetNumber
Sky7
Cloud90

 

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

 

TABLE2 Updated

AssetNumberValue
Sky7Door
Cloud90 

 

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

1 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

 

 

1.PNG

 

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

View solution in original post

4 REPLIES 4
harshnathani
Super User III
Super User III

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])

 

 

1.PNG

 

You can also try Merge Queries in Power Query

 

1.PNG

 

 

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

 

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

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

 

 

1.PNG

 

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

View solution in original post

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors