Forum Discussion
Gateway unavailable - DirectQuery to AS source not working on report
Hi, I have been trying to create a report using multiple data sources, some are 'DirectQuery to AS' and the rest are either Excel (web) or one which is 'Dataflows'. On the desktop everything is fine and works but when I go to publish it, I get this message:
Upon publishing, the excel data visuals all work fine but ones which come from the ‘Direct Query to AS’ which I pulled through as a semantic model don’t work apart from one which shows one visual but not the other one?? I get the message ‘There is no available gateway’. I installed the gateway app and have set one up, but I can’t seem to make it actually do anything useful to fix my problem…
Under the settings for my semantic model it says ‘One or more cloud data sources for this semantic model have been deleted’. Underneath this is the option to ‘Recreate cloud data sources’ but this doesn’t work, doesn’t do anything. If I go into ‘Gateway and cloud connections’ it lists my ‘Personal Gateway’ and then the new one I created underneath (personal mode) but for this one it says ‘Not configured correctly’. Also in the ‘Cloud connections’ beneath this is shows all of them connected to my ‘Personal Cloud Connect’ except the main one that doesn’t work on the main report. There is a dropdown box that says ‘Maps to:’ which is empty although I’ve tried to set up a connection using my new gateway, it doesn’t work. I’m running out of ideas here and just hoping someone out there can help me?
One last note is that the dataset which is the main one not working does work fine on another report I made whereby I only imported that dataset and one web based excel table so I don't know if it's a confilct with the other data sources. maybe the Dataflow?
If anyone has any advice it would be greatly appreciated! Thank you
Hi samc_26 -Dataflows and Datamarts rely on cloud-based connections and have specific requirements for compatibility. The message you're seeing indicates that the DirectQuery connections you're using for Analysis Services (AS) aren't supported within the current cloud environment you're using for Dataflows or Datamarts.
You need to ensure that you're using the on-premises gateway correctly for Analysis Services (AS) connections. This may require switching from personal mode to standard mode in your gateway setup.
- To create the correct connections:
- Go to Manage Gateways in Power BI Service.
- Ensure the gateway is properly configured to support DirectQuery to AS.
- You may need to delete and re-add the data sources under Enterprise Mode Gateway instead of using personal cloud connections.
- Switch from Personal Mode to Standard Mode Gateway for AS.
- Recreate dataflows, if necessary, within the Dataflows editor.
- If you’re facing issues with the Analysis Services connection, check its credentials and ensure the gateway is set to handle DirectQuery.
Hope this inputs helps to sort the issue.
- To create the correct connections:
4 Replies
Hi samc_26 - the gateway is installed and configured correctly. You need the On-premises Data Gateway (Standard Mode), not the Personal Mode, if you're sharing reports or collaborating.Under Manage Gateways in Power BI Service, check that the gateway is configured to access your on-premises Analysis Services data source.Add the necessary credentials for each data source (Analysis Services, SQL Server) to the gateway in the "Data Sources" section.Go to Manage Gateways in Power BI Service and ensure your gateway matches the data source (database) you're using. You may need to reconfigure the gateway to include Analysis Services, SQL Server, and other sources you're pulling from.Ensure the cloud credentials match those used on your Power BI Desktop.
- Under Datasets → Settings → Gateway Connection, check that every data source (Excel, Dataflows, Analysis Services) is correctly mapped to a valid gateway.
- For each data source, ensure that the credentials are correctly entered and matched.
On-premises data gateway - Power BI | Microsoft Learn
DirectQuery in Power BI - Power BI | Microsoft Learn
Troubleshoot the on-premises data gateway | Microsoft Learn
Hi rajendraongole1 thank you for your reply. I have installed the standard mode but I'm still struggling to connect to my 'Analysis Services' sources 😅 On the dropdown for 'Maps to:' it has the option for me to create a new connection but then at the top it says 'Currently, these cloud connections are not supported by Dataflows, Dataflows Gen2, and Datamarts. To create personal cloud connections for these experiences, please use the Dataflows or Datamarts editor in "Get Data".
When I try to set something up through here it appears to work but when I select it under the 'Gateway connections' it lets me select it but won't let me apply it and other boxes go blank too. It keeps asking for me to create connections for data source types that I've already created connections for...The more I do the worse it seems to look 😫 Hoping you can advise me further!
Hi samc_26 -Dataflows and Datamarts rely on cloud-based connections and have specific requirements for compatibility. The message you're seeing indicates that the DirectQuery connections you're using for Analysis Services (AS) aren't supported within the current cloud environment you're using for Dataflows or Datamarts.
You need to ensure that you're using the on-premises gateway correctly for Analysis Services (AS) connections. This may require switching from personal mode to standard mode in your gateway setup.
- To create the correct connections:
- Go to Manage Gateways in Power BI Service.
- Ensure the gateway is properly configured to support DirectQuery to AS.
- You may need to delete and re-add the data sources under Enterprise Mode Gateway instead of using personal cloud connections.
- Switch from Personal Mode to Standard Mode Gateway for AS.
- Recreate dataflows, if necessary, within the Dataflows editor.
- If you’re facing issues with the Analysis Services connection, check its credentials and ensure the gateway is set to handle DirectQuery.
Hope this inputs helps to sort the issue.
- To create the correct connections:
