topic Re: Counting orders based on their latest status in DAX Commands and Tips

Counting orders based on their latest status

Maciej1992 — Tue, 26 Sep 2023 11:16:21 GMT

Hello,

For some time now I am strugling with measure that will count orders based on their last status.

I tackled this from multiple angles and even ask ChatGPT for help, but couldn't find a solution.

My goal is table visualization with columns for:

names of statuses in first column (STATUS_DICTIONARY[NAME]),

number of orders which have given status as their latest,

% of GT

My model looks like this:

ORDERS[ID] - 1 to * - ORDERS_STATUS[ID_ORDER]

STATUS_DICTIONARY[ID] - 1 to * - ORDERS_STATUS[ID_STATUS]

And below is some sample data:

ORDERS

ID	NUMBER
1	23/01/1
2	23/01/2
3	23/01/3
4	23/01/4
5	23/01/5

ORDERS_STATUS

ID	ID_ORDER	ID_STATUS	DATE_CHANGE
1	1	1	26.09.2023
2	1	2	26.09.2023
3	2	1	26.09.2023
4	3	1	27.09.2023
5	4	1	27.09.2023
6	1	3	27.09.2023
7	2	5	28.09.2023
8	3	2	28.09.2023
9	3	3	29.09.2023
10	5	1	30.09.2023
11	4	2	30.09.2023

STATUS_DICTIONARY

ID	NAME
1	aaa
2	bbb
3	ccc
4	ddd
5	eee

Based on all above I expect result to look like this:

STATUS NAME	NUMBER OF ORDERS	% OF GT
aaa	1	20%
bbb	1	20%
ccc	2	40%
ddd	0	0%
eee	1	20%

I will be very gratefull for any help.

Thank you!

Re: Counting orders based on their latest status

ERD — Wed, 27 Sep 2023 16:22:35 GMT

Maciej1992 , here are the measures:

orders_amt = VAR t = FILTER ( ORDERS_STATUS, VAR order_id = CALCULATE ( SELECTEDVALUE ( ORDERS[ID] ) ) VAR maxDt = CALCULATE ( MAX ( ORDERS_STATUS[DATE_CHANGE] ), ORDERS_STATUS[ID_ORDER] = order_id, ALL ( ORDERS_STATUS ) ) VAR maxStatus = CALCULATE ( MAX ( ORDERS_STATUS[ID_STATUS] ), ORDERS_STATUS[DATE_CHANGE] = maxDt, ORDERS_STATUS[ID_ORDER] = order_id ) RETURN ORDERS_STATUS[ID_ORDER] = order_id && ORDERS_STATUS[ID_STATUS] = maxStatus ) RETURN COUNTROWS ( t )

% OF GT = VAR latestRows = [orders_amt] VAR allrows = CALCULATE ( SUMX ( VALUES ( STATUS_DICTIONARY[NAME] ), [orders_amt] ), ALL ( STATUS_DICTIONARY[NAME] ) ) RETURN latestRows / allrows

Re: Counting orders based on their latest status

Maciej1992 — Thu, 28 Sep 2023 12:49:15 GMT

Worked like a charm! Thank you very much. You truly are a Rockstar 🙂