cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oscarrudnas Frequent Visitor
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 numberPart numberDifference in delivery date (days)OTIF

1000

ABC-20
1000CDE40
1000EFG-20
1001ABC-11
1001CDE-11
1002ABC01
1002CDE-11

 

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
Super User IV

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

Hi @oscarrudnas 

 

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.

View solution in original post

Super User IV
Super User IV

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

Hi @oscarrudnas 

 

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.

 

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

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

Hi @oscarrudnas 

 

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.

View solution in original post

oscarrudnas Frequent Visitor
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
Super User IV

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

Hi @oscarrudnas 

 

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.

 

View solution in original post

Highlighted
oscarrudnas Frequent Visitor
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
Super User IV

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

Hi @oscarrudnas 


Glad I could help.

 

Best Regards,
Mariusz

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors