Count of records processed out of order

[metcala]

Hi

Really struggling to write a DAX measure that provides a total of applications that have been completed out of order.

I have the following tables:

Status

ID	Status	Status Updated
1	Started	1/1/23
1	Pre-checks	2/1/23
1	Processing	3/1/23
1	Complete	4/1/23
2	Started	1/1/23
2	Processing	2/1/23
2	Pre-checks	3/1/23
2	Complete	4/1/23
...	...	...

Order

Status	Order
Started	1
Pre-checks	2
Processing	3
Complete	4

In the above example I'd expect:

1 complete application

1 incorrect order 

Any pointers would be really really appreciated!

Thanks

[Dangar332 · Accepted solution]

hi, @metcala 
 tyr below measure 
 

Adjust Name For YourTable

countofid =

var a = ADDCOLUMNS(VALUES(status[id]),"a1",CALCULATE(COUNT(checkingtable[status])))

return

COUNTAX(FILTER(a,[a1]>3),status[id])

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

[metcala]

Thank you so much! That's worked perfectly.

Sorry if this question isn't allowed but I am trying to decipher exactly how this works so I don't need to ask again. I don't suppose you could explain what is happening in the variable and COUNTAX function?

Many thanks!

[Dangar332]

hi, @metcala 

i am gonna upload pbix. file in which i mention little bit description so refer Here 

and for more dax knowledge refer Sqlbi website

[Abhinav054]

hii,

Total_Correct_Applications = CALCULATE(
COUNTX(
                FILTER(

                             VALUES('Status'[ID]),

                                CALCULATE(MAX('Status'[Order])) = MAX('Status'[Order]) ), 1 ) )

[metcala]

Hi

Thanks so much for the reply but it seems to be returning 2 for the example above.

For clarity, I am looking for it to:

Count an ID if each stage in date order for that ID is completed in the order defined in the Stage Order table

Thanks

[Abhinav054]

Once try this

Total_Correct_Order_Applications =
CALCULATE(
                 COUNTAX(
                                 VALUES('Status'[ID]),
                                  VAR MaxOrder = MAX('Order'[Order])
                RETURN
                              IF (
                                          NOT(ISBLANK(MaxOrder)) &&
                                           COUNTROWS(FILTER('Status', 'Status'[Order] = MaxOrder)) > 0,
                                                                                         1, 0)))

[some_bih]

Hi @metcala possible solution as measure for complete status below; adjust name for YourTable.

Measure complete=
CALCULATE(
DISTINCTCOUNT(YourTable[ID]),
YourTable[Status]="Complete"
)

Did I answer your question? Kudos appreciated / accept solution!

[metcala]

Hi, thanks for the reply but that doesn't account for checking if the ID has been completed out of order.