Hiding a column based on a user

[Anonymous]

Hi Folks,

Just trying to figure out something. There is a small population of users that I need to give access to the reports, but they are not allowed to see a performance rating column. Is there any way to hide this based on a group of users? 

So for example, if  Joe Employee logs in and he isn't supposed to see performance rating, how can I make it appear as 0 or N/A? 

Many thanks!!

[smowais]

I believe you're asking about hiding columns based on user role and privileges. For design mode we can simply hide the columns but that would be a static approach and surely can not be workable for a large number of users and roles. 

I am investigating the same capabilities in Power BI and we know there is no such built-in feature in Power BI but I not 100% confident but I believe this could be achieved by following two ways:

1. Customized Power BI Visual Development using TypeScript and Power BI REST API

2. Dynamic data-source, even with dynamic OLAP cubes. (Performance could be a concern)

I am working on Proof-of-Concept and would share my learning soon.

[Ivo]

Hi @smowais, please do as this is something that a lot of people are looking to do.

Thanks,

Ivo

[Anonymous]

Good to know. Thanks!

[v-shex-msft]

Hi @Anonymous,

For your requirement, I'd like to suggest you to use query parameter to dynamic control the displayed columns.

Steps:
1. Create query parameter to store user name.
2. Create mapping table with 'user name' and 'column name' 

Sample:
User, Columns
abc, {"ColumnA","ColumnB"}
bc, {"ColumnA","ColumnB","ColumnC"}
Test, {"ColumnB"}

3. Find out the specific table column names based on above user parameter, then use it as 'table name' parameter of Table.SelectColumns function.

Reference links:

Deep Dive into Query Parameters and Power BI Templates

Table.SelectColumns

BTW, I haven't found a way to pass 'username' to query editor, so it is impossible to create dynamic table based on username.

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you. I will try this and see what happens. Much appreciated. 

[parry2k]

@v-shex-msftyour solution is ok but if any of the field is used in report and is not available in data model based on user column selection, report will stop working, so I personally don;t think it is advisable to not to return the columns to the model , it is better to make multiple roles based on user and remove those columns. 

Let me know if I missed something.

So solution is to create calculated column and remove value from the column, it will break any report.

[Anonymous]

Thanks for this. While the formula works to put a dash, the problem is since we can't turn off underlying data they can still export the aggragate and see the salary information.

I'm so bummed out about this. 😞 😞 😞 😞 

[parry2k]

@Anonymous In my very first reply I mentioned you about this. You really need to create seperate report and remove this field from data model, there is no other way at this point. If there was ability to pass user name to "power query", it would have been nice. You have to mask your data at source end or remove the column from model.

[Anonymous]

I know. I just wish underlying data could at least be turned off and not just full export. 

[Anonymous]

Hi @parry2k,

I hope you are well. I tried doing this again using this formula: 

Amount Hide 3 =
var r = IF(CONTAINS(Users,Users[UserName], USERNAME()), 1, 0)
return if(r = 1, Calculate(SUM('Active'[Annual Salary (USD)])), Blank())

However, when I add this into my table visual, all the records show up as blank values if the user is not there, not just the one column -- strange. I only want the Annual Salary (USD) column to show up as blank values. Is there something I'm doing wrong? I don't have the users table mapped to my active table because if I do that then only one record shows. 

Please and thank you!

[Anonymous]

I still can't get this to work correctly. 😞 

[Mitsql]

@Anonymous Try setting a role-based filter in power bi desktop before publishing to the cloud. Also, you can check using "view as role" before publishing.

[parry2k]

@Mitsql it is for row level security, not column level. 

[Anonymous]

@parry2k Is there a way to make it blank or with a "-" based on a user logged in? They only have read only access to the dashboards.

[Anonymous]

@parry2k Any ideas would be great please 🙂

[parry2k]

You can add calculated field and check if user has permission to see the value to that field or not and then make that field blank()

here is link for reference 

Just an idea

Amount Hide = 
var r = IF(CONTAINS(UsersTable,UsersTable[login], USERNAME()), 1, 0)
return if(r = 1, Table[Amount], blank())

[Anonymous]

Does this get added as a Page Filter Measure? If I have a field that is dragged into a table or matrix visual then that has the review rating, how does this prevent it from being visible? 

I guess what is where I am struggling a bit. 

[parry2k]

Sorry @Anonymous didn't explain well, I assumed you have amount field you want to hide, so this new calculated column will give blank value and then use calculated field in your report instead of your original field

[parry2k]

you cannot hide column but you can hide the value, having said that, user can have access to the value if he knows how to do things in powerbi