Report Goes Blank After Scheduled Refresh

[Kfausch]

Hello,

I have an issue where I have a report that I made in the desktop version, it works perfectly and I have never had an issue refreshing. I published this to O365 to share with others and set up a scheduled refresh. When I publish the report at first it works fine, but after a scheduled refresh takes place, the majority of the report goes blank. Its worth noting that this particular report has 10+ pages, however only 1 is having trouble.

I never get an error on refresh, it says successful, but no data. If I republish it works again, until the next schedule refresh occurs.

Does anyone know what I could be done wrong? All of my other reports/pages work fine. Thank in advance for any help.

Here are the formulas used:

Cost Amount Consumption = -1 * CALCULATE([Cost Actual],'Value Entry'[Item_Ledger_Entry_Type] = "Consumption")

Cost Actual = CALCULATE(SUM('Value Entry'[Cost_Amount_Actual]), FILTER('Value Entry',[Expected_Cost]="False"))

Cost Per Unit = Divide([Cost Amount Consumption], [Units Output])

Units Output = CALCULATE(SUM('Item Ledger Entry'[Quantity]),'Item Ledger Entry'[Entry_Type] = "output")

Desktop version - > O365 -> relationships

[JBTech]

We used to run into this pretty frequently.  Our on-prem data warehouse would refresh its data from our live/production database every 15 minutes.  Our published Power BI reports were scheduled to import data from the warehouse every 1.5 hrs. starting at 6 AM.  We found that we could expect our data warehouse to refresh right on time every 15 minutes, e.g. 8:00, 8:15, 8:30, etc.  However, the scheduled refresh in the Power BI service is not as reliable.  I may have a report scheduled to refresh at 8:30 in the service but it may refresh anytime between 8:15 - 8:45, more of a windowed design.

If by coincidence the Power BI Service refreshed at the same time our on-prem data warehouse did it may pull no data.  This is due to how we refresh our data.  We truncate the data warehouse tables and then repopulate them based on specific conditions.  This causes us to see no data because there is truly no data within the table at the beginning of our data warehouse refresh because it was truncated.  Also, this may only affect a single table in the report, not the entire report would be blank.

Since then we've backed off how frequently we're refreshing our data warehouse which has helped our situation immensely.

[Anonymous]

I just encountered this problem and none of these proposed solutions were the answer. For some reason yet to be explained, I moved the raw data into another network folder and it worked. Both network folders were in the gateway, but the first path just continued to cause the report to go blank after the first refresh. Not sure why this worked, but I thought someone else might want to know.

[rajulshah]

@Kfausch @Anonymous , even I am facing the same issue. 

@Kfausch did you get hold of the cause of the issue? I am using shared dataset. Please let me know how you solved the issue.

[Kfausch]

From what I remember my issue was related to this formula below. In the desktop version of Power BI the field [Expected_Cost] would either be True or False, but in the web version after a refresh the data would change to either 0 or 1. I had to change the field in Power BI Desktop to a binary field so it would remain consistent. 

Cost Actual = CALCULATE(SUM('Value Entry'[Cost_Amount_Actual]), FILTER('Value Entry',[Expected_Cost]="False"))

I hope this helps,

Kevin Fausch

[Anonymous]

Do you have any Report Level or Page Level filters that could be changing when the refresh occurs?

For example you might have a "FieldName = True" type filter that is becoming false.

I've had refreshes causes issues like that due to datetime math being problematic when my local PC timezone is different to the server timezone and I'd had time dependant criteria (i.e. stuff from today, which the query sees the wrong data being time shifted).

[Kfausch]

I created a new page with no filters at all and seperated the fields, I was able to isolate the issue to these 3 fields. I cannot figure out why they go (Blank) when a scheduled refresh kicks in.

I dont have any formulas based on dates, although that is a good tip to know.

Still not sure what the problem is, my formulas seem okay. Thoughts?

[Anonymous]

It would seem that "Cost" is the common denominator between those values.  What is that based on? Is your costing table information dependant on some calculation that is made during an import?  I.e. Are you converting any figures that could potentially be multiplied by zero if there is a logic issue?

[Kfausch]

All the tables are pulled in via OData URL's and connected to a ERP database. I do not believe there is any calculations made during the import.

All the cost calculations where shown in the original post, all are pulled from the Value Entry table shown in the screen shot.

[Anonymous]

Then the answer must lie in the table relationships.  Something must be out of place in the other tables, causing the relationships to prevent any records being returned.