Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

row level security but keep the data

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 c...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

https://quisitive-my.sharepoint.com/:u:/p/mario_anzaldua/EYIcZZdDMiFNlWzaY0OqMOEB5V6_XvJkS124TTRKN98sOg?e=bxpHzi

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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