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