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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Fill table based on information according to date.

Hello, 

 

I'm currently having dificultes in fiding a way to do this. 

 

Basically I have two tables, one with the order information: 

Product DateMaterial
O101/01/2022Iron 
O205/02/2022Plastic
O309/02/2023Iron 

 

And one with the supplier information: 

Supplier ContractDateLimitSupplies
StarkIndustries01/01/2023Iron
Plasticman01/01/9999Plastic
WaineEnterprises01/01/9999Iron

 

As you can see, the supplier of iron changed. 

 

What I would like to do is to create a column in the order table with this information. 

 

Basically this: 

 

Product DateMaterialSupplier
O101/01/2022Iron StarkIndustries
O205/02/2022PlasticPlasticMan
O309/02/2023Iron WaineEnterprises

 

Thank you very much for the help. 

 

@amitchandak @Greg_Deckler @FreemanZ @Sahir_Maharaj 

 

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

Supplier = 
VAR CurrentDate = Orders[Date]
VAR Material = Orders[Material]
RETURN
CALCULATE(
    FIRSTNONBLANK(Suppliers[Supplier], 1),
    Suppliers[Supplies] = Material,
    Suppliers[ContractDateLimit] >= CurrentDate
)

 

This formula uses a combination of the CALCULATE, FIRSTNONBLANK, and VAR functions to create a new column called "Supplier" in the Orders table. It looks up the supplier that supplies the material for each order and that has an active contract as of the order date.

 

Let me know if you might need further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

3 REPLIES 3
Sahir_Maharaj
Super User
Super User

Hello @Anonymous,

 

Supplier = 
VAR CurrentDate = Orders[Date]
VAR Material = Orders[Material]
RETURN
CALCULATE(
    FIRSTNONBLANK(Suppliers[Supplier], 1),
    Suppliers[Supplies] = Material,
    Suppliers[ContractDateLimit] >= CurrentDate
)

 

This formula uses a combination of the CALCULATE, FIRSTNONBLANK, and VAR functions to create a new column called "Supplier" in the Orders table. It looks up the supplier that supplies the material for each order and that has an active contract as of the order date.

 

Let me know if you might need further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
rajulshah
Memorable Member
Memorable Member

@Anonymous ,

 

Can you explain by what logic are you relating Product with Supplier?

Anonymous
Not applicable

I'm sorry but I don't understand your question... Do you want to know what is the relationship between these two tables? 

The problem starts there and goes all the way to what I've said. I've tried to relate the material with supplies, but I don't think that makes a lot of sense. 

I didn't give that information because, I have no clue on what relationship should there be in this type of situations. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.