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 "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!

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

     

5 Replies

  • 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

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

       

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

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

        Anonymous 

         

        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

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