Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
Hello,
I'm currently having dificultes in fiding a way to do this.
Basically I have two tables, one with the order information:
Product | Date | Material |
O1 | 01/01/2022 | Iron |
O2 | 05/02/2022 | Plastic |
O3 | 09/02/2023 | Iron |
And one with the supplier information:
Supplier | ContractDateLimit | Supplies |
StarkIndustries | 01/01/2023 | Iron |
Plasticman | 01/01/9999 | Plastic |
WaineEnterprises | 01/01/9999 | Iron |
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 | Date | Material | Supplier |
O1 | 01/01/2022 | Iron | StarkIndustries |
O2 | 05/02/2022 | Plastic | PlasticMan |
O3 | 09/02/2023 | Iron | WaineEnterprises |
Thank you very much for the help.
@amitchandak @Greg_Deckler @FreemanZ @Sahir_Maharaj
Solved! Go to Solution.
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.
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.
@Anonymous ,
Can you explain by what logic are you relating Product with Supplier?
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.
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |