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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bvy
Helper V
Helper V

Trouble with new OFFSET() function and ignoring Blanks

I have a Data table (UnitCounts), and a Dates table. I have a measure called _avg which averages unit counts by date. I have another measure meant to return the difference of the average in the current date from the average from the previous date. It looks like this: 

 

_range = ABS(

   CALCULATE(   [_avg],    OFFSET(-1, , ORDERBY( Dates[Date]))   )

   -
   [_avg]

)

 

How do I modify the measure to IGNORE rows where either the _avg is BLANK, or the _avg from the previous row/date (given by the OFFSET function) is BLANK? The default behavior seems to be to treat those values as zero and return a value. I want it to return blank in those scenarios. More detail in the screenshot below, and a PBIX is attached also. Thank you. 

 

bvy_0-1674596939827.png

 

https://www.icloud.com/iclouddrive/034mSDdynrXt6DEWBGSJwvfCw#imr

 

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-jianboli-msft
Community Support
Community Support

Hi @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:

vjianbolimsft_0-1674703532695.png

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.

@v-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). 

 

bvy_0-1674746437481.png

 

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.

@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... 

tex628
Community Champion
Community Champion

Hi @bvy

Try this: 

 

Dynamic Offset = 
VAR DatesFiltered = FILTER( ADDCOLUMNS( ALL(Dates) , "Value" , [_avg] ) , [Value] <> BLANK())
Return
SUMX( SUMMARIZE( Dates, Dates[Date] , "Value" , CALCULATE( [_avg]  , OFFSET( -1 , DatesFiltered , ORDERBY( Dates[Date] )))), [Value] )

 


Br, 
J


Connect on LinkedIn

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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