Power BI & DAX: Data Masking vs. Row Level Security (RLS)

Data Masking is vastly different from Row Level Security (RLS). Often, we get frustrated with RLS because we are trying to solve a data masking problem by using RLS techniques. Data Masking is feature in the security realm that allows us to show the totals for all the data at the high level while allowing us to mask the details for the data elements that a user may not have access to.

Here is a good example: two salespeople can see the overall sales for the entire country, but at the customer level they can only see their own sales, the sales of other salespeople are masked. This is different from the default RLS behavior that would filter out data that they are not supposed to see which would make them unable to see the overall company performance, but only their own.

This video provides all the details and additional background on data masking as well as some implementation techniques:

The security mapping table looks like this:

Distribution Channel

User

All

Andre

Retail

Jacob

Digital

Jack

Direct

Jane

3rd Party

John

And here is all the DAX that I use:

Masked Revenue =
IF ( [isMasked], “*”, [Revenue] )

isMasked =
VAR hasAllAccess =
    IF (
        CONTAINS (
            DistributionChannelUser,
            DistributionChannelUser[User], USERPRINCIPALNAME (),
            DistributionChannelUser[Distribution Channel], “All”
        ),
        TRUE (),
        FALSE ()
    )
VAR hasChannelAccess =
    IF (
        CONTAINS (
            DistributionChannelUser,
            DistributionChannelUser[User], USERPRINCIPALNAME (),
            DistributionChannelUser[Distribution Channel], SELECTEDVALUE ( ‘Distribution Channel'[Distribution Channel] )
        ),
        TRUE (),
        FALSE ()
    )
VAR moreThanTwoChannelsVisible =
    COUNTROWS ( DISTINCT ( ‘Distribution Channel'[Distribution Channel] ) ) > 2
RETURN
    IF (
        hasAllAccess || moreThanTwoChannelsVisible
            || hasChannelAccess,
        FALSE (),
        TRUE ()
    )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s