Calculated table refers to remote table via Direct Query

[smalltownbear]

I recently created a new report based on a remote shared semantic model. The new report connects to the shared semantic model via Direct Query. In this report I created a calculated table that refers to one of the remote tables in the shared semantic model. When I went to publish the report to the PBI service, I got the following message:

When I opened the report in PBI service I noticed that it threw an error and failed to load any of the data. After doing a little research it's still not entirely clear to me what I need to do to get this working, or whether it is even possible. Any tips or guidance would be much appreciated. Thanks!

[rajendraongole1 · Accepted solution]

Hi @smalltownbear - In Direct Query mode, calculated tables are not supported. This is because calculated tables are created in memory and require a full data model to work, which is not the case in Direct Query mode. Instead, Direct Query only sends queries to the underlying data source when visuals require data.

alternatively, you can use DAX Measures: If you're trying to perform calculations that result in new data, consider using measures instead of calculated tables. Measures calculate values dynamically based on the context of the report visuals without storing the data in a separate table or in Query Editor: If you need to filter or transform data from the remote table, consider doing this in the Query Editor before loading it into the report. This may involve creating a new table using Power Query rather than DAX.

If you require a specific transformation of the data, you might consider creating a view in the underlying data source that performs the necessary calculations or transformations. You can then reference this view in your Power BI report.

Make sure you have the necessary permissions to access the shared semantic model and any underlying data sources.

Check Data Source Settings: In the Power BI Service, ensure your data source settings and credentials are set up correctly for the shared semantic model.

[smalltownbear]

Thanks for the reply @rajendraongole1. The explanation for why calculated tables are not supported makes sense, although it is a bit unfortunate from a user experience perspective that it works perfectly well in desktop and only stops working when you publish to the service. I will look into using one of the workarounds you suggest above. Thanks again.