Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
In this fact table, I'd like to achieve the result in the column "Merge Product":
Here're the criteria:
- If Product A & B exists in the same Document No, AND
- Product A & B "Quantity" is the same, AND
- Product A & B "Order Type" is the same
Then Merge Product = B, else remain as "Product"
How can I achieve above using DAX formulas? Thank you for your help in advance!
Solved! Go to Solution.
@stevencsh
Add the following code as a new column:
New Column =
var __t = SELECTCOLUMNS( FILTER( ALL(Table2) , Table2[Document No] = EARLIER(Table2[Document No]) && Table2[Quantity] = EARLIER(Table2[Quantity]) && Table2[Order Type]=EARLIER(Table2[Order Type] )),"Prod" , Table2[Product] ) return
IF( "A" in __t && "B" in __t , "B" , Table2[Product] )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@stevencsh
Add the following code as a new column:
New Column =
var __t = SELECTCOLUMNS( FILTER( ALL(Table2) , Table2[Document No] = EARLIER(Table2[Document No]) && Table2[Quantity] = EARLIER(Table2[Quantity]) && Table2[Order Type]=EARLIER(Table2[Order Type] )),"Prod" , Table2[Product] ) return
IF( "A" in __t && "B" in __t , "B" , Table2[Product] )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy, there is a change in the requirement as follow:
Previous scenario:
- If Product A & B exists in the same Document No, AND
- Product A & B "Quantity" is the same, AND
- Product A & B "Order Type" is the same
Then Merge Product = B, else remain as "Product"
Additional scenario:
- If Product A & B exists in the same Document No, AND
- Product A "Quantity" <> Product B "Quantity", AND
- Product A "Order Type" <> Product B "Order Type"
Then Merge Product = B, else remain as "Product" (This would be the one circled in red). Notice that in row #3 it should still remain as A.
How can we modify the previous formula to achieve the new scenario?
You are the saviour! It works perfectly!! 😍 Thank you so much!
A question about your formula, what is the "Prod" used for?
Prod is the name I gave for the Product column which is required by the SELECTCOLUMNS function, it extracts the Product Column after the FILTER has done its job
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I see. Thanks for your clarification! Appreciate that.
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 |
---|---|
106 | |
101 | |
82 | |
79 | |
66 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |