Forum Discussion
Helper VTrouble with new OFFSET() function and ignoring Blanks
Community SupportHi bvy ,
Please try to modify the measure _range:
_range =
var _a = ABS(CALCULATE([_avg], OFFSET(-1, , ORDERBY(Dates[Date]))) - [_avg])
return IF(ISBLANK([_avg])||ISBLANK([_prevAvg]),BLANK(),_a)Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper Vv-jianboli-msft Hey that works really well, thank you. Except for one problem. When I publish to the Service, I get an unspecified error from that measure when it's tied to a period slicer that I use. See below. This is from my actual production solution, but the measure shown is equivalent to _rangeAvg in the solution here. Works fine in Desktop. And it works fine in the Service if I eliminate the OFFSET function from the underlying _range measure calculation. Makes me think there's something buggy about it since it's a new addition to DAX (Dec. 2022).
- v-jianboli-msft
Hi bvy ,
This issue has been submitted internally.
Please be patient to wait for fixing. If there is any news, I will update it here.
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- bvy
v-jianboli-msft Thank you so much! Is there a case or ticket number you can share with me? This is pretty important for our project, even though we have a workaround for now...