Gateway not recognized when query Appends SQL query to CRM Online query

[rl_evans]

I created a report with Power BI desktop that contains a query to an on-prem SQL Server, a query to Dynamics CRM Online (2016), and a third query that appends the two previous queries together.  When I publish the report to an Workspace and go to Schedule Refresh, I am not able to select a gateway for refreshing.  I get the error "You don't have any gateway installed or configured for the data sources in this dataset...".  Also, the Data Source Credentials menu item is grayed out.

If I remove the third query and publish the dataset, I am able to select a gateway and under Data Source Credentials I see that the credentials are valid for both the SQL Server connection and the CRM connection; plus I'm able to refresh the dataset. 

It appears to me that this is a bug in the PowerBI.com service as the third query works fine in the desktop.  I'm able to manually refresh the dataset on the desktop but unable to connect to a gateway and refresh the dataset in PowerBI.com.

[v-yuezhe-msft]

@rl_evans,

Do you use personal mode gateway or on-premises gateway? I test the scenario as yours, I can schedule refresh for the dataset when including append query using personal mode.

Hoever, it is not possible to use on-premises gateway for combined data sources(online and on-premises) when the dataset includes append or merge query, this is a know issue, please review this idea.

There is a similar thread for your reference,
https://community.powerbi.com/t5/Integrations-with-Files-and/PowerBI-Enterprise-gateway-You-don-t-ha...

Regards,
Lydia

[rl_evans]

I'm using the On-Premise gateway. 

I do have a less than optimal workaround which is to separate the on-prem and online data into two different fact tables.  Then, create common dimensions between the two.  I then create a similar measure for each of the two tables (e.g., SUM(Revenue)) and finally create one measure that sums the two measures together.  This gives me a mashup of revenue across the two sources without using a query to do the mashup.

[v-yuezhe-msft]

@rl_evans,

Glad to hear that you have found the optimal workaround.

Regards,

Lyda