Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Arnault_
Resolver III
Resolver III

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

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.

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

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.

Hi @v-jiascu-msft,

Thanks for your reply. I have updated the link.

Best

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.

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.