One of the top things on people’s mind is, of course, security. In today’s business world, security of your data and information is of utmost importance. In this post I’ll review Row Level, Column Level and Table Level Security in Power BI.
Most people using Power BI think about Row Level Security, which is the idea of setting up rows based on different roles within an organization. A common mistake is people think this is called Role Level, not Row Level Security. Yes, the security is based on roles but we’re creating security at the row level.
Row Level Security
- Let’s say you have a fact table, for example, which contains sales data for your company. This sales information is about products that are sold all over the country. Sales reps are typically assigned to different regions, whether that be the Northeast or Central US, etc. The rep in each region should only be able to see sales information that pertains to them and their region.
- The sales director or manager of the whole team would need to see the sales information across all the regions. This can all be set up with Row Level Security. This is done by filtering down a table that pertains to each row that belongs to each region.
Column Level Security
- It’s important to know that in Power BI you cannot do Column Level Security. In this case think of an HR person that should be able to see all the sales data in all regions, but also should be able to see salary level information that sales reps should not have access to. This must be a separate report because in Row Level Security, there is no way to filter out a column like salary level, for instance.
- The ONLY way you can have the ability to do Column Level Security is in the Tabular model. Doing it is a bit tricky. To try to put it simply you’d still use the Row Level Security in the manage rows option in Power BI but instead of making a filter based on a certain demographic within a column (Region equals Northeast for example), you’d make a filter in a column such as Salary equals False. This would eliminate that column for the entire report.
Table Level Security
- With Table Level Security you can eliminate entire tables from a report. You can do this in any model, not just Tabular.
- To do this, you’d put Table Name equals False and that would filter out the table from the entire report depending on certain roles you have set up.
- This can be handy but another key thing to note is that Column and Table Level security can break certain visuals. If you put certain columns into a visual and that visual no longer has those columns, it will break that visual and no one will be able to see it if that column or table is missing.
-
These are all things to consider when you’re thinking about different types of security settings that you have in your Power BI reports and how to best set up security for your needs based on roles within your organization. If you have questions about how to set up any of these types of security in Power BI or questions about Power BI in general, we’re here to help.
Are you maxing out your bandwidth dealing with Power BI security management compliance and end user support? Our fully managed services centralize the management and architectural support – let us take on that burden and you can focus on growing your business. Click below to learn more about our Managed Services offerings.