Dynamic Row Level Security in Power BI with Many to many Best Practice

Рет қаралды 4,176

Күн бұрын

Пікірлер: 11

@officesuperhero4651 2 ай бұрын

Excellent tutorial as always. I had a situation like this. I work for a school district. So, one teacher could have 100 students and each of those students could have multiple teachers. But I needed to create RLS for teachers so that they still only see their own students. You provide a great solution. Thanks.

@RADACAD 2 ай бұрын

Glad it helps, thanks for sharing your scenario here.

@eprocket97 5 күн бұрын

Thank you for this video! I do have a question for a situation that I am facing, I need to implement RLS based on Hierarchy but also using this type of one to many structure. Any advice on this would be great, im thinking it would be a mix of the logic on this video along with PathContains from your Power BI Dynamic Row Level Security with Organizational Hierarchy video, Ive now followed both but unsure how to merge the logic together

@joaocarrilho7649 Ай бұрын

Congratulations on the video! Was the 'UserProfile' table created manually to establish the relationships between 'UserId' and 'ProfileId'? Would the maintenance of the RLS be only in it?

@RADACAD Ай бұрын

Yes, this table is the table that keeps the logic of who has what role. and needs maintenance. sometimes you might have O365 or Active Directory groups that have this logic in them, in that case you can pull data from those and create this table using those.

@AnshulMarele-gbaa 2 ай бұрын

👌🏻👌🏻👌🏻

@DoThePoint 2 ай бұрын

Can you just combine the user and user profile table and use one way filter with many-to-many to profile?

@RADACAD 2 ай бұрын

even if you do that, you would still be on the many side of the relationship, and would require a both-directional relationship, which is not good for performance. and also; maintaining such table won't be easy. for every user or every profile, you will need to maintain multiple records. and if later another dimension adds to that it makes it super complicated, better to do it the right way (which seems harder at first, but simpler to maintain in the future) that what seems to be easy way (which will be harder in the future to maintain)

@varamo9917 2 ай бұрын

@@RADACAD so, if you wanted to use the user in a slicer (say you wanna see the sales of a vendor), you would have to replicate this DAX in your measures and wouldn't be able to filter their profile. How bad would it be to denormalize profiles and users into a single table, same number of rows as the bridge table, in a many to many relationship, one direction relationship with sales. Maybe I don't see how much of an issue it would be to maintain such table or performance wise, but it would make RLS and slicing more intuitive.