Power BI Report not showing data after publishing to Service

[nasirali]

Hello,

I have designed a report using power bi desktop. Everything is working fine in power bi desktop. As soon as i publish the report to Power BI Service, the report shows data first time i access it. But after first refresh report is empty. Data is no longer there. All the tiles in the report are empty. If i publish the report again same thing happens data is there for one or two viewing then gone.

Any pointers will be highly appreciated. 

Regards,

[SuperCoolGuy]

TLDR: If your calculated column returns an error in any of the rows, you need to handle the errors in the DAX formula.

So, I had this same issue, however, the solution here was not relevant to me. I spent 2 days trialling many different things and finally solved my problem. I thought I would post here in case it helps anyone.

I have a page filter which is a Calculated Column boolean which refers the another Calculated Column to get the result. The specific message I was receving when viewing the report on PBI Service was "The query referenced column 'Table'[ColumnName] which depends on another column, relationship or measure that is not in a valid state. Additional information: ''"

The issue was specifically with not handling errors on the Calculated Column the boolean referred to. Hence the "not in a valid state" part of the error message.

Here is the DAX that caused the error:

PrevArrearsBal = IF('flend LedgerEntry'[LoanId] = LOOKUPVALUE('flend LedgerEntry'[LoanId], 'flend LedgerEntry'[Index], 'flend LedgerEntry'[Index] + 1), LOOKUPVALUE('flend LedgerEntry'[Arrears Balance], 'flend LedgerEntry'[Index], 'flend LedgerEntry'[Index] + 1), 'flend LedgerEntry'[Arrears Balance])

And, here is the DAX that solved the issue:

PrevArrearsBal =

VAR NextLoanId = LOOKUPVALUE('flend LedgerEntry'[LoanId], [Index], [Index] + 1)

VAR RowError = ISERROR(NextLoanId)

RETURN

    IF(RowError = FALSE(),

        IF([LoanId] = LOOKUPVALUE('flend LedgerEntry'[LoanId], [Index], [Index] + 1), LOOKUPVALUE('flend LedgerEntry'[Arrears Balance], [Index], [Index] + 1), [Arrears Balance]),

        [Arrears Balance])

I believe my issue was related to the fact the eventually the calculation would look for an Index Row that doesn't exist and return an error. Even though 99% of my values returned were fixed decimal numbers (which show up with no problems in PBI Desktop), it inevitably resulted in an error. I can only assume PBI service does not like errors as much as PBI desktop does.

See a screenshot below where I left the error-ridden calculation in one of the visuals to demonstrate the other visuals loading as expected.

I'm also pretty new to PBI so it's also possible that I completely lucked out and have no idea what I'm talking about.

Good luck out there!

[indhu]

I have the same issue but there is no boolean data in the filter 😞 Not sure what is wrong

any help is much appreciated.

Thanks,

Indhu

[GilbertQ]

Hi there

Is the data imported into the Power BI data model?

Also please ensure that there is no caching in your internet browser.

[nasirali]

Yes. Data is inside data model. If i try to analyze the dataset of report via excel or something, i can see everything. Everything is there. And caching is disabled. 

[GilbertQ]

So does the data disappear after a refresh via the Gateway?

[nasirali]

NO. I dont have gateway. I am getting JSON data from web. Data refresh etc happens smootly. I can see the report once or twice after fresh publish to power bI cloud service. After 3rd time, data just does not show in the report. 

[GilbertQ]

That is weird, so the data has not been refreshed?

[nasirali]

Hi @GilbertQ,

i have similar issue as discussed in this thread. 

https://community.powerbi.com/t5/Report-Server/After-a-scheduled-refresh-the-report-has-no-data/td-p...

[GilbertQ]

Hi there, thank link is for Power BI Report Server.

Once again I am not sure why it is happening,I would suggest logging a support ticket to get it resolved.

[nasirali · Accepted solution]

Hello @GilbertQ,

Seems like i have found the problem. as mentioned in the following thread. 

https://community.powerbi.com/t5/Report-Server/After-a-scheduled-refresh-the-report-has-no-data/td-p...

I have a page level filter on the report, which has boolean value but in the data model of report data type was string. i changed it to boolean. Now report is showing data even after schedule data refresh. So far the problem has not generated again. I think this was the problem. 

Regards,

[nasirali]

Yes this is weird. Data is getting refreshed properly. Report is working properly on Power BI Desktop. Problem come when i deploy to service. After one or two time report is appearing empty.