Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
7 years ago
Solved

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

 

 

  • 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,

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    Hi Arnault_,

     

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

     

     

    Best Regards,

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee

        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,