topic Re: Count of records processed out of order in DAX Commands and Tips

Count of records processed out of order

metcala — Wed, 11 Oct 2023 14:09:12 GMT

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

Re: Count of records processed out of order

some_bih — Wed, 11 Oct 2023 14:28:33 GMT

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!

Re: Count of records processed out of order

metcala — Wed, 11 Oct 2023 14:32:20 GMT

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

Re: Count of records processed out of order

Anonymous — Wed, 11 Oct 2023 14:36:55 GMT

hii,

Total_Correct_Applications = CALCULATE(
COUNTX(
FILTER(

VALUES('Status'[ID]),

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

Re: Count of records processed out of order

metcala — Thu, 12 Oct 2023 10:43:04 GMT

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

Re: Count of records processed out of order

Dangar332 — Thu, 12 Oct 2023 13:51:04 GMT

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.

Re: Count of records processed out of order

Anonymous — Thu, 12 Oct 2023 14:05:10 GMT

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)))

Re: Count of records processed out of order

metcala — Thu, 12 Oct 2023 15:08:18 GMT

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!

Re: Count of records processed out of order

Dangar332 — Thu, 12 Oct 2023 16:18:38 GMT

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