Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter out rows based on loged in user

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

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

    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!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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