Couldn't load the data for this visual (calculated column holds no data)

[RobertoM]

Hello community,

I'm getting this error, which seems to be related to a formula I added into a New Column:

Couldn't load the data for this visual

The query referenced calculated column 'KPI'[OnTarget] which does not hold any data because evaluation of one of the rows caused an error.

The formula I'm using is this, which is quite simple. I just want to check if the data was submitted before day 5 of each month (format DD/MM/YYYY):

OnTarget = If(VALUE(left([Created],2))<06,"Yes","No")

The weird behavior is that the visualization appers on PowerBI Desktop, but when I publish it, it doesn't.

Published:

PowerBI Desktop:

I tried to create a new table (reference) and use its fields instead, tried to change the credentials, close and open PowerBI, but none of these worked.

The dataset is connected to a Sharepoint site in the cloud, so I don't use gateways.

Any ideas?

Thanks in advance.

[v-kkf-msft]

Hi @RobertoM ,

What is the formula for your calculated column 'KPI'[OnTarget]?

Is it in the correct format and results?

Could you please share your PBIX file without sensitive data?

Best Regards,
Winniz

[lbendlin · Accepted solution]

Does it have to be a calculated column or can you do this in Power Query?  There you can use the Column quality checks to see where the missing values come from and how to avoid them.

[RobertoM]

Hey, you just gave me a good idea that helped me solve the issue... what I did was to create a new column on Power Query extractig only the date from the Created column... with that I was able to remove part of the formula in the Calculated Column, which seems to be causing the issue, to:

OnTarget = If(VALUE([Created_Day])<6,"Yes","No")

The issue was happening after a Scheduled refresh event, but now it's working just fine.
Thanks for your reply and for the idea of using the Power Query to solve the issue.