cancel
Showing results for
Did you mean:
Helper I

## Classifying an order based on multiple results within the order

I have a table that looks like this:

 Order # Date requested by Actual delivery date item ID Qty ordered Qty delivered Result 54386 5/4/2020 5/7/2020 238 347 347 Late 54386 4/17/2020 4/16/2020 192 220 220 On time 79345 6/3/2020 6/1/2020 681 15 15 On time 79345 6/17/2020 6/5/2020 356 110 110 On time 40321 2/17/2020 2/21/2020 974 56 56 Late 40321 3/1/2020 2/13/2020 606 89 89 On time 40321 4/12/2020 4/9/2020 135 200 200 On time

The "Result" column is a calculated field that determines whether the item on an order was on time or late. In most cases, an order will have multiple items on it and each item may have different delivery dates requested by the customer. I want to set up an additional column/measure that looks at the order as a whole and if one item on that order was late, then the whole order would be classified as late. Below is my desired result. What would the formula look like for that? Any help would be appreciated!

 Order # Overall Result 54386 Late 79345 On Time 40321 Late
1 ACCEPTED SOLUTION
Community Support

Hi @jakeudy ,

``````Column =
VAR x =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Order #] ),
'Table'[Actual delivery date] > 'Table'[Date requested by]
)
)
RETURN
IF ( x > 0, "Late", "On time" )
``````
``````Measure =
VAR x =
COUNTROWS (
FILTER ( 'Table', 'Table'[Actual delivery date] > 'Table'[Date requested by] )
)
RETURN
IF ( x > 0, "Late", "On time" )
``````

For more details, please see the attachment.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

@jakeudy , Try a new measure like

``maxx(summarize(Table,Table[order], "_1", calculate(countrows(Table),Table[Result]= "Late")),if([_1]>=1,"Late","On-Time"))``
Helper I

This sort of worked, however its not quite what I'm looking for. When I view the data in table format it works correctly. However, when I try to view it in a different format such as a card or a column chart, it messes up the calculation. Ideally I would like to use this measure to see what percentage of orders are on time and what percentage are late. So I'd like to build a 100% stacked column chart with this measure as the legend, and the count (distinct) of orders as the value. Is there something I can add to the formula to accomodate this? Do I need to change it from a measure to a column? I've noticed that using a measure in the legend of a visual often does not work. Thank you for your help! @amitchandak

Community Support

Hi @jakeudy ,

``````Column =
VAR x =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Order #] ),
'Table'[Actual delivery date] > 'Table'[Date requested by]
)
)
RETURN
IF ( x > 0, "Late", "On time" )
``````
``````Measure =
VAR x =
COUNTROWS (
FILTER ( 'Table', 'Table'[Actual delivery date] > 'Table'[Date requested by] )
)
RETURN
IF ( x > 0, "Late", "On time" )
``````

For more details, please see the attachment.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

This worked perfectly, thank you!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors