Filter out rows based on loged in user

[jomu]

Hi,

I struggle with an issue and did not find a solution until now. Maybe someone could help me.

There is a dimension table: DimPlaces

DimPlaces hold mainly cities like London, Berlin, Rome, Madrid, Boston etc.

Now different uses can use my report and the log in via the cloud. Which uses accesses the report is easy to find out. I implemented a measure CurrentUser := USERNAME()

Then I want to filter out some rows from DimPlaces based on the current user.

For this I had an additional table UserPlaces like:

Mike – London
Mike – Madrid
Pedro – Rome
Herbert – Berlin
Herbert – Rome

My measure tells me which use is active, I can find out the cities for this user but how to filter out the entries in DimPlaces that the user should not see?

Any advices?

Best regards

[Anonymous · Accepted solution]

Hi @jomu 

So what you're trying to achieve is called RLS (short for Row-level Security). Fortunately, you have everything you need to implement RLS, so do the following:

Create a relationship between your two tables DimPlaces and UserPlaces with City as the connection column. 
Then go to Modeling -> Manage Roles and create a new role for your UserPlaces tables. Here you can create a simple DAX saying something like Name = USERNAME()

This will filter your UserPlaces table, which will then filter your DimPlaces due to the relationship between them. 

Hope this helps!

[v-frfei-msft · Accepted solution]

Hi @jomu ,

Please refer to the online document about setting RLS.

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

[Anonymous · Accepted solution]

Hi @jomu 

So what you're trying to achieve is called RLS (short for Row-level Security). Fortunately, you have everything you need to implement RLS, so do the following:

Create a relationship between your two tables DimPlaces and UserPlaces with City as the connection column. 
Then go to Modeling -> Manage Roles and create a new role for your UserPlaces tables. Here you can create a simple DAX saying something like Name = USERNAME()

This will filter your UserPlaces table, which will then filter your DimPlaces due to the relationship between them. 

Hope this helps!

[jomu]

Yes, that's a solution.

The only issue here (well, not really an issue ...) is, that security is not a concern in this project. It is just a question about usability - there are many places and a user should not see places (for selection) that he does not need.

Thanks to you I can realize this via security.

Regards,

jomu