Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Merging different criteria as one final value

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 ...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 
    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] )

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

Anonymous 
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] )

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?