There is no available gateway - Couldn't load the data for this visual

[asjones]

I havea composet model developed by Power BI Desktop connecting to a data model then adding on 2 Excel files to the data model. So DirectQuerey via SQL Server Analysis Services.  Everything worked fine a week or twp ago.

Then today i published from the desktop and my App did not work anymore. I received the error "There is no avaialbe gateway".

I checked an the gateway is sill active.
I then tried to build a new visual on the web instead of desktop. Those visuals no matter what data set i tired gave the error. "Couldn't load the data for this visual"

There may be a simular issue here
https://community.powerbi.com/t5/Service/Analysis-Services-Authentication-Authentication-Type-Not/m-...

See below for the full error message below

Couldn't load the data for this visual
Couldn't retrieve the data for this visual. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.

Activity ID: d2cad957-b5c5-4e53-92df-b4f8dfe3e951
Request ID: a11493d5-03cb-fdea-ec33-84c48f2fc038
Correlation ID: e87a3523-8be6-64b8-5ff1-f54ad1c90159
Time: Tue Mar 28 2023 19:26:20 GMT-0500 (Central Daylight Time)
Service version: 13.0.20342.55
Client version: 2303.3.12949-train
Cluster URI: https://wabi-us-north-central-h-primary-redirect.analysis.windows.net/

[asjones]

My gateway says it is setup correct untill I addan external data source and publish.

When I dig into the Gateway setting this it says the Gateway can't find my data model OR the extra excel sheet that i added to the model. ... seems odd it says it can't find he data model that is in the Power BI Cloud. The Excel file should be local to the report.

IF I remove the extra Excel sheet from the model and repbublish everything it all works fine.  Something seems messed up with the new composet model stuff when adding an extra local data source. It was my understanding that we would not need to add a file to the Gateway process so that a user could combine data from the data model and a seperate Excel file.

[Djonesr1]

@Djonesr1 

[Anonymous]

Hi @asjones 

So when you remove excel from the model, everything is back to normal. It seems that the problem is in the gateway configuration of excel. Did you re-add the excel gateway inside Manage gateways?

Best Regards,
Community Support Team _ Ailsa Tao

[asjones]

so it seems that i can't add and Excel file to composit model on my desktop and publish it with out adding the excel file to a path my gateway can get to.

This makes composet models less usefule. The Excel file I had only has static numbers and did not need to be refreshed. This is something more cumbersome for end users.

[kpathri]

I am getting a similar error too. Looks like @asjones is spot on. For now, I've managed to save the Excel File in a Sharepoint folder and imported the file into PBI from there. That worked for me.

[Datazen]

A good method to use when you have 2 different sources like this is to maintain separate PBIX files. 

PBIX File 1: Holds the Data from DirectQuery via SQL Server Analysis Services. You use Power Query to get it into the structures you want, but it need not have any reports. You then publish that Dataset to Power BI Service where it resides as a Dataset1

PBIX File 2: Holds other data such as from Excel, Google Sheets, etc. You use Power Query to get it into the structures you want, but, again,  it need not have any reports. You then publish that Dataset to Power BI Service where it resides as a Dataset2

PBIX File 3: This is your final PBIX file, holding your reports. For its sources you connect to the published Dataset1 and Dataset2 . Then you do your joins on the data and write your reports in this file. 

One additional advantage of this method is that the refresh scehdule of Dataset1 and Dataset2 in Power BI Service can be scheduled separately. So if Dataset2 pulls from a Google Sheet and you want to refresh it hourly, you can set that up on its own- without regard to the SQL Server Analysis Services data in Dataset1.