cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
minh23tran
Helper I
Helper I

Dynamic datasource

 

Groups and permissions work great, but the only issue I'm coming across is having dynamic connections. I'm wondering if it's possible with power BI.

Current Setup

 

DataSource

Apples, Oranges

 

Report DessertsSold

 

Each user can only view one of the datasource. Either Apples or Oranges. If they can view apples, when they generate DessertsSold report, it should query only Apples Dataset. Otherwise Oranges. 

 

I know I can create a content pack but that would virtually mean  N being number of clients, R being number of reports, We'll have N*R items to create. Updates will be a gigantic pain

3 REPLIES 3
v-micsh-msft
Microsoft
Microsoft

Hi minh23tran,

 

Could you please share more details about the Data Source, are you using any database as the data source here? Further, what is your data model?

If using database source, then we could create database role to control if a user has permission to access the proper database(or even the table), at the data source side.

For example:

Using the following T-SQL:

exec sp_msforeachtable "DENY SELECT ON '?' TO [username];"

GO

GRANT SELECT ON [schemaName].[tableName] to [username]

Go

Will only give the [username] SELECT permission on [tableName].

 

If configuring data source permission is currently not available, then please take a look at the role level security under Power BI:

Row-level security (RLS) with Power BI (Preview)

This allows admin to create special role(under Power BI Desktop) which could only see the selected rows or columns of the dataset, then manage the wanted user to the special role under Power BI service. This feature is still in Preview, so there might be some limitations.

See if this would meet your requirements.

If any further help needed, please feel free to post back.

Regards

 

Hi Michael, Maybe it'll be more straight forward if I you detail of my current setup instead of pseudo setup. My server currently contains 25 databases. 

 

Each database have the same number of tables and table structure. 

 

 

 

I built the reports using Demo Dataset.

 

I need it to change base on the organization the client is from. That's where it comes to play. Lets say my customer is from Atikokan, when he runs report Business Counselling, it should generate based on Atikokan dataset. I hope this is clear

Hi minh23tran,

Apologize for the late response.

Currently I don't think it is possible for Reports to switch Datasets based on user login information.

We could take use of Role Level Security to control which data could be access by who, but that is based on the same data source, changing data source is only available in Query Editor currently.

If data from different data source is needed for one report, then we could append or combine them into one under Query editor, after that, use the customized Query to build the report, and configure the Role Level Security.

Regards

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors