Solved: The query referenced column ****** which depends o...

Otavio_bhz · ‎04-29-2017

Hi all,

I have updated done my query in power bi destop and worked well.

But when I upload that, works well to, but when I update from the web, it appear the foollowing message:

The query referenced column ******** which depends on another column, relationship or measure that is not in a valid state.

Does someone know why that is happening ?

- Power bi destop ok and update (ok)

- Power bi web after upload (ok)

- Power bi after update (Appear that problem)

Thank you very much,

KR

Otavio

Otavio_bhz · ‎05-08-2017

Hi All,

I could solve the issue with the Power Bi update via WEB update.

The problem was that on desktop, if you use int values on imported files those “variables” works fine.

But when you upload that and update, the web system don’t understand as int, and the problem come when you use related() and calculation.

Then I have changed all the columns from integer to number % and everything is fine now on the Web.

Thank you very much for your help,

Kr,

Otávio

View solution in original post

David_Monivas · ‎04-03-2024

In my case, the web update was a temporary fix. After a few hours, the problem reappeared, and it was really frustrating.

I tried almost everything: checked column types, references, formulas... and then I started to backtrack through my panel until I found the "change" that I think triggered everything.

My panel had a table with one column serving as an index to centralize all relations between tables in a star. This table was constructed in DAX by joining all the IDs from all the tables and ensuring that we had all possible IDs. But to prevent errors, I introduced a FILTER in that formula to remove empty values. It was working fine (although terribly slow and inefficient). To improve efficiency, I removed the Filter, relying on DISTINCT.

Since then, I've been experiencing troubles, and here's my theory: the index isn't being calculated correctly online in DAX, which is causing the problem. Any duplications, empty values, etc., and the panel crashes. How come that it happens only online? My guess again. In desktop is not case sensitive. Online IT IS. That's leaving duplicated values in the Index column which leads to the error.

My solution: I moved the creation of the index table to Power Query (Power M) before actually using the panel. It's not as efficient (takes more time), is case-sensitive, and more complex, but the benefits are the same, and now the panel is stable.

I apologize if any of what I'm saying sounds like nonsense to experts; I'm a total newbie in Power BI. I just wanted to share my experience in case it gives any ideas to anybody with better insight into the actual workings of Power BI.

Thanks!

Jay95 · ‎11-04-2023

I have the same problem. Could you please help?

RHOU · ‎09-28-2022

I have the same issue. My model includes imported tables as well as a live connection to an additional PowerBI dataset with relationships between the two. When I use the calculated field directly in a visual, the error changes slightly: "The query referenced calculated column XXX which does not hold any data because evaluation of one of the rows caused an error."

The issue started, when I added a calculated column in my imported tables that pulls data from the live connection dataset.

Therefore, I'm guessing that the refresh on Desktop can properly handle this, but on the Service the calculated column is not properly calculated.

For the other ones that have this issue, do you also have calculated columns that access some tables that are connected via live / direct query model?

Anonymous · ‎02-07-2022

Is there any news about latest issues?

Couple of visuals in Power BI Service also show errors:

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

The query referenced column 'Table'[Column] which depends on another column, relationship or measure that is not in a valid state. Additional information: ''.

In Power BI Desktop no errors occoured.

miftah_02 · ‎08-22-2022

Same issue

Hi I also get an issue like this. Has this issue been resolved?
Thankss..

Anonymous · ‎12-29-2021

Hi all, I am facing this error "The query referenced column 'data'[sample] which depends on another column, relationship or measure that is not in a valid state. Additional information: ''.

The vissuals are showing in Power BI Desktop but not displaying in the Power BI service. Appreciate your quick response on this. Thank you

PSG83 · ‎10-12-2021

@IncursioML1 - do you solved your Issue? Facing the same right now for my Update on the Service. I only have a Dataset Live Connection and an Databricks Connector setup in this Dataset.

IncursioML1 · ‎08-30-2021

This seems to be happening to me too and I just updated to the latest version on the desktop to see if that was the problem.

Creating the report in PowerBI Desktop, then publishing to Cloud is fine. Once you do any type of refresh, whether manually or through the scheduled refresh - the visuals just bomb out.

The only way that seems to be a workaround is refreshing the report in the desktop then publishing to the cloud.

Not sure if this is a bug or if it's relating to all those date/time reference problem or something else I'm missing.

I have 2 datasets, one from SharePoint Online (from a list) and one from SQL Server On Prem. The SharePoint Online is imported and then a relationship made to get some end reporting. When I had split these two datasets to two different pbix files and refreshed them through the cloud scheduling - it works fine.

Even tried converting the direct queries to imports and that still didn't work. The part that is frustrating is that the message isn't very helpful and on top of that the refresh works - it doesn't throw an error, but the visuals aren't loading. (really?!)

Anonymous · ‎05-01-2017

@Otavio_bhz

Do you use on prem sources or online sources for your model?

And what kind of sources? (Excel, SQL etc.)

Otavio_bhz · ‎05-02-2017

@Anonymous,

I am using both.

Excel and SQL, but the problem is only with this report.

The other reports have mixed database source (SQL and EXCEL) and are working correctly when I update Desktop and when I update web.

The only problem is with one report and with a database that use a related function. That mix Excel and SQL functions.

And that happend just in WEB update, on the update from power bi desktop works fine (Both gateway and SQL).

Please let me know your thoughts.

Thank you very much,

KR,

Otávio.

v-sihou-msft · ‎05-03-2017

@Otavio_bhz

What kind of update you have made on Power BI Service?

If the error message mentioned a LocaDateTime column, it might be the date type or "out of bound" issue. Please refer to similar threads below:

https://community.powerbi.com/t5/Desktop/Date-Hierarchy-couldn-t-load-the-data-for-this-visual/td-p/...

http://community.powerbi.com/t5/Desktop/On-calculated-field-using-the-date-hierarchy-causes-an-error...

Regards,

Otavio_bhz · ‎05-06-2017

Hi,

It is not related to date hierachy.

I don't know what could be happening.

Someone have some guess ?

Thank you very much,

KR,

Otavio

Otavio_bhz · ‎05-08-2017

Hi All,

I could solve the issue with the Power Bi update via WEB update.

The problem was that on desktop, if you use int values on imported files those “variables” works fine.

But when you upload that and update, the web system don’t understand as int, and the problem come when you use related() and calculation.

Then I have changed all the columns from integer to number % and everything is fine now on the Web.

Thank you very much for your help,

Kr,

Otávio

learner03 · ‎01-28-2024

@Otavio_bhz If we change related function to lookup, will that solve the issue?

learner03 · ‎01-28-2024

@Otavio_bhz How did you replace? I have the same issue.

So if the column is Decimal number with 2 places after decimal, how to just convert that column as percent and get same result?

vamri · ‎09-26-2022

@Otavio_bhz i am using M query and incremental refresh in my report and I am facing a similar issue. Are you proposing to that I change all my columns with data type "decimal number" to "percentage" ?

BertaEmilSchule · ‎09-24-2022

Waht the Hack. It realy worked to replace all integer Date-Types in my Calendar Table. Thank you very very much.

What a horrible expierience, thank you MS.

pavel_rudenko · ‎04-08-2020

Same situation to me:

- power bi desktop tables/visual browsing - OK

- power bi desktop manual refresh - OK

- power bi desktop deploy to power bi service - OK

- power bi service report brows - OK

- power bi service publish to sharepoint online - OK

- power bi service set refresh schedule - OK

- once refreshed - CRASHED in power bi service and sharepoint

config is pretty much simple:

- two tables from sharepoint online list (users title and some dates)

- one table Calendat - to have all dates for some period

- one intermediate table to produce working days per month and calculate working days spent by concultants

- final reporting table (consultant name, working days in month, actual worked days in month, month workload)

visual throws an error:

Couldn't load the data for this visual

The query referenced calculated table 'ReportingData' which depends on another column, table, relationship or measure that is not in a valid state.

Please try again later or contact support. If you contact support, please provide these details.

Activity ID8921f0a4-d8da-4c07-9929-487d2419cae7
Request IDecaead5a-818f-eb79-ab10-967dfb52e6eb
Correlation ID2da96bb2-b71b-16f7-5121-a5a5b9ca3998
TimeThu Apr 09 2020 02:44:16 GMT+0300 (Eastern European Summer Time)
Service version13.0.12739.219
Client version2003.3.022
Cluster URIhttps://WABI-US-CENTRAL-A-PRIMARY-redirect.analysis.windows.net

tried to get all fields - decimal and type number - no help.

Please, sugest some possible solution - need to publish report to SPO and been able to refresh it ASAP.

Thanks all in advance

Anonymous · ‎09-15-2020

Having same problem here. PBI Desktop working fine; publishing works fine. When updating through web, crashes a few visualizations. Can somebody help? Thanks in advance.

andrezmor · ‎11-09-2023

Having the same problem here. I am working in power BI Report server, I have been troubleshooting the problem by eliminating some relationships and measures but still get the same behaviour. Everything works fine in PBI Desktop but after uploading to server and first update, all the visual crashed.

The query referenced column ****** which depends on another column

Couldn't load the data for this visual

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024