cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Arnault_ Regular Visitor
Regular Visitor

DAX optimization / 0 vs Blank issue

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX optimization / 0 vs Blank issue

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 ()
    )
)

DAX-optimization-0-vs-Blank-issue

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: DAX optimization / 0 vs Blank issue

Hi @Arnault_,

 

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

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Arnault_ Regular Visitor
Regular Visitor

Re: DAX optimization / 0 vs Blank issue

Hi @v-jiascu-msft,

Thanks for your reply. I have updated the link.

Best

Community Support Team
Community Support Team

Re: DAX optimization / 0 vs Blank issue

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 ()
    )
)

DAX-optimization-0-vs-Blank-issue

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Arnault_ Regular Visitor
Regular Visitor

Re: DAX optimization / 0 vs Blank issue

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