Integrated DirectQuery data with Imported data cannot be published to workspace

[BosJelle]

Hi! 

I have a 'master' report with master data and 2 other reports from which I DirectQuery data into the master report. I want to combine the master data, with the data from the other two reports in a way that:

• When the data from the two reports is available it does not show these rows if they are also in the 'master' data
• Else, show the 'master' data (rows)

The result is a combined dataset from the 2 DirectQuery's and the master data. I have built a query that does this. However, when I try to publish I get the following message:

"This dataset contains calculated tables or calculated columns that refer to remote tables, which will result in refresh failures after publication. Are you sure you want to continue publishing?"

How do i mitigate this and make sure that I can combine the data while removing any master data if rows are present in the other two (DirectQuery) reports?

Here an image to clarify:

Thanks in advance 🙂.

[v-nuoc-msft]

Hi @BosJelle 

@lbendlin Thanks for your sharing!

I added some of my methods.

The warning message you're seeing is Power BI's way of alerting you to potential refresh issues post-publication due to the nature of calculated columns or tables referencing DirectQuery sources.

This is because, upon refresh, Power BI attempts to update these calculated elements based on the DirectQuery data, which can lead to failures if not configured correctly.

The following methods may solve your problem:

Check Calculated Columns/Tables: Make sure that any calculated columns or tables that reference DirectQuery data are absolutely necessary for your report. If you can, simplify or remove them without affecting your report.

Make sure you're leveraging composite models effectively. This involves setting up relationships correctly and understanding how data refreshes work within these models.

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

If the calculated columns or tables are essential, consider managing the dataset refresh settings to minimize the impact of refresh failures. This might involve adjusting the refresh frequency or using incremental refreshes where possible.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BosJelle]

Thank you for your answer!

Using relationships, how would I make sure that the visualisations on my page will show the master data, except when similar data is found (e.g. based on 3 similar columns) in one of the two other tables? Do I need to make a measure? And if so, how?

Please, let me know!

[lbendlin]

Use COALESCE.

[BosJelle]

Hi!

Thank you for your answer but unfortunately this does not answer my question. Here some more context:

I want to show the master data in my visualisations, except when there is data from that particular project in the directquery. This can be based on project name. The method does not really matter (e.g. relationships, query). However, I need to be able to publish it to the workspace. 

[v-nuoc-msft]

Hi   @BosJelle 

To display master data in a visualization, you need to create relationships between tables based on common or related columns.Power BI can detect and create relationships automatically, or you can create them manually.

You can view the link below for more details:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[BosJelle]

Hi!

Thank you for your answer but unfortunately this does not answer my question. Here some more context:

I want to show the master data in my visualisations, except when there is data from that particular project in the directquery. This can be based on project name. The method does not really matter (e.g. relationships, query). However, I need to be able to publish it to the workspace. 

[lbendlin]

I think you can (ab)use the Aggregation feature for this.  Treat the Master table as the aggregation table, and the Direct Query as the "Details"  table.  That would then result in the data being fetched first from the ... never mind.

How about incremental refresh with the hot partition pointing to the Direct Query?

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

Here are some optimization hints for these:  https://blog.crossjoin.co.uk/2024/02/25/datacoveragedefinition-a-new-optimisation-for-hybrid-tables-...