Forum Discussion
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.
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,
4 Replies
- v-jiascu-msftMicrosoft Employee
Hi Arnault_,
The file link is broken. Please update it. Please don't share anything sensitive.
Best Regards,
- Arnault_Resolver III
- v-jiascu-msftMicrosoft 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 () ) )
Best Regards,