DAX optimization / 0 vs Blank issue

[Arnault_]

Dear Power bi users,

I have created 2 measures to achieve the following:

- measuring a difference between 2 dates (deviation) : "On-time delivery (dev.)"

- counting the number of occurence (deviation) : "On-time delivery (val.)"

Here are the 2 measures :

On-time delivery (dev.) = 
    VAR DeliveryRDD = 
        CALCULATE(
            AVERAGE('FACT Order'[req_delivery_date]);
                FILTER('FACT Order';'FACT Order'[req_delivery_date]<>BLANK()&&
                        'FACT Order'[TS09_transp_reception_date]<>BLANK()))
    VAR DeliveryADD = 
        CALCULATE(
            AVERAGE('FACT Order'[TS09_transp_reception_date]);
                FILTER('FACT Order';'FACT Order'[TS09_transp_reception_date]<>BLANK()&&
                        'FACT Order'[req_delivery_date]<>BLANK()))

    RETURN CALCULATE((DeliveryADD-DeliveryRDD))

 Note : I do not use DATEDIFF because I want to have the exact numeric value (not the absolute value). I have chosen average because MIN or MAX would give a date rather than a numeric value.

On-time delivery (val) = 
    CALCULATE(
        DISTINCTCOUNT('FACT Order'[packing_line_id]);
            FILTER(VALUES('FACT Order'[packing_line_id]);
                COUNTROWS(
                    FILTER('DIM Deviation';
                        [On-time delivery (dev.)]>=-'DIM Deviation'[Deviation value Selected]&& 
                        [On-time delivery (dev.)]<='DIM Deviation'[Deviation value Selected]&&
[On-time delivery (dev.)]<>BLANK()))
                >0))

I have a problem with the second measure, since DAX is unable to make a difference between 0 and NULL. 

Note: By default the deviation range is [-7;7], but the user can change it ("Deviation value Selected"). However this is not important. What matters is the issue regarding 0 and NULL.

Of course , any suggestion would be welcome to optimize the DAX code.

Thanks in advance for your help.

Here is a PBIX file with the dataset and the measures.

Pbix

[v-jiascu-msft]

Hi @Arnault_,

The file link is broken. Please update it. Please don't share anything sensitive.

Best Regards,

[Arnault_]

Hi @v-jiascu-msft,

Thanks for your reply. I have updated the link.

Best

[v-jiascu-msft · Accepted solution]

Hi @Arnault_,

Actually, the isblank() can do this job. It's simple to use. Please also try this formula. 

On-time delivery (val) new =
CALCULATE (
    DISTINCTCOUNT ( 'FACT Order'[packing_line_id] ),
    FILTER (
        VALUES ( 'FACT Order'[packing_line_id] ),
        [On-time delivery (dev.)] >= - 'DIM Deviation'[Deviation value Selected]
            && [On-time delivery (dev.)] <= 'DIM Deviation'[Deviation value Selected]
            && ISBLANK ( [On-time delivery (dev.)] ) = FALSE ()
    )
)

Best Regards,

[Arnault_]

Dear @v-jiascu-msft,

I am very, very grateful to you for having solved my problem. I wasn't that far from the solution however your proposition helped me better understand my mistake and improve my knowledge of DAX.

Many thanks for that.

Best