Forum Discussion

JajatiDev's avatar
JajatiDev
Helper II
4 years ago
Solved

Excel Formula to DAX

Hi, I have the following excel formula, that retains the following values;   Allocated Pending Allocation Partial Allocation In Production   IF(OR([status]="Production",[status]="FactShipped"...
Need Help
  • JajatiDev's avatar
    JajatiDev
    4 years ago

    Hi,

    I was able to replicate the formula.

     

    Order Status =
    IF(OR('Orders'[status]="Production",'Orders'[status]="FactShipped"),"In Production",
    IF(AND('Orders'[delivery_type]="Ship as available",'Orders'[order_qty]='Orders'[confirmed_qty]),"Allocated",
    IF('Orders'[delivery_type]="Ship as available" && 'Orders'[confirmed_qty]>0,"Partial Allocation",
    IF('Orders'[delivery_type]="Full order consolidation" && SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders'[order_no])),'Orders'[order_qty])=SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders'[order_no])),'Orders'[confirmed_qty]),"Allocated",
    IF('Orders'[delivery_type]="Full order consolidation" && SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders '[order_no])),'Orders'[confirmed_qty])>0 ,"Partial Allocation",
    "Pending Allocation")))))
     
    Now the next step for me is to code this in M Language.
     
    Thanks,
    Dev
AliceW's avatar
AliceW
Power Participant
4 years ago

Why don't you create two measures for the two SUMIFs using CALCULATE?

Then you could just include them in the column/measure you want to build.

  • JajatiDev's avatar
    JajatiDev
    Helper II
    4 years ago

    Hi,

    I am not calculating anything here. The SUMIF you see are part of the logic statement to retain a text at the end.

     

    The logic is meant to retain the following;

    Allocated

    Pending Allocation

    Partial Allocation

    In Production

     

    This logic in excel looks at every table row of the specified fields to provide a consolidated order status.

     

    • AliceW's avatar
      AliceW
      Power Participant
      4 years ago

      I'm guessing you want a calculated column?

      Do you  have different tables? SUMIF seems to indicate at least two? If yes, why not connect them using ORDER NUMBER? Then you can just apply an IF(RELATED...).