Excel Formula to DAX

[JajatiDev]

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"),"In Production",
IF(AND([[delivery_type]]="ship as available",[[order_qty]]=[[confirmed_qty]]),"Allocated",
IF(AND([[delivery_type]]="ship as available",[[confirmed_qty]]>0,[[confirmed_qty]]<[[order_qty]]),"Partial Allocation",
IF(AND([[delivery_type]]="full order consolidation",SUMIF([order_no],[[order_no]],[order_qty])=SUMIF([order_no],[[order_no]],[confirmed_qty])),"Allocated",
IF(AND([[delivery_type]]="full order consolidation",SUMIF([order_no],[[order_no]],[confirmed_qty])>0,SUMIF([order_no],[[order_no]],[confirmed_qty])<SUMIF([order_no],[[order_no]],[order_qty])),"Partial Allocation","Pending Allocation")))))

My efforts are to add a column in the power bi report that would capture the result based on the logic. The excel formula looks at multiple fields to retain the above-mentioned values.

Thanks and Regards,

Jajati Dev

[JajatiDev · Accepted solution]

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]

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]

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]

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

[selimovd]

Hey @JajatiDev ,

Power BI is not Excel.

What do you want to do? Add column? Create a measure? What should the SUMIF represent? The sum on a row level? The sum for a specific column?

Please tell us how your table looks like and what you want as a result and then it's easier to help you.

Best regards

Denis

[JajatiDev]

Hi,

Yes, I do acknowledge Power BI is different from Excel.

All that I am trying to achieve is to add a column in the report replicating the excel formula to retain the text value.

Thanks and Regards,

Jajati Dev