Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
slav84
Helper I
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

SKUPurchaseDateWasItLive
1231231/1/2022 
3214531/3/2022 
4135671/5/2022 
3267891/14/2022 
9086871/7/2022 
5674931/18/2022 
2342361/2/2022 
5834671/15/2022 
7142671/25/2022 
7623451/9/2022 

 

Refference

SKUDateLive
1231231/1/2022
3214531/7/2022
4135671/13/2022
3267891/19/2022
9086871/25/2022
5674931/31/2022
2342362/6/2022
5834672/12/2022
7142672/18/2022
7623452/24/2022
2 ACCEPTED SOLUTIONS
v-rongtiep-msft
Community Support
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" )

vpollymsft_0-1648522558038.png

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.

View solution in original post

This will work. Thank you so much

 

View solution in original post

6 REPLIES 6
v-rongtiep-msft
Community Support
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" )

vpollymsft_0-1648522558038.png

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.

This will work. Thank you so much

 

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.

johnt75
Super User
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")

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.