PBI Service Query referenced calculated table which does not hold any data

[mohanrkrishna19]

Hi All

Getting below error in Power BI service. Report works fine in desktop , works fine after first publish. But getting below error after date inputs are changed/data is refreshed.  Please find details on report design. Any insights is appreciated. Is there any better way to troubleshoot this issue in Power BI service?  Thanks!

Core tables:  (Imports)

Facts Data table  

Transaction table (Two key fields - Team , BU)

Two dates (Dt1 , Dt2) in a table populated from Sharepoint list

DAX Calculated tables as follows:

Dt1Facts = Data from Facts data with Date <= Dt1
Dt1latest Tx = Group by two keys on above table , apply MAXX to get max trn id 
Dt1Facts latest tx = Dt1latest Tx is joined with Facts Data table to get data for max trn ids

Dt2Facts = Data from Facts data with Date <= Dt2
Dt2latest Tx = Group by two keys on above table , apply MAXX to get max trn id 
Dt2Facts latest tx = Dt2latest Tx is joined with Facts Data table to get data for max trn ids

Variance table = Dax to calculate variance for all $ fields in Dt1Facts latest tx and Dt2Facts latest tx

Dt1latest Tx  or Dt2latest Tx comes up empty with no data when below error occurs. 

Couldn't load the data for this visual
The query referenced calculated table '####' which does not hold any data because evaluation of one of the rows caused an error.
Please try again later or contact support. 

[v-lid-msft]

Hi @mohanrkrishna19 ,

If  refresh in Power BI Desktop after date inputs changed works, I suggest that you should check the connect status of gateway to see if it can get the three tables normally, or in other word,  can you see the date of three core table changed after you refresh in Power BI Service.

If the refresh of underlying tables work fine , it may caused by the join funcuntion occor error. You can find the similar situation here: https://www.reddit.com/r/PowerBI/comments/9fh569/report_failing_for_mysterious_reasons/

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.