Forum Discussion

wbhite's avatar
wbhite
Helper I
2 years ago

Getting a value from another table using a lookup value and date comparison

Hello,

I've provided the image below to better illustrate my needs, but I'm having issues getting a value from another [non-related] table using a single lookup value as well as checking to see if a date exists between two other dates in the table withthe return value.  I've tried various things but have had little luck so far.  Hopefully there's a simple DAX formula I can use.  Thanks in advance.

 

Need Help

4 Replies

  • tzvetkov_b87's avatar
    tzvetkov_b87
    Helper I
    2 years ago

    Hi wbhite ,

     

    I would do the following:

     

    Lookup Calculation =
    VAR _date = 'Table1'[Date]
    VAR _lookup = 'Table1'[LookupValue]

    VAR _lookup_start_date = LOOKUPVALUE( Table2[StartDate], Table2[LookupValue], _lookup )
    VAR _lookup_end_date = LOOKUPVALUE( Table2[EndDate], Table2[LookupValue], _lookup )
    VAR _lookup_return_value = LOOKUPVALUE( Table2[ReturnValue], Table2[LookupValue], _lookup )

    RETURN
    SWITCH( TRUE(),
        // Check if the Lookup has returned a value - if it's blank means there was no Lookup Match.
        // In Excel you would get the #N/A, here you get blank instead.
        ISBLANK( _lookup_return_value ) = TRUE, "No Match",

        // If there has been a match, then check the dates are between start and end (both inclusive)
        _date >= _lookup_start_date && _date <= _lookup_end_date, _lookup_return_value,

        // If they are not between the dates, return the below text
        "Not within date range"
    )
     
     
    I've created two tables in Power BI called Table1 and Table2, each containing the columns you have listed.
    Then I create a new column in Table1, that takes the LookupValue from Table1 and checks it against Table2.
     
    In the switch statement I first check if that value exists in Table2. If the result is Blank, means it doesn't exits so I just return Blank.
     
    If it exists, then I check if it is equal to or bigger than the start date and less than or equal to the end date. If it is I return the value, otherwise I just return some text I have specified.
     
    You can modify those as you wish.
     
    Hope it helped you!
    • wbhite's avatar
      wbhite
      Helper I
      2 years ago

      This is very, very helpful.  Thank you.

      My only issue now is that I get an error that too many results were returned, even though I vetted Table2 to confirm that is no overlap in dates (it's only ~100 rows).  I should have mentioned that the Table1.Date value should be unique in Table2, but Table2.LookupValue has many (but with differing date ranges).

      • tzvetkov_b87's avatar
        tzvetkov_b87
        Helper I
        2 years ago

        I think I got confused, what is the unique value that joins the two tables together?

        Can LookupValue appear more than once in either table - is it not unique?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi wbhite ,

     

    Has your problem been solved? If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out. Thanks in advance.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.