cancel
Showing results for
Did you mean:
Frequent Visitor

## DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

Hello there!

I'm basically trying to calculate a KPI called OTIF (on time in full delivery) and OTIF%

Easiest to explain through a table:

 Order number Part number Difference in delivery date (days) OTIF 1000 ABC -2 0 1000 CDE 4 0 1000 EFG -2 0 1001 ABC -1 1 1001 CDE -1 1 1002 ABC 0 1 1002 CDE -1 1

So basically, if the order number is the same --> get the max of difference in delivery days --> if max is less than or equal to 0, OTIF = 1 but if it is greater than 0 = 0.

Then I want to calculate the percentage for all those lines:

2 orders was OTIF and 1 was not --> 2/3 OTIF. How do I only calculate the OTIF = 1 once per order number?

Thanks for any help. This has bothered me for a couple of days.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User IV

## Re: DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

Try the below calculation it can be used as a Column or Measure.

``````Column =
CALCULATE(
VAR __max = MAX( 'Table'[Difference in delivery date (days)] )
RETURN INT( __max <= 0 ),
ALLEXCEPT( 'Table', 'Table'[Order number] )
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.
Super User IV

## Re: DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

Try this

``````Measure =
DIVIDE(
CALCULATE(
DISTINCTCOUNT( 'Table'[Order number] ),
ALL( 'Table' ),
'Table'[Column] = 1
),
COUNTROWS( ALL( 'Table'[Order number] ) )
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

5 REPLIES 5
Super User IV

## Re: DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

Try the below calculation it can be used as a Column or Measure.

``````Column =
CALCULATE(
VAR __max = MAX( 'Table'[Difference in delivery date (days)] )
RETURN INT( __max <= 0 ),
ALLEXCEPT( 'Table', 'Table'[Order number] )
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.
Frequent Visitor

## Re: DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

Thanks alot! That was fast!

It worked at least as a calculated column to return 0 and 1.

Do you also happen to know how I could calculate the %?

I guess that would be a measure in a similar way, using allexcept also?

OTIF% = count distinct order number where OTIF = 1 / all orders

Super User IV

## Re: DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

Try this

``````Measure =
DIVIDE(
CALCULATE(
DISTINCTCOUNT( 'Table'[Order number] ),
ALL( 'Table' ),
'Table'[Column] = 1
),
COUNTROWS( ALL( 'Table'[Order number] ) )
)``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Highlighted
Frequent Visitor

## Re: DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

You're a genius!

Thanks alot Mariusz

Super User IV

## Re: DAX: When same values in column, evaluate the max and return 0 or 1 based on the condition

Best Regards,
Mariusz

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!