DAX or Power Query to create the Result Table?

DAX DAX Calculations
1

Hello all,

I’m facing a problem when trying to refresh a file which schedule the deliveries for a list of Open POs based on their availability of production quantities.

I have a data set with more than 200 Open POs and their respective products weekly production schedule and I’ve built several Power Query based versions to do the weekly allocations … but it’s taking forever to calculate and it usually breaks / blocks end-users Excel.

I’ve built a sample data set in a Power BI file where I’ve included both the INPUTS and the desired OUTPUT like in the picture below:

image
image1999×1155 212 KB

Final goal is to see the delivery Schedule like below (second page of the PBI report) and be able to interact with it:

image
image1980×1171 179 KB

LE: just to make sure the problem is completely explained: there are 2 INPUT tables and I need to create a 3rd Result table !

What would be your approach to get to this result?
Is it Power Query with Custom Functions as I started initially or is it DAX?

The file with the sample dataset is here:
Delivery Schedule by Product and Open PO.pbix (43.3 KB)
Thank you

Cristian

2

You would want to do this in DAX vs. PowerQuery so that it becomes more flexible. If done in PQ then the output is “Set” till the next data refresh, which means not responsive to slicers, filters, or any other ad-hoc request.

Took a look at the file and you need to build a data model.

Dimension Tables:
Date
Product

Fact Tables:
Production Schedule
PO

Could be more or less as I just took a quick look at your file.

This looks a lot like an Actual vs. Budget problem. The PO fact table has a granularity of the actual day by product and product number while the schedule has a granularity set to the product and month. Not a problem at all, just need to be aware as you set up the data model.

I’m sure there are examples here of Budget vs Actuals. I would take a look at those videos and see if anything clicks. once you have something feel free to come back here and ask questions.

3

Thank you @Nick_M.

Interesting comparison with Actuals vs Budget. I never saw it like this: was just trying to “allocate” Prod Qty to POs with custom functions in PQ.

I’ll have a look on this

4

The INPUTS are actually two Excel Files which are updated once a week.
In the file I uploaded they are two tables entered manually.
Having the results “fixed” until next refresh is actually the desired way to go.

I can do the model right so it can be easilly slice-ble as long as I can create the right resulting table with correct allocated quantities.

5

@cristian.angyal
can you take a look at the below. Obviously not familiar with your data like you are, but I’m running into an issue because we are trying to mix granularity, but not sure I’m looking at this correct

image
image1711×1262 421 KB

6

Hey @Nick_M,

You understood correctly the split of the quantities: 92 is the remaining Prod Qty available after delivering PO_01 completely . Then, as there is no more available Prod Qty in WK 38 the total qty of PO_02 (100 pcs) is completed in WK 39 with 8 more pcs.

Received Date is used only to determine the order of PO deliveries (a kind of Prio: the first received needs to be completed, then the next one … and so on).

image
image2001×1157 219 KB

Thanks for taking the time to understand the issue.

7

I started all my PQ solutions from POs with Open Qty Running Totals and merging them with Prod Qty also adding Prod Qty Running totals.

Started a new version today “backwards”: from Prod Qty and Running Totals towards “covering” all POs … but still in PQ.

I probably need to use CALCULATETABLE, SUMMARIZE and ADDCOLUMNS in DAX … and I’m better in Power Query … this is why I still try to figure it out in M.

8

I’ve found PQ to be terribly slow in doing those types of calculations. That is why I am trying to do as much in PQ to get everything set up and then use DAX calculated columns to get the running totals. There’s no real performance penalty for a calculated column in DAX vs. in PQ. Ill keep thinking though

9

Running Totals in PQ is not THAT bad as accessing previous row value with indexes … this is where PQ is reaaaaaaaaaaally slooooow :slight_smile:

10

Thank you @EnterpriseDNA Team for assigning it to DAX Calculations category.
Maybe your experts like @Melissa, @BrianJ, @Greg, @haroonali1000 can have a look on this and come up with their approach.

11

Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.