cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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 need is in new column in Transaction table that wil say Live/Not Live. So I basically i have to look up the SKU and Purchase date from Transaction table and compare it to SKU and DateLive date in Refference table. And if the Date of Purchase is = or greater than DateLive, than I will get value "Live" In column WasItLive. Otherwise if the date of purchase was less then When the SKU testing DateLive then I want to get "Not Live" value.

So i need to know if sku was testing or not at the time of purchase.

Another thing is once the SKU is live for testing it stays live which is why i think i have to use >= when comparing purchase date vs DateLive.

I have been stuck on this problem for few hours. It seems simple but every fucntion i tried is not giving me correct results.

Any Idea?

Transactions

 SKU PurchaseDate WasItLive 123123 1/1/2022 321453 1/3/2022 413567 1/5/2022 326789 1/14/2022 908687 1/7/2022 567493 1/18/2022 234236 1/2/2022 583467 1/15/2022 714267 1/25/2022 762345 1/9/2022

Refference

 SKU DateLive 123123 1/1/2022 321453 1/7/2022 413567 1/13/2022 326789 1/19/2022 908687 1/25/2022 567493 1/31/2022 234236 2/6/2022 583467 2/12/2022 714267 2/18/2022 762345 2/24/2022
2 ACCEPTED SOLUTIONS
Community Support

Hi @slav84 ,

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.

Helper I

This will work. Thank you so much

6 REPLIES 6
Community Support

Hi @slav84 ,

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.

Helper I

This will work. Thank you so much

Community Support

Hi @slav84 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

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.

Super User

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")``
Helper I

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

Super User

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