Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi - I have two tables Sales and Warranty as below. I want to add a calculated column to the sales table that looksup the Warranty table, matches the Product and if the ship date is between the Ship Start and Ship End from warranty table, adds the text thats in the Warranty column of the Warranty table.
so something like => if(Sales Table[Product]=Warranty Table[Product] && Sales Table[ShipDate]>= Warranty Table[Ship Start] && ales Table[ShipDate]<= Warranty Table[Ship End], Warranty, blank())
How do I do this? I should be able to do this but I cant access another table when I am in the Sales Table.I do have ProductDim Table and ShipDateDim Tables that create a relationship between these tables. Ultimately What I want to do is, for the selected product, show a histogram of Sales qty over a period of time and just highlight the time periods that have Warranty issues. I think if I am able to create the columns with category then I can use it as a legend in the chart.
I would appreciate your advice. Thanks.
Solved! Go to Solution.
Add following measure in your sales table:
Warranty =
calculate(firstnonblank(Warranty[Warranty],1),
filter(Warranty,
Warranty[Product] =MAX( Sales[Product] ) &&
MAX(Sales[Date]) >= Warranty[Start Date] &&
MAX(Sales[Date]) <= Warranty[End Date]
)
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous
You can also use following DAX foumula to create a column.
Column = CALCULATE ( MAX ( Warranty[Warranty] ), FILTER ( Warranty, Sales[ShipDate] >= Warranty[Ship Start] && Sales[ShipDate] <= Warranty[Ship End] && Sales[Product] = Warranty[Product] ) )
Best Regards,
Herbert
@Anonymous
You can also use following DAX foumula to create a column.
Column = CALCULATE ( MAX ( Warranty[Warranty] ), FILTER ( Warranty, Sales[ShipDate] >= Warranty[Ship Start] && Sales[ShipDate] <= Warranty[Ship End] && Sales[Product] = Warranty[Product] ) )
Best Regards,
Herbert
what happens if a product have record with ship start/end date in existing date range of the same product, or it will never happen
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Good question. I was going to create a second column to deal with that. It will be rare but still a possibility.
Add following measure in your sales table:
Warranty =
calculate(firstnonblank(Warranty[Warranty],1),
filter(Warranty,
Warranty[Product] =MAX( Sales[Product] ) &&
MAX(Sales[Date]) >= Warranty[Start Date] &&
MAX(Sales[Date]) <= Warranty[End Date]
)
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |