Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Looking up values based on two variables

Hello,  I have two simple tables. In Transaction table I have SKU number and date when the SKU was purchased.  In my Refference  table I have SKU and date when the SKU Testing went live.  What I...
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please refer to my pbix file to see if it helps you.

    Create a column.

    Column =
VAR datelive =
    LOOKUPVALUE ( Refference[DateLive], Refference[SKU], Transactions[SKU] )
RETURN
    IF ( Transactions[PurchaseDate] >= datelive, "Live", "Not live" )

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

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

  • Anonymous's avatar
    Anonymous
    4 years ago

    This will work. Thank you so much

     

johnt75's avatar
johnt75
Super User
4 years ago

If your Reference table only contains unique values, i.e. 1 row per SKU, then you can create a 1-to-many relationship between the tables and use the RELATED function to add a calculated column to the Transactions table, e.g.

Was live = IF( Transactions[Purchase Date] > RELATED('Reference'[Date Live]), "Yes", "No")
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Is there any other way without doing relationship between these two tables?

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You could use LOOKUPVALUE but that will fail if it returns more than 1 value, so you really need the SKUs to be unique