Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
Solved! Go to 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.
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" )
For more details, please see the attachment.
This worked perfectly, thank you!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
82 | |
66 | |
61 | |
46 | |
45 |