Calculating Total, and Percentages in a Row-Level Security Power BI Model

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

I have written dozens of articles about row-level security so far and explained in all of them explained different methods in which you can reduce the amount of data rows that a user can see based on their roles. However, in my presentations, I still get this question that: What if I want to compare the branch data against the total, but I don’t want the user to see other branch’s data. My approach to solving that is always an aggregated table. Let me walk you through a sample of it in this article. If you like to learn more about Power BI, read Power BI from Rookie to Rock Star book.

Prerequisite

I highly recommend reading these two articles before reading this article:

and I also highly recommend below articles, if you are interested to learn more about patterns of dynamic row-level security in Power BI:

Introduction

Row-Level Security in Power BI means limiting the access to the data rows in an existing Power BI dataset or report. Let’s say; you share a Power BI report with user 1 and user 2, but you want them to see only part of the data from the whole report, not the entire data. You want user 1 to see for example sales of USA, and user 2 to see sales of Canada. This is something you can do with row-level security, and in this article, I explained the basics of it.

For example, you can have a report which looks like below:

Which has information about all countries, and then you can apply row-level security that limits the access for some users, and they see only part of the data, like this:

The screenshots that you have seen here are coming from my row-level security article which I explained step by step guide of how to implement it.

The Challenge

One of the challenges in the above implementation is that how you can access the total level data (average, sum) without exposing the details. Let’s say that the sales manager of the United States wants to compare his sales with total sales and see how much percentage of the total sales across all countries belongs to his region. Unfortunately, using DAX functions such as ALL or other functions that changes the scope of filter won’t give you much luck. Because the row-level security is applied on the dataset table level before setting the filter context.

I usually do this with an aggregated table for the total values. Users don’t need to learn about the details of other countries, they just need a total of all countries. so this can be an aggregated table with no country information in it. Let’s see that through an example:

Sample Model

I am using the AdventureWorksDW2012 Excel dataset, which you can download it here. and I am using these tables: DimSalesTerritory, DimDate, DimProduct, and FactResellerSales.

Create the Aggregated Table

You can create this aggregated table anywhere: in the data source using T-SQL or other database languages, or in Power Query using Group By or any other places you are more comfortable doing the aggregation. I use Power Query because that is the method you can use regardless of the data source.

The first step is to create a copy of FactResellerSales table, then Group By in the Power Query Editor;

For this Group By operation, I use all key fields in the FactResellerSales except SalesTerritoryKey (because that is the field that connects this table to DimSalesTerritory, which is going to have the row-level security filter enabled on it). You can also add as many aggregations you want as Sum, Min, Max, Average etc.

Add the Aggregated table to the model

In a very simple data model, your aggregated table might be only returning one row: the total. However, if in your data model is slicing and dicing by multiple dimension tables, then it is likely that you need to connect your aggregated table to other tables. Our sample here is one of those too. I connect the aggregated table to the DimProduct and DimDate, but not to the DimSalesTerrirory, because now this aggregated table has the totals of all territories.

Row-Level Security Definition

In this sample example, I have used static row-level security. However, the same approach with slight changes can be used for dynamic row-level security too. here are my roles:

Testing the Solution

Well, that’s pretty much all of it in terms of the modelling. To test the work, you can create a measure such as below:

and show it on a page with other visuals:

Now, if you try to view this as a user, let’s say United States Sales Manager user, this is what you see:

You can see that the detailed table (FactInternetSales) got filtered by the row-level security configuration. However, the total table only gets filtered by other dimensions (such as date table in this case), not the DimSalesTerritory.

How to Implement it with Dynamic Row-Level Security?

If you are implementing this in a dynamic row-level security approach, then the aggregated table should not be connected to your user table (in simple DRLS scenarios). However, for more complex scenarios, you need to think about how to implement it. for example, you might want other users to see the total of higher-level organizational hierarchy or something, which depends on that the aggregated table should be created and connected to the model.

Summary and Study More

In Summary, this article was an explanation of the aggregated table approach used to enhance the row-level security based dataset, so that you can calculate values vs total, or percentages or etc. I have written the most comprehensive set of articles you can find about row-level security in Power BI, which I highly recommend you to read them all;

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply

Your email address will not be published. Required fields are marked *