Forum Discussion

oliverL's avatar
oliverL
Frequent Visitor
5 years ago
Solved

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 Value Start Interval End Interval House 1 5 Door 6 10 Window 11 15...
DAX Commands
Desktop
Help Requested
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
oliverL
Frequent Visitor
5 years ago

Hi Anonymous ,

 

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    oliverL
    Frequent Visitor
    5 years ago

    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!