cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jakeudy
Helper I
Helper I

Classifying an order based on multiple results within the order

I have a table that looks like this:

Order #Date requested byActual delivery dateitem IDQty orderedQty deliveredResult
543865/4/20205/7/2020238347347Late
543864/17/20204/16/2020192220220On time
793456/3/20206/1/20206811515On time
793456/17/20206/5/2020356110110On time
403212/17/20202/21/20209745656Late
403213/1/20202/13/20206068989On time
403214/12/20204/9/2020135200200On 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
54386Late
79345On Time
40321Late
1 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" )

1.PNG

 

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.

View solution in original post

4 REPLIES 4
amitchandak
Super User
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"))

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 

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

1.PNG

 

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.

This worked perfectly, thank you!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

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

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

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!

MPPC 2023 PBI Carousel

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