cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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"),"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

1 ACCEPTED SOLUTION
Helper I

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
6 REPLIES 6
Helper I

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
Impactful Individual

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.

Helper I

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.

Impactful Individual

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

Super User

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?

Best regards

Denis

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors