Calculated columns disappearing

[Anonymous]

Hi all,

I build a report using PBI Desktop. The data source is a SharePoint Online list.

The report has some visualisations based on DAX calculations.

When I save and publish, everything is fine. The report and dashboard are looking good and working.

However, the moment I add data in the SharePoint list, my visualisations turn blank.

What could be the reason and how could I fix that?

Thanks!

[Anonymous · Accepted solution]

Ok so after some troubleshooting, a colleague figured out what was wrong so I thought I'd post it here as general info.

The visualizations were working correctly in the pbix file on PBI Desktop but not online.

The visualizations that were having issues were Quick Measures. The core formula is the same for all of them. The only difference is the column the evaluate in SharePoint. Here's an example of a formula:

Help Requests to Implement an Initiative = CALCULATE(COUNTROWS('Sustainability App Analytics'),FILTER('Sustainability App Analytics','Sustainability App Analytics'[InitiativeImplementationViewed] = "TRUE"))

The issue here is that the column "InitiativeImplementationViewed" and the others are text columns and the "TRUE" being evaluated has double quotes, indicating that it's a text value instead of being a boolean.

The colomns were initially boolean in the SharePoint list but the transformation probably did not happen properly when imported in PBI.

It's even weirded that it's working fine in the Desktop but not Online.

The solution here was to go back to the dataset and switch all the relevant columns from Text to True/False.

Also modified all the relevant formulas in the Quick Measures so they could evaluate the proper data type.

The new formula looks like this:

Help Requests to Implement an Initiative = CALCULATE(COUNTROWS('Sustainability App Analytics'),FILTER('Sustainability App Analytics','Sustainability App Analytics'[InitiativeImplementationViewed] = TRUE))

[v-yetao1-msft]

Hi @Anonymous 

According to your description, refreshing in the service will not report an error, but the visual related to the calculated column will not be displayed. If it is refreshed in the desktop and then uploaded to the service, there will be no problem, right ?

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Everything seems to work in the pbix file when I do a refresh from there.

It does not work in the PBI Service, even if I publish.

For some reason, it worked only once in the PBI Service. I am not able to make it show the visuals anymore.

[GilbertQ]

Hi @Anonymous 

I would go through your PBIX file and refresh the dataset. It would appear that something is changing from your SharePoint list.

[GilbertQ]

Hi @Anonymous 

Is your dataset refreshing successfully?

[Anonymous]

Nothing happens when I do it in the PBI Service.

Had to download everything into a pbix file and refresh the source from there (on the right where we see all the columns and custom measures), then saved and published.

The visualization worked again until I added more data in the SharePoint list.

I tried to repeat those steps again but now it's just not working at all.

One thing I noticed is that only my custom measures (with the calculator icon) are not working well. The only visualization that's using the default columns seem to be working correctly all the time.