USP or Role measure to blank columns

[nlefalher]

Hello all,

I'm trying to upgrade my RLS. At the moment, RLS filters on region depending of the role.

I wanted to blank a column depending of the role or the user to blank a column.

I would prefer to get a measure using role to blank a column but i don't know if it's possible.

visuel_CA = IF(or(USERPRINCIPALNAME()="eb@pb.fr";or(USERPRINCIPALNAME()="philippe@pb.fr";USERPRINCIPALNAME()="allo@pb.fr"));calcuLATE(sum('CA-zsaisieent'[CA]));blank())

So, I wanted to switch the formula with email to role in RLS. I saw that it is working like this but I am too scared to forget to add a new colleague in RLS and in this formula.

So if you have any solution or ressources to achieve it, I already thanked you now 🙂

If you need more details, feel free to reply.

Thank you for your help.

[v-xicai]

Hi @nlefalher ,

You may set Row-Level Security (RLS) to manage roles, please see the link Power BI Desktop Dynamic security cheat sheet, which described the detailed steps. Maybe it doesn't work, there are some tips to let it work and test it efficiently.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nlefalher]

Hi @v-xicai ,

Your suggestion is good, i appreciate. But i want to hide some values regarding the role of the user. RLS allows only to hide lines. I want to hide only columns on a table, that's why I have to work on formula and RLS at the time.

Best regards

(If it's your blog, I will have a look soon. It looks good )

[JarroVGIT · Accepted solution]

Well this is now the 3rd time of typing this message, there seems to be something wrong with my account 😞 Anyway, here we go again. First of, very cool question! I haven't ran into this requirement before but I can surely see this is a valid usecase and I hope MSFT is adding this to their DAX implementation. 

But in the meantime, you can do this:

1. Create a table Roles with one Role column containing all roles. In my example I've used usergroupA and usergroupB.

2. Create the Roles and set their permissions on the Roles table to their own row. E.g. for usergroupA, the permission on the Role table is [Role] = "usergroupA".

3. Recreate your measure to check if the table Roles contains the value usergroupA.

Measure = IF(CONTAINS(Roles, Roles[Role], "usergroupA"), CALCULATE(SUM('CA-zsaisieent'[CA])), BLANK())

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[nlefalher]

Thank you @JarroVGIT  I took the time to do it well.

Pros and cons : I have to change all the formula where the value appears but it works.

Kind regards 🙂