Dynamic Row Level Security

[samgreene1]

Hi,

In other reporting products, I've been able to retrieve the current user and then filter a dataset by that value.  In PowerBI I have created a measure using username() and am trying to create a relationship between that calculated measure and other tables.  This doesn't seem to be an option, as calculated columns are not presented as joinable columns in the relationship editor. 

I tried this approach because of this line.

https://docs.microsoft.com/en-us/power-bi/service-admin-rls

"You can enable dynamic security within Power BI Desktop by making use of the username() or userprincipalname() DAX functions and having the proper relationships configured."

I am querying SQL Server.  Tried direct and import methods. 

Using the roles feature seems nice, if you have a few users.  This is not the approach we take where I work if we can help it.

Is there a solution to this? 

Thanks,

Sam

[v-jiascu-msft]

Hi @samgreene1,

Were there any error messages when you created the relationship?

Best Regards,
Dale

[samgreene1]

There is not an error message.  It is not available to choose in the relationship manager.  Only the 'real' column is available. 

[Greg_Deckler]

You can create relationships with calculated columns.

[samgreene1]

If I attempt to use the USERNAME function in the query editor, I get 

Expression.Error: The name 'USERNAME' wasn't recognized.  Make sure it's spelled correctly.

[v-jiascu-msft]

Hi @samgreene1,

The "Dynamic" here means one role can provide different filters for different users automatically. Usually, the data one role can filter is static. For instance, Role 1 filters out the data of customer A. Now we can make it dynamic with function USERNAME. Please refer to the demo below.

IF (
    USERPRINCIPALNAME () = "userA@ms.com",
    [Provider] = 0,
    IF (
        USERPRINCIPALNAME () = "userB@ms.com",
        [Provider] = 1,
        IF ( USERPRINCIPALNAME () = "userC@ms.com", [Provider] = 2 )
    )
)

Best Regards,
Dale

[samgreene1]

@v-jiascu-msft , 

Thanks for that demo, but unfortunately that still won't work for me, even if I dynamically generated the filter clause for 150 employees and update it each time our staffing changes! I'm using Power BI Report Server and it seems security roles are not an option at this time.  It also looks like I'm not able to configure a data source to impersonate the currently connected user, if I were to get Kerberos working to pass security through to the db server and use dynamic views there (maybe since model is in import mode?)

I'm still curious about the possibilty to relate a calculated column based on username() to a column in my report data.  Perhaps I can't do this since I'm on report server? 

Thanks,

Sam

[v-jiascu-msft]

Hi @samgreene1,

The RLS for Report Server is still on the roadmap. Please refer to business-applications-release-notes/october18/intelligence-platform/power-bi-report-server/.

Did you create a calculated column based on username()? I'm afraid something is wrong. Please refer to the snapshot below.

Best Regards,
Dale

[samgreene1]

Greg,

Where would you create a calculated column?  I see 'new column' and 'new measure/quick measure' .  New column throws error 'USERNAME is not supported'.  I created a measure and it is not able to be related.

Thanks,

Sam