Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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,
Hi @Arnault_,
The file link is broken. Please update it. Please don't share anything sensitive.
Best Regards,
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,
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |