User only the most current row of an order

[boyddt_mn]

Hello, thank you in advance for any help offered. I am trying to put together an WIP chart for our plant. Each order can have anywhere for 5 to 25 operations. We have 3 different measurements in Power BI - Due in 5 Days, Past Due, Aged that are based on the Wait Time column. I need to present a stacked bar chart with these three columns based on the last OpAc completed. Right now the graph is using all rows so for the example included it would list 11 entries as 'Due in 5 Days' and not a single entry. I will get my pbix file onto my dropbox this afternoon.

Order	Material	OpAc	Postg date	Actual Release Date	Basic finish date	MRP Controller	MRP_Desc	Department	Wait Time
99999999	XX.YYY.ZZZ-AA	10	6/10/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	2
99999999	XX.YYY.ZZZ-AA	20	6/10/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	2
99999999	XX.YYY.ZZZ-AA	30	6/10/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	2
99999999	XX.YYY.ZZZ-AA	40	6/11/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	1
99999999	XX.YYY.ZZZ-AA	50	6/11/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	1
99999999	XX.YYY.ZZZ-AA	60	6/11/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	1
99999999	XX.YYY.ZZZ-AA	70	6/11/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	1
99999999	XX.YYY.ZZZ-AA	80	6/11/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	1
99999999	XX.YYY.ZZZ-AA	90	6/11/2020	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	1
99999999	XX.YYY.ZZZ-AA	100	NULL	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	NULL
99999999	XX.YYY.ZZZ-AA	110	NULL	6/9/2020	7/9/2020	132	Screw I V Line Met	Screw 1	NULL

[v-zhenbw-msft]

Hi @boyddt_mn ,

We have a little confused about your issue, what is the last OpAc completed.

And [Basic finish date] doesn’t contain the 5 days data, so there isn’t 11 entries as 'Due in 5 Days’.

Could you please show the exact expected result based on the table that you have shared?

It will be helpful if you can show us the exact expected result based on the tables.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[boyddt_mn]

I've updated both the the pbix files and the spreadsheet which shows a couple of orders that are 5 days and past due. Below is a snippet showing MRP Controller 112 and Order 9960. The graphic shows that for Past Due there 11 items when in fact there is a single order with 11 steps. I need the graphic to show only 1 for past due. Likewise for 5 Days, it shows 15 though it is a single order - 9959 - with 15 steps. The graphic should only show a single entry for 5 Days.

Let me know if there is anything else you need.

MRP Controller 112Order 9960

[v-zhenbw-msft · Accepted solution]

Hi @boyddt_mn ,

We can create three measures to replace the columns in Column values to meet your requirement.

5 Days measure = CALCULATE(DISTINCTCOUNT(SAP_WIP[Order]),FILTER(SAP_WIP,SAP_WIP[5 Days]=1))

Past Due measure = CALCULATE(DISTINCTCOUNT(SAP_WIP[Order]),FILTER(SAP_WIP,SAP_WIP[Past Due]=1))

Current measure = CALCULATE(DISTINCTCOUNT(SAP_WIP[Order]),FILTER(SAP_WIP,SAP_WIP[Current]=1))

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[boyddt_mn]

Here is the link to the files

https://www.dropbox.com/sh/d6gfg9kns6jdtpa/AAANNd305bBeWR3pHJCh-TEla?dl=0

The two files are Community_Example_SAPWIP.pbix & SAP_WIP.rpt.xlsx

Thank you for your help

[aj1973]

Hi @boyddt_mn 

Not sure what do you mean by last OpAc and/or depends on which of the dates in your table. However you can add the OpAc field to the pane filters and apply your needs on how you want your Visual to act

Regards