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

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

      I see. Thanks for your clarification! Appreciate that.