DAX vs power query for Summariing data ?

[Tejinder]

Thanks for clicking on this
This is my sample data:

BarcodeID		Order ID	BOX sequence	TotalBoxes	Received(Y/N)	received
100620559		1959	1	4	Y	2023-07-18 8:16
100620565		1959	4	4	Y	2023-07-18 8:16
100620566		1959	2	4	Y	2023-07-18 8:16
100620567		1959	3	4	N	null
100673629		1961506	4	4	Y	2023-07-21 9:01
100673630		1961506	2	4	Y	2023-07-21 9:01
100673631		1961506	3	4	Y	2023-07-21 9:01
100673632		1961506	1	4	Y	2023-07-21 9:01
100686199		196	6	7	Y	2023-07-21 2:16
100686200		196	7	7	Y	2023-07-21 2:16
100686201		196	5	7	Y	2023-07-21 2:16
100686203		196	3	7	Y	2023-07-21 2:52
100686204		196	4	7	Y	2023-07-21 2:13
100686205		196	1	7	Y	2023-07-21 2:13
100686206		196	2	7	Y	2023-07-21 2:13
100682964		19616002	5	5	N	null
100682965		19616002	4	5	N	null
100682966		19616002	3	5	N	null
100682967		19616002	2	5	N	null
100682968		19616002	1	5	N	null

This is from an inventory scan; scans are done daily on barcode levels.
Every order has multiple boxes and unique barcodes for those boxes, sometimes some or all are not scanned (missing). I would like to summarize it into partially missing, missing or complete, For example:  

Order ID	Status
1959	Partial missing
1961506	Complete
196	Complete
19616002	Missing

There are time stamps for every scan and sometimes some of the orders are scanned multiple times and new records are created, it doesn’t replace existing scan or lack of.

Way I tried to achieve this is by counting time stamps in power query and group by Order ID and compare to TotalBoxes Col, but that didn’t work as it was adding all instances from multiple days, for example: Order ID 1959 would show 6 scans but only have 4 boxes. Because some boxes were scanned at a later date.

How can I do this easily in DAX?

[Anonymous · Accepted solution]

Hi @Tejinder ,

Try this measure:

Status = var _countid=CALCULATE(COUNT('Table'[Order ID]),FILTER(ALLSELECTED('Table'),[Order ID]=MAX('Table'[Order ID])))
var _countreceived=CALCULATE(COUNT('Table'[received]),FILTER(ALLSELECTED('Table'),[Order ID]=MAX('Table'[Order ID])&&[received]<>BLANK()))+0
return SWITCH(TRUE(),_countreceived=0,"Missing",_countid=_countreceived,"Complete",_countid<>_countreceived,"Partial missing")

Best Regards,

Stephen Tao

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

[sanalytics]

Hello @Tejinder 
Based on your shared data and explanation , i have used ReceivedY/N column for achiving your desired result.

Please find the below DAX code

Output =

VAR _1 =

SUMMARIZE(

'Table','Table'[Order ID],

"TotBox",Max( 'Table'[TotalBoxes] ),

"Count",

COUNTROWS(

FILTER(

'Table', 'Table'[Received(Y/N)] = "Y"

) )

)

VAR _2 =

ADDCOLUMNS(

_1,"Status",

IF(

[Count]= BLANK(),"Missing",

IF(

[Count] = [TotBox],"Completed",

"Partially Completed"

) ) )

VAR _Result =

SELECTCOLUMNS(

_2,"OrderID",[Order ID],

"Status",[Status]

)

RETURN

_Result

Here it is the screenshot

Hope this will help you.

Regards

sanalytics

If it is your solution then please like and accept it as solution

[Tejinder]

This works but doesn't work for me operationally. 
I can't have it in a separate table. it complicates it as some orders are scanned on different days, 

Let's assume Order 1959 was missing one piece but it was scanned at a later date, this would show it as completed. 

Instead of adding a column, can it be a measure so i can filter by time/date and then have it reflect if the order is complete partial or missing?

[Idrissshatila]

Hello @Tejinder ,

check the summarizing dax function and tru applying it https://learn.microsoft.com/en-us/dax/summarizecolumns-function-dax

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin