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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors