Power Bi DAX summarize with mutiple IF conditions

Question

Thanks for clicking on this

This is my sample data:

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:

1959 is partial 1961506 is complete 19616002 is 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 scans or lack of.

Way i tried to do it is by creating a table by summarize DAX measure, but it doesnt work for me operationally because some order are scanned multiple times, Lets take order 1959 for example. If it has 3/4 boxes scanned it will show as partial but if another box is scanned at a later date, it will count it as 4 scans and show it as complete.

I would like to have it in a measure so I can filter it by time/date

Here is my DAX:

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 is the result

I am not a trained Pbi user, whatever I have learned is from youtube and free tutorials

Any help is appreciated.

Answer 1

Way i tried to do it is by creating a table by summarize DAX measure, but it doesnt work for me operationally because some order are scanned multiple times,

So you're saying that part of your order when you import it can have duplicates? (Just making sure I'm answering the right question!) If that's the case then the part you're missing is PowerQuery. After you import your data, you can either Load it or Transform and then Load it. You want to Transform it, too. If you have duplicates of some/all of some orders, you can use Group By in PowerQuery to make PowerQuery basically remove the duplicates. You just add all the columns that make up a "group". in your case, it would be all the columns. Don't specify any aggregations - you don't need them. Then when that gets run, the results will be deduplicated, and you can just run your reports. The good thing about doing it in PowerQuery is that PowerQuery will do that every time you import data. (Behind the scenes it's writing a bunch of PowerQuery code, which it will run against all your incoming data from now on, because that will be part of the data flow.) then your resulting data will be deduplicated and you can just run your report.

Really good for a rookie! Keep at it!