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

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