Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BirhanAYDS
Helper III
Helper III

The query referenced calculated column '_Table'[Column] which does not hold any data because evaluat

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

 

Hello everyone,

 

I have faced with error. My reports datasources are SSAS, Excel and two tables which are created in Power Query.

I add the calculations using the tables sourced from SSAS as a calculated column to the table I created in the power query. There is no problem in Pbix, it is also not a problem when I publish to the service and do a manual refresh. I started getting this error when scheduled refresh was running. By the way calculated column doesnt contains any error... 

 

Could you help me?

8 REPLIES 8
C4YNelis
Advocate II
Advocate II

So, just to follow up on my last comment:

 

I changed the model by duplicating the DQ table that was being referenced by the calculated column in my mixed-mode model. (the calculated column residing in a table from the on-premisses workbook.

 

Duplicating the model and changing some dependencies, the refresh now failed (before it refreshed just fine, but the report consequently "crashed"), stating the following error, confirming my suspicions:

 

COM error: Microsoft.PowerBI.AS.ConnectionDetailsProvider, Single sign-on (SSO) credential is not provided which is required to execute the current request. Note that performing a refresh over a dataset with a calculated table or a calculated column which references a Direct Query data source is not supported within the Power BI service..

 

So basically, it simply is not possible to reference a DQ data source in a calculated column.

 

Cheers,

Niels

Anonymous
Not applicable

Great answer to this issue.  Now I have a follow up question to this.
My PBI data model contains excel workbooks and powerbi datasets that are read only .
I am trying to bring fields in from the PBI dataset, and lookupvalues function doesn't see any of the fields in the dataset tables.

The problem is that i can't recreate the table locally because it's locked out to me as read only dataset. 
and when i use something like TREATAS, it does work functionally but when i pubish the report, I get this error.

Correct. You cannot move data like that across islands (DQ vs Vertipaq). You can move filters, not data.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
C4YNelis
Advocate II
Advocate II

Hi @edhans and @BirhanAYDS ,

 

I know this is an older thread, but I think I know what the problem is / was (did you ever solve this already?) that BirhanAYDS is describing. I believe I am experiencing a similar issue.

 

What I'm working with is a mixed model, where I load two datasets via Direct Query (one consisting of calls and one consisting of sales data, without either references the another). Furthermore I have combined this with an on-premises Excel workbook (with potential leads) that is updated daily by the business. This Excel workbook provides three tables (Leads, Prospects and Customers, all from the same source).

 

The general idea is that the Leads table from the Excel workbook provides a list of leads, which serves as a basis that sales need to work off bit by bit every day. Once they have done that, they mark the lead in the workbook with a corresponding reference to a prospect or customer in our ERP system and the rest of the data (if any) will be visible in Power BI, provided from either of the datasets I mentioned before.

 

Now the trick is, that in order to clean the list with potential leads, I have created a calculated column in the table Leads (originating from the on-premises workbook). The calculation basically uses the calls provided from the DQ dataset, to count the rows with calls. This calculated column with the number of calls per lead is added to the table with leads. Then, any lead with more than 0 rows is then filtered from the visual.

 

I'll admit, the model is not exactly an optimal one, but the amount of data (couple of thousands leads) wouldn't normally warrant to spend much more time to build a better model, so I decided to take the quick and dirty way.

 

Now the issue at hand is that, while maybe not a perfect way of setting this up, it works just fine in PBI Desktop. However, when publishing this to the service, it refuses to show this visual (table) with leads, complaining that the data for the visual element could not be loaded with the following errormessage:

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

 

Now, this seemed like a perfect opportunity to play around with the brand new EvaluateAndLog function and DaxDebugging, however, they just show me a perfect result with no errors (like expected).

 

So my questions are:

- what exactly is happening here?

- why does this work in PBI Desktop, but not in the PBI Service?

- is there a known solution / workaround?

 

Last but not least, when trying to view a preview of the contents of the dataset online, it is complaining it cannot access the datasource (the workbook), when I select the Leads table. The other two tables (also originating from the on-premises workbook) do show up just fine? The direct query datasets are also viewing just fine. To prevent this from being some strange issue with the data itself, I've stripped the data down to one single row, which is 100% guaranteed ok and still it does not work in the service.

 

Cheers,

Niels

As mentioned in the limitations section in our documentation on this feature, this is currently not supported in the Service, so I'd caution against doing this in Desktop unless you are not going to publish your model to the Service.

edhans
Super User
Super User

You need to provide data and info, and confirm the terms you are using. Power Query uses "custom column" and DAX uses "Calculated Columns".

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I need to add a column from direct query to my table in import mode. I made this addition but I am getting error about that column on pbix refresh in Service. The error above.

I am still not sure what you are asking.

"I need to add a column from direct query to my table in import mode."

You are either using direct query, or you are using import mode, or you are usinga  composite model where one table is DQ and another is Import, or you are using the new hybrid feature where you can mix modes in a table.

 

I'll repeat my request for data and screenshots of objectives. And be super super clear on the terminoligy used. Calculated columns are not in Power Query, Custom Columns are not in DAX, and unless using hybrid tables, you cannot have direct query and import on the same table. 

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors