cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Counting orders based on their latest status

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!

1 ACCEPTED SOLUTION
Super User

@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``````

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

2 REPLIES 2
Super User

@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``````

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

New Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors