Row Level Security With Hierarchies

Chris Barber shows how to create reporting which enables everyone within your organisation to see only the information for which they are authorised. The key benefits are:
1) Time saving - Maintenance of one set of data
2) Scale - The model is flexible as more individuals get added to the organisation
3) Secure - Leverages the current Microsoft infrastructure to maintain security by using the username of the logged in individual
4) Transparent - One set of data means that those who have the highest level of security can see exactly what everyone sees in the same report
The demo uses a fictional hierarchy to create commission numbers for each individual, limiting them to see only their data and that of anyone who reports into them directly or indirectly.
All files are in the link tinyurl.com/y3p5e7vr
Music: www.bensound.com

[@amitgoyal7189]

Great explanation, best RLS video I have ever seen! Thanks and keep us teaching :)

[@lambanlaa]

Your presentation of this topic is the most clear I have watched. Thank you.

[@bandishgupta2046]

One of the best Dynamic RLS videos !

[@bradsolly3289]

Fantastic, straight to the point and under 30 minutes

[@mariapradeepm]

Amazing Presentation, One of the best RLS video I have ever seen! Thanks so much for putting in detailed way!

[@chrisbarber639]

Thanks Maria

[@samueljfranklin]

Thank you for this wonderful RLS presentation. Wow - Enjoyed Every minute of it and High quality Tutorial. Great Work.

[@chrisbarber639]

Thanks Samuel

[@workstuff5253]

Fantastic explanation. Thank you for taking the time to show us.

[@hsalim82]

You explained that so well, many thanks

[@ramnathrajagopalan0510]

Perfect thank you

[@PaulEBrownbill]

Thanks for the presentation, very useful. Paul

[@LondonBusinessAnalyticsGroup]

Very welcome

[@rdg515]

Brilliant!

[@MaanEid]

Thanks a lot, very well organized and explaind !

[@LondonBusinessAnalyticsGroup]

Glad it was helpful!

[@demris15]

best explanation for RLS ive seen!! since you are talking about payment info, I'm curious if it would be possible to have the logged in user unable to view their own information. in many instances, their bonus/payments may not be approved until after they have approved their subordinates.

[@thiagoferreirabob]

Excelent video thanks. One sugestion, All "IDs" works better when we convert on Text.

[@PortogasDZ7]

Amazing video. Works as expected. Just a question, if I want to escalate this, and instead of returning one value, is there a way to receive multiple values and compare them in the PATHCONTAINS? or there is another solution for that?
For example, having a dimension Geo, and my Access table with 1 or more rows specifying the levels that I can see, where it can be many levels of the Geo dimension.
AccessTable
[ID], [AccessTo]
user1, Argentina
user1, US
user2, WorldWide
user3, NorthAmerica
Geo
[WW], [Region],[Geo],[Country]
WorldWide, America, NorthAmerica, US
WorldWide, America, SouthAmerica, Argentina
WorldWide, Europe, Spain
For users 2 and 3 it's just like in the video, I create a Path with all the hierarchies and search their value. Bur for user1, it would return 2 values.
I cannot use LOOKUPVALUE because it returns more than one value.
How do I filter for both of them (US and Argentina)?

[@maheralsahen8312]

great! thanks a lot.
I have an important question about the applying the "Lookupvalue" from another table in manage roles!

[@xxmysteryangel5xx]

Hi. out of all the RLS videos I've seen this was the easiest to understand. Will this work when a person has more than one position in a hierarchy?

[@gauravchouhan4703]

In this case if team member are supposed to see there team's total, how would that case work?

[@AustinWhitmire]

Do you know what would happen in your example if a user falls within both the HR and the Sales group? Will they default to the higher-level access (HR), or does it cause conflicts?

[@stevenhampson8657]

Filtering is additive. So they will see all data.

[@jamesdoku7807]

Hello,
Thanks for this video, that is what l need to implement my RLS, but in my hierarchy l don't
values for manager ID and user ID l only have the below ids
Manger id = ERF234 and user id =YTS234.
How do l convert the id to a number or how do l use these to implement the RLS.
Thanks

[@ratomarsupial1]

Is there a way to apply the RLS with hierarchies to a person that the manager has changed over time?

[@arturdzido7112]

Hello there, any idea how to overcome issue with users having multiple positions within hierarchy?

[@kmiles0151]

I get an error that the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[@LondonBusinessAnalyticsGroup]

Hi Ken, that errorcan happen when tou are creating a measure which must be a single value - and you are returning a table. - Mark

[@Fernando_Calero]

Did you fix your error? It happened to me too, what I did was to modify the DAX expression in DAX Studio replacing USERPRINCIPALNAME and removing the ADDCOLUMNS then copy it to PBI Desktop and it worked.

[@ricardodiaz7296]

​@@Fernando_Calero how was your dax code looking like at the end?

[@nicoles.2149]

When I use path(), it returns an error saying: "Tha value '' in TABLE[Parent_Account_ID] must also exist in TABLE[Account_ID]. Please add the missing data and try again."
You will always have a null value in the Parent_Accout_ID as not all accounts have a parent. How do I fix to make it work?