Power BI Row-Level Security And Where To Filter

yo what's up this is Patrick from Guyana cube and in this video I'm gonna dig deep into some row level security stay tuned okay so row-level security there's lots of videos and blog posts Adam even did one on it right so why why are you doing it well Adam and I ran into a little challenge we came up with a couple of ways to solve this and you guys know what power bi there's probably a gazillion ways a gazillion a lots of ways to solve the exact same problem and so I want to put this video out there because I'm curious on how you guys solved this problem did you run into it right so if you have any ways that you've run into this posted in the comments below the scenario was well the best way for me to explain as to what head over to my laptop okay check out this data model so it's pretty straight forward I have you know my internet sales table a customer table sales territory table my date table and a product table and on my cells territory table I actually have take a people take a people look at this check out my sales territory table and have a username now you're gonna see me use a scalar function called username throughout this video that's because the customer I was working with they're doing embedding their embedded in their power bi into their own application if you're using power bi comm and email addresses you want to use user principal name the scalar function use a principal name instead of user name okay just want to put that out there using embed it and you're not using an email address you have your own custom way of users authenticating into your application use user name because you can pass in what they are authenticating with and use that in your role level security all right all right let's head back to my laptop okay so that's what we have here right I have you know Patrick owns the United States and Adam is more of an international type of guy right he is more of international I like to stay right here in my little country okay all right I have this table basically what I want to do the first thing they said was patch we need row-level security because we want Patrick to see this and Adam to see that that's it piece of cake gonna manage rolls easy easy peasy create a roll we're gonna call the security right and what we're gonna do is go to my sales territory and say username equals username check it easy BAM right so then I really like the desktop desktop because it allows me to check it out before I actually deploy so I'm gonna go ahead and say viewers roll say this Patrick and security and click OK don't fail me now and notice everything is filtered out to just the United States what I have access to the five regions in the United States my slicer is only set to United States and hey I'm logged in as Patrick right but check this out and I only have seven thousand eight hundred and nineteen customers but if I click so when I click this customer pretty cool right they were like Oh Patrick you are so so so so smart like a little bit right then they click this one's like it's great then they click on Aaron Allen and they got nothing like why why doesn't every now and so anything I was like well he may or may not be your customer that may that's maybe Adams customer and they go whoa stop the presses Patrick stop stop stop stop stop stop stop so when the one customer signs in when one of our clients sign in we don't want them to see the other clients customers and vice versa I was like whoa how do you figure that out how do you stop that so I got to take it right my brain got to turn in and they go especially when we use a slicer we don't want things to show up blank like this was like ok ok guys I get it I get it so Adam and I chatted we actually talked to our friend Marco what's up Marco thank you so much for giving us helping us out with this problem and we came up with two different types of solutions and let me show you what we did let me show you the first way all right so I'm gonna stop viewing stop viewing the very first thing we did was we created a bridge table and if you read documentation and stuff out there there's lots of documentation out there that it talks about the bridge table and so basically in my briefs table I have sales territory key and customer T key t90 every possible combination of sales territory and customer but what I also did was based on the sales territory I added the username to this table okay and you'll see why in just a little bit and so once I have that done I'm gonna go over here and create a relationship between sales bridge customer and the customer key all right and it automatically turns it on for bi-directional filtering but what you also have to do is go and check the box labeled apply security filter in both directions so I was a little apprehensive about using this bi-directional filtering especially after field attending Marcos class on Dax mastering Dax I was really really assertive but after I talked to him you know he said hey Patrick you know this is not bad because using the breeze table it's very not that likely of introducing ambiguity right we'll have another video talking about ambiguity which or bi-directional filters but go ahead and follow this and click OK right and it works there you go so I got my my relationship setup bi-directional be sure to check that box and then I came back to my manager roles check this out let's see how hard this was actually repeated the exact same Dax expression like check make sure I check it click OK click Save alright now let's go back here you guys remember Aaron Allen right I'm gonna sign back in this Patrick it's simulating I'm gonna act like I'm Patrick yeah I am really Patrick Security let's see if Oh Alan Aaron Allen disappears click okay boom Aaron Allen is gone and as I click through my list there you'll see how right every customer selecting that list is associated to the United States is associated to this guy right here Patrick not this guy I'm at the sky but right the client okay all right great I was like ok Marco Adam which one should we use they were like well which one performs better all right and so Marco did say he did say that the direction one would perform better but you know right I you know I got a see I got a see I got to make sure which one performs better so using that studio I was able to get some metrics on this one and then I came up with a different approach so I'm gonna stop viewing and come over here and what I'm gonna do is let me see manage my roles I'm gonna clear this filter out let me clear this filter out that and I'm gonna delete this relationship it's like Patrick you a crazy you just went through all that work why would you delete it it's like cuz I want to something I want to pursue something else would work so have you guys been working with SSAS you know especially tablet miles when they first came out there's a great there was a great article written in the docs that Microsoft and lots of other people wrote about it using a lookup value I mean didn't really didn't require a relationship because you can establish that relationship using the lookup value not gonna go into all the details about lookup value go look it up it's just do a quick Bing search and you'll find the answer but I have some code here that I've written and so we're gonna go back to manage roles and we're gonna add one here on my customer table add filter customer key right and it's just a little code where I'm doing a lookup I'm finding the customer key it's like in the join and T sequel saying give me the customer key join on username username customer key customer key right and returns any turns it returns those customer keys and then what I'm also gonna do is they add a filter to hide all my roles in this table because I don't anybody messing with this rate right click Save let's see if this works let's see if the behavior works so we're gonna go here view us field twos so let's see Erin Adams like one second let's make sure so Adam had a few so we're gonna log in as add-on this time let's make sure these two guys disappear so we view its roles and I'm gonna use Adam this time security click OK give it a little bit to think seems a little slower than the bidirectional it really does right so Aaron Allen was the first one that won you can see I'm signed in as Adam behavior is the same I have to look using back studio I did realize that this method is a little slower than bi-directional filtering but completely up to you completely up to your volumes of data you know I'm just curious how are you guys so how do you have you ran into this problem before how are you solving this problem you know post it where in the comments below if it's a really cool solution I'll reach out to you probably do a video on it give you a big shout out what do you guys think again post some comments give me some ideas some thoughts right looking for other ways to do this if you've done in other ways maybe this is your first time seeing it if this is your first time visiting a guy in the cube channel what you got to do be sure to subscribe right if you like my video big thumbs up as always from Adam and Patrick thanks for watching we'll see you in the next video

Channel: Guy in a Cube

Views: 153,156

Keywords: Power BI Row-Level Security And Where To Filter, power bi row level security, power bi row-level security, row level security in power bi, dynamic row level security power bi, power bi dynamic row level security, power bi embedded row level security, power bi dax, power bi desktop, power bi dynamic rls, power bi dynamic security, power bi relationships, power bi tutorial, business analytics, business intelligence, dynamic filtering, patrick leblanc, rls

Id: 9wN33rTaiB4

Length: 9min 41sec (581 seconds)

Published: Wed Jul 11 2018