How to implement row-level security

Check list of all posts

Row-level security restricts data rows of data from being viewed by report users, which is the key topic to resolve for all BI tools. Cognos topically resolves this problem at FM model and data module level. I have provided all options for Cognos ( How to implement row-level security in framework manager (all options))

While Cognos Analytics uses Macros to capture user's information, Power BI applies DAX to implement all complicated security logic.  There are three significant areas for implementing row-level security:

1. Static RLS vs. Dynamic RLS:

Static RLS is to different roles, and then assign users and user group to the role;

Dynamic RLS is to use a table to derive a security assignment. It captures the logged user;  and then matches the user's login to acceptable rows based on relationships in the data model.    

2. Simple relationship vs. Complicated  relationship

Simple relationship: The security dimension is one of the dimensions in the STAR scheme: In other words, there is a direct relationship between fact and the security dimension. A security dimension is the dimension whose column is used to filter with DAX.

Complicated relationship:    Security dimension does not directly connect to a fact table; normally, there is a bridge table to define the relationship between the security dimension and fact or another dimension. most of the cases, to make the filter work, we need to enable a bidirectional relationship between the bridge table and another dimension table; 

3. Simple DAX vs. Complicated DAX

Simple DAX: such as   "[Security dimension Name] ="value", or   [Security dimension name ] =UserName() or UserPrincipalName()"    

Complicated  DAX:  such as "[Email - Work] = USERPRINCIPALNAME()

|| [Org Level 4] = USERPRINCIPALNAME()

|| [Org Level 3] = USERPRINCIPALNAME()

|| [Org Level 2] = USERPRINCIPALNAME()

|| [Org Level 1] = USERPRINCIPALNAME()"    

4. Cases

We can combine with all cases and listed below:

case 1: Static RLS;Simple relationship;Simple DAX

case 2: Dynamic RLS;Simple relationship;Simple DAX

case 3: Dynamic RLS;Simple relationship;Complicated DAX

case 4: Dynamic RLS;Complicated  relationship;Simple DAX

case 5: Dynamic RLS;Complicated  relationship;Complicated DAX

4. Reference

Setting Up Power BI Row Level Security

Row Level Security Organizational Hierarchy