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

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

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)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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