Merging different criteria as one final value

[stevencsh]

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!

[Fowmy · Accepted 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] )

[stevencsh]

@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?

[stevencsh]

You are the saviour! It works perfectly!! 😍 Thank you so much!

A question about your formula, what is the "Prod" used for?

[Fowmy]

@stevencsh 

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

[stevencsh]

I see. Thanks for your clarification! Appreciate that.