How do I connect a Power BI report to an existing Power BI dataset that uses Excel as a datasource?

[PAPutzback2]

I have an Excel file with a list of Employee email addresses and some other attributes. I want to use that data in other reports for setting up row level security. When I open up an existing report and go to "get data" and choose Power BI datasets, I don't see my dataset. However, if I open a new report and go to select a Power BI dataset, I can see my Employee List dataset? 

The preview feature to use Power BI datasets is on in the report. It must be because it shows me the Power BI Dataset connector.

Thoughts?

Thanks,

Phil

[v-jingzhang · Accepted solution]

Hi @PAPutzback2 

In your scenario, it has hit some limitations of the preview feature, so you couldn't see the dataset when you tried to add it to an existing report. When you connect to a dataset in an existing report, it will be in DirectQuery mode. Please read the following limitations highlighted in red. 

There are also a few limitations you need to keep in mind:

• Parameters for database and server names are currently disabled.

• Defining RLS on tables from a remote source is not supported.

• Using any of the following sources as a DirectQuery source is not supported:

  • SQL Server Analysis Services (SSAS)
  • SAP HANA
  • SAP Business Warehouse
  • Real-time datasets
  • Sample Datasets
  • Excel Online Refresh
  • Import Excel / CSV files
  • Content Packs (deprecated)

However, when you open a new report and go to select a Power BI dataset, by default it will connect to the dataset in Live Connection mode as it is the first data source in the new report, so it will display all Power BI datasets that you have permissions of. That's why you can see the dataset in a new report. 

For more limitations about the preview feature, please refer to below official document:

Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

Additionally, according to the limitation highlighted above in blue, using a table from a dataset to set up row level security in other reports is not supported either. I would suggest creating a Power BI dataflow to connect to the Excel file. Then connect to this dataflow in other reports. You can create a dataflow using define new tables.

Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs

Creating a dataflow - Power BI | Microsoft Docs

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[PAPutzback2]

The problem is my dataset does not appear in the list of available datasets to choose from. 

[jennratten]

Hello - this article should help you.  It discusses composite data models with live connections to Power BI datasets.  

https://sqlserverbi.blog/2021/01/10/a-first-look-at-gen-2-composite-models-with-live-power-bi-datase...