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 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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |