Row Level Security


#1

Hi

This is a test scenario and I’m having the following issue

I have two tables

Users (with two columns)

Username Region Name


Auser Seattle

Buser San Diego

Regions (with two columns)

Region Name State


Seattle WA

San Diego CA

Row level security has been configured with filtering [Username] = USERPRINCIPALNAME() From users table.

I have the following Measures

_USERPRINCIPALNAME = VALUES(Users[Username])

_USERPRINCIPALNAME Region = if(HASONEVALUE(Users[Region Name]),VALUES(Users[Region Name]),“single Region must be selected”)

And following calculated column on Region

Filter Region = if(Regions[MarketName]=[_USERPRINCIPALNAME Region],1, 0)

if I view report as role Buser

_USERPRINCIPALNAME will be populated as Buser

_USERPRINCIPALNAME Region will be populated as Seattle

However Filter Region will always return 0. I was expecting for San Diego to return 1 and for all other regions to return 0

Thanks


#2

Can we also check the relationship you have in the model somehow? Maybe with an image.

Can you post images of the entire model by chance to review this more.

Thanks


#3

there are no relationship
these are two separate table. the user table gets filtered based on the userprincipalname() and the associated region with the user will be used to populate the following measure

_USERPRINCIPALNAME Region = if(HASONEVALUE(Users[Region Name]),VALUES(Users[Region Name]),“single Region must be selected”)

which in return will be used in the calculated column

Filter Region = if(Regions[MarketName]=[_USERPRINCIPALNAME Region],1, 0)

Thanks


#4

I’ve been thinking about this one quite a bit and don’t really have a solution at the moment unfortunately.

Have you made any progress at your end?

Here’s some links I’ve found that might be helpful

Hopefully these can assist.

It’s certainly not the easiest thing to implement in Power BI that’s for sure.

Thanks, Sam