Utilizing Power BI and Advanced Filtering

[nbeukema]

My use case is attempting to use Power BI on an existing dataset on a school assessment platform.

We'd like to set up a small app that uses the same database of this existing assessment platform, and to use the .NET Power BI API package to allow users to see their specific data reports.

I would attach Power BI to our existing MYSQL database, and I want to allow our backend to access all data and any report it needs, but to conditionally filter the reports and the data in the reports to the current user based on certain criteria that we already have business rules for in the existing app.

Eg.

Sam logs in and requests to view the report for Assessment Ids of 1, 4, and 6. I have a set of rules in my app already that says Sam can or cannot view these. In this case Sam can view the report, so I hit the Power BI API with my assessment report id, my master account credentials, and I want to only view the assessment data for assessments 1, 4, and 6.

Essentially I want my app to filter what data the user can and cannot see, and I want Power BI to generate the report. This does need to be secure, and cannot allow for users to access certain assessment data that our app says they cannot access.

Am I able to utilize Power BI for this? I would love to be able to build reports with ease inside of this service.

[v-jiascu-msft]

Hi @nbeukema,

Could you please mark the proper answer as solution?

Best Regards!
Dale

[GilbertQ]

Hi @nbeukema

It would appear that what you need to use is Dynamic Row Level Security that is part of Power BI.

What will happen is once the Dynamic Row Level Security is setup, the user will log into the App, Power BI will know who the user is and dynamically ONLY show them the data they are allowed to access.

Here are to links which will firstly give you more details around Row Level Security. And the second is a step by step blog post on how to implement Dynamic Row Level Security.

Row Level Security Explained

Dynamic Row Level Security Made Simple

[nbeukema]

I see. This is interesting, but I see the max allowed users is 1,000. We do have around 40,000 users, mostly students so I suppose they wouldn't all need access to reports, but regardless, there are quite a few users.

More importantly though, the business rules are very complex, and we wouldn't be able to distill them down to these RLS items. We have an entire service that handles the rules of whether a user can access a particular report for a set of data, which are all dynamic and changing as well based on who is in which districts/courses/allowed access.

This is why I'm trying to take the approach of having our app provide the open/closing of the gate. Thank you for the suggestion though, I'm going to attempt to spike this idea out, but are you aware of any other features we could leverage to try what I'm describing?

[GilbertQ]

Hi there, could I ask where you saw the limit is 1000 users?

I have implemented Dynamic RLS with about 10 000 users where there were only 3 Roles, and all the users were in one of those 3 buckets, based on the requirements. Where also quite complex due to the way the business was structured.

I am almost certain that it can be done, the challenge will be to ensure that the way it is setup is done in an efficient manner.