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

[Anonymous]

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.

[Mariusz · Accepted solution]

Hi @Anonymous 

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.

[Mariusz · Accepted solution]

Hi @Anonymous 

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.

[Anonymous]

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

[Mariusz · Accepted solution]

Hi @Anonymous 

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.

[Anonymous]

You're a genius! 

Thanks alot Mariusz

[Mariusz]

Hi @Anonymous 

Glad I could help.

Best Regards,
Mariusz