Classifying an order based on multiple results within the order

[jakeudy]

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

[amitchandak]

@jakeudy , Try a new measure like

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

[jakeudy]

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 

[v-xuding-msft · Accepted solution]

Hi @jakeudy ,

I think you are talking about this:

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.

[jakeudy]

This worked perfectly, thank you!