Measures Breaking on Refresh

[Anonymous]

Hi there!

I've been using Power BI desktop and the web service for over a year now and have not run into any issues I couldn't solve through documentation or the forums until a few weeks ago. I have the strangest issue and I seem to have narrowed it down to some component of the refresh feature.

Some details:

 - My dataset is querying only a few thousand rows

 - I have many measures, multiple of which are memory heavy

 - All measures display and calculate correctly on Power BI desktop even after refreshes
 - Refreshes do not take abnormally long

So the main extent of my issue is that I have a number of measures that do not calculate properly when refreshed on the web service. With scheduled refresh turned off, I can usually republish the data set and it will fix it, but if scheduled refresh was on then I have to delete the data set from the workspace before republishing (a real annoyance because it involves unpublishing the app meaning I have to resetup all my permissions and RLS). Most of the simple measures work just fine, but the more complicated ones come up with values I can't figure out (so I haven't been able diagnose where things are going wrong). Also I have used an almost identical set of measures in a previous dataset with no troubles...

Any ideas what could be the cause? Even if we can find it, I'm still concerned why something works differently online vs on the desktop...

[v-jayw-msft]

Hi @Anonymous ,

Can you check the refresh history on the manage scheduled refresh page?

Have you tried refresh the dataset manually?

Best Regards,

Jay

[Anonymous · Accepted solution]

OH MY GOSH I FIGURED IT OUT!!! After over a month of driving me nuts I figured it out today. What a relief and a super annoying bug I hope Microsoft takes care of to avoid any confusion later on. So here it is: when querying out of Business Central, it outputs any empty date fields (in my use case the termination date of some employees) as 1/1/0001. Power BI desktop picks up on this and treats and displays all 1/1/0001 dates as blank cells. For many of my measures I had to filter for terminated employees, so I would naturally use "ISBLANK([Termination Date])". This all wokred fine and dandy in Power BI desktop. Apparently, Power BI service does not treat 1/1/0001 as blank cells, and thus all my measures that would usually filter out terminated employees, was actually filtering out non-terminated emplyees when refreshed online. Simply adding a replace "1/1/0001" with "null" to my query has fixed the issue. What a tremendous headache for such a simple fix!

[arjunislearning]

Thanks for sharing this info!

[lbendlin]

Need more details - are you on shared or dedicated capacity? if dedicated, which SKU?  How busy is it? How many evictions etc? 

[Anonymous]

I apologize for the delayed reply. I honestly don't know if it is shared or dedicated (or how to find out). I am not in IT, just a user. I know though that in our entire company I am the only 365 user that is publishing or creating anything on the Power Platform, so if traffic is only concerning our domain, then it is next to none...

If your line of thinking is that it is a ram issue I am inclined to agree, as I created a stripped out version of the dataset with a fraction of the data and no online queries (just data entered straight into Power Query tables) and that set works fine. Though I would have expected a memory issue to cause some sort of fail of the refresh, not result in gibberish numbers...

I apologize that I don't have more specific details.