Power Bi Embedded Dynamic Row Filtering with SQL Server and App Owns Data

programthings — Mon, 19 Apr 2021 13:27:56 GMT

tl;dr all of this already works so why doesn't the api endpoint support passing parameters to the role instead of requiring manual definition unless i'm just not finding the documentation of how row level filtering on a column is supposed to be achieved?

As my subject mentions, I am using the "App Owns Data" to embed reports for my customers. The data is stored in a SQL Server database.
I am using RLS and want to be able to do it dynamically. So that a finished report doesn't need to be updated with new role information as customers are added or removed. I just want to be able to filter a column with one or more values in the embed token itself for obvious security reasons.

I am able to acheieve this manually by creating the role as shown in my attached image. The role name is the same as the value name since they are unique to each of my customers.

So obviously this is achievable, but the 2 dynamic solutions to this i've been able to find require paying extra for Azure Analysis Services or make my customers have to create their own accounts with microsoft which means that microsoft now receives my individual customer data which seems to fly in the face of the "App Owns Data" intention.

The On-Premises Gateway connector clearly supports the ability to filter columns since it works via the manual way!

This leads me to believe I must just be unable to find the instructions of how to specify a column and array of values to filter it at the point of token creation, because everything thing needed to accomplish this is already in use!

Create a function called apiParameters() which can be used similarly to username() and userprincipalname()

"Role Name" [Column] = "manual string"

would be

"Role Name" [Column] = apiParameters()

request body would look like this...

... "identities": [ { "username": "john@contoso.com", "roles": [ "RoleExpectingParameters": [ "Param1", "Param2" ], "ManuallyCreatedRole" ], "datasets": [ "cfafbeb1-8037-4d0c-896e-a46fb27ff229" ] } ] ...

This would also ensure parameters couldn't be passed unless the report designer intended for that to be allowed by creating a role with apiParameters() as the value

Re: Power Bi Embedded Dynamic Row Filtering with SQL Server and App Owns Data

Anonymous — Wed, 21 Apr 2021 07:46:49 GMT

Hi @programthings ，

Based on my investigation, we can use DAX function USERNAME() or UserPrincipalName() or the CUSTOMDATA() function to implement dynamic RLS when the models with live connection mode that lie in Azure Analysis Services can use, I have not been able to find other way to achieve. It must be a function or property that Power BI can recognize if use apiParameters to implement dynamic RLS. . .

Power BI Embedded: Convention-based dynamic Row-level Security

Working with Analysis Services live connections

Best Regards

topic Power Bi Embedded Dynamic Row Filtering with SQL Server and App Owns Data in Developer

Power Bi Embedded Dynamic Row Filtering with SQL Server and App Owns Data

Re: Power Bi Embedded Dynamic Row Filtering with SQL Server and App Owns Data