Connection few reports (SQL, Excel, Semantic model)

[The_Crow]

Hello,
I would like to connect 2 reports together.

Data source are:
for "A" report:
Excel on TEAMS

Snowflake database
PBI semantic model

for "B" report:
SQL database

Excel on TEAMS
"A" report.

This is shown in the graphic below.

Two reports works separately, but when I connect report "A" and "B" then On premises data gateway is turned off and I cannot use it.
Is there any way to combine these reports?
I made a few tests and my conclusion is:
1. The connection configuration does not work: SQL+Semantic model from another report + Excel on TEAMS
2.. However, the connection does work: Semantic model from another report + Excel on TEAMS

[rohit1991]

Hi @The_Crow .

• Issue: Power BI does not support On-Premises Gateway (SQL) + DirectQuery to a Semantic Model. This disables the Gateway.
• Works: Connecting Semantic Model + Excel on Teams works because both support DirectQuery.
• Fixes:
  • Use Import Mode instead of DirectQuery for the Semantic Model.
  • Pre-aggregate SQL data into a dataset and connect it separately.
  • Use a separate dataset for SQL and merge it later in Power BI Service.

[nilendraFabric]

Hello @The_Crow 

could you please make sure you are on the latest version of gateway 

and check this too

Power BI’s On-Premises Data Gateway can be configured to support merging or appending on-premises and cloud data sources in a single query. However, this requires enabling the setting “Allow user’s cloud data sources to refresh through this gateway cluster” in the gateway configuration. Without this setting, the gateway will not execute queries that combine both types of data sources

https://learn.microsoft.com/en/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

https://community.fabric.microsoft.com/t5/Service/Issues-with-Power-BI-Gateway-Refresh-of-SQL-and-Sh...

[The_Crow]

Hello @nilendraFabric 
I'm using On Premises data gateway Personal Mode 3000.254.4.
I could not find option: “Allow user’s cloud data sources to refresh through this gateway cluster”.

As I mentioned, I can download the data using SQL and e.g. excel, but it stops when connect another PBI report.
This is not in description in those 2 links you attached.

[nilendraFabric · Accepted solution]

Hello @The_Crow 

hello @The_Crow 

Thanks for getting back with details

the problem lies with with personal gateway only.

The Personal Mode Gateway does not support combining data from multiple sources when one of those sources is another Power BI report (semantic model). This limitation arises because Personal Mode lacks the ability to manage complex mashups involving both cloud and on-premises data sources.
• When you try to connect a semantic model from another report along with SQL Server and Excel on Teams, the gateway cannot handle this combination due to its restricted functionality

Mixing cloud-based data sources (like Power BI semantic models) with on-premises data sources requires advanced capabilities that are typically supported by the Standard On-Premises Data Gateway, not the Personal Mode Gateway

https://radacad.com/the-power-bi-gateway-all-you-need-to-know

please accept this answer and give kudos if this is helpful 

[The_Crow]

Unfortunately this is what I thought, personal mode cannot works in this case. I will ask our IT why we cannot use standard mode. 

Thanks for clarifying.