Hello! In this video we'll investigate Row-level security.
We have a basic dashboard that we will personalize for different sales people. It displays the sales over time on the graph on the left, and on the right we see three different cards with extra sales information such as the transaction count.
We have a fact table to pull this information, but it is comprised of different entities. The sales are from multiple stores in different countries and different regions. We can filter the fact table to only provide transactions that match with specific entities. Let's go to modeling and navigate to manage roles.
We create a new role and call it entity. We'll select the Dim_Entity table to filter on, and more specifically to email. As we learned in the previous video, we can use the USERPRINCIPALNAME() function to return the email of each user. Power BI will filter on the email address of the logged in user, and only show those values. We navigate to the Dim_Entity table to see how this works. If a person with an email out of this list would log in, they would only see the sales values associated with their email address.
Let's navigate the modeling page and select view as.
We tick both check boxes so we can type one of the email addresses present in the Dim_Entity table. Let's type [email protected], to see how the dashboard would look like for the Switzerland entity.
The header changed to Switzerland, the sales value over time changed, and the cards on the right also changed. We do notice however that Product Category count is still 8. We don't sell all product categories in Switzerland, so something is going wrong here.
When we investigate the formula, we notice the product category counts the values in the ProductCategory table, instead of the fact table. We used a cross-filter function to make sure it could go between the ProductCategory and the fact table, but it doesn't seem to be applied correctly in this case. There is nothing wrong with the function, so let's investigate how the relationship between both tables looks in the Model view.
The USERPRINCIPALNAME() function we defined earlier filters for values related to an email in the Dim_Entity table. There is a connection between the Dim_Entity and the fact table, so the corresponding value in the fact table got shown correctly.
The arrow is going from the dimensional table to the fact table, but not from the fact_table to the dimensional table, which is why we are unable to query it. We need to change the relationship to both and apply the security filter in both directions. It is important we tick this check box, so the information can flow from the Dim_Entity table all the way to the ProductCategory table using security filters.
The report pane now shows us the correct number of product categories of 1. Contoso only sells products from the TV & Video category in Switzerland, so this makes sense. That's it for the demo. Over to you!