row level security but keep the data

[Anonymous]

My company is evaluating Power BI as a replacement for our current BI tool. As a proof-of-concept, I developed a report that visualizes our sales data. My visuals aggregate data from multiple sales channels. Senior executives need to see all of this data. However, Channel executives need visibility to their own channel data; they also need to know how they perform relative to the other sales channels.

My thought is to somehow create an "Other" category to use for data that can be hidden by RLS. However, the "Other" calculation has to be dynamic based on the User Role so that each user sees his data an "Other".

Is this something that can be implemented with DAX and RLS?

Senior executives would see the visual below, but VanArsdel executives would see the VanArsdel column and one "Other" column.  Or, each of the "Non-VanArsdel" columns could be renamed "Other1, Other2, etc."  Just as long as the names are obscured.  This idea would be preferred as all the visuals would be the same, just the names are hidden

 

Appreciate any suggestions.

[v-shex-msft]

HI @Anonymous,

Please share some sample data(sample data with same core data structure) with expected result for test, I think it will be help for coding dax formula.

In addition, you can consider to setup multiple level fields as different category to group your users, then you can apply RLS on different level fields to achieve accurate role mange.

Regards,

Xiaoxin Sheng

[Anonymous]

https://quisitive-my.sharepoint.com/:u:/p/mario_anzaldua/EYIcZZdDMiFNlWzaY0OqMOEB5V6_XvJkS124TTRKN98...

Thank you, Xiaoxin

The link above takes you to my test Power BI Model.  Page 2 shows an example of the result I am looking for.  I would like for a user to see all of the data, but the names are hidden.  So, If I am part of the VanArsdel group, I will see the name "VanArsdel" not Other7.  All other Manufacturer names are Hidden as Other1, Other2, etc.  Each user would see only his/her Manufacturer name and the remaining names show OtherX.  So, the row level security hides the names, but keeps the data so each Manufacturer sees his/her data relative to the others but not the other names.

Thanks for your help.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>>So, If I am part of the VanArsdel group, I will see the name "VanArsdel" not Other7.  All other Manufacturer names are Hidden as Other1, Other2, etc. 

Unfortunately, current power bi not allow to create calculate column/table based on USERNAME/USERPRINCIPALNAME function. RLS also not support to dynamic hide some of data model fields.(row level based security, it not means object level security)

Measure is possible to achieve this, but it can't use as axis to expand summarized records. 

In my opinion, I'd like to suggest you create new table to duplicate specific table records to twice(original records and replace name records) and custom field 'is hidden'. 

Add a user control table to set up which type of records users can view.(manger to see his data and original records, normal user see their own data, particular users can see their data and replaced records)

After these steps, use duplicate table as source of specific visuals which need to be viewed by channel executives.

In addition, you can also consider to setup security on database side.

Regards,

Xiaoxin Sheng