Row-level security management is a very important aspect for many companies, not just the corporate-level ones.
For those new to the topic, the idea of row-level security (RLS) with Power BI is to restrict access to data for specific users.
The filters placed on the columns of the tables belonging to the data model limit access to data at the row level and it is possible to define filters within the roles in such a way as to define “who sees what”.
Note that in the Power BI Cloud service, members of a workspace have access to the datasets they contain, and RLS does not restrict this data access.
This is one reason why it is always advisable to separate datasets and reports into separate workspaces.
This article will present the new editor available in preview in Power BI desktop and, in particular, will examine a “static” RLS application.
How do you set up row-level security
The editor for managing this important feature is implemented within Power BI desktop.
Before you can use it, however, you need to enable it from Options > Global > Preview features (see figure 1).
After this step you are able to use the new feature.
This example used the sample dataset included in Power BI desktop.
Once the data has been loaded, you need to access role management (see figure 2)
The new editor has also been updated in terms of graphics and, in particular, after having made the choice to create a new role (1) you can choose whether (2) to work with the default setting (assisted and guided editor) or ( 3) go back to the classic editor where you will be able to enter DAX code manually.
Choosing the first possibility (use the facilitated method for defining the roles and the portion of the data that will have to be kept available) you will have to click on “Add” and you will be able to start defining which filters will have to be valid and which relationship, in terms of logic must exist among the conditions listed (see point 3: choice between All and Any).
In this regard, the possibility of “inspecting” the DAX code that is automatically produced is very interesting.
This way of working closely resembles what happens in Power Query in terms of translating, in terms of M-code, the steps performed by the user (see Figure 5).
As you can see, the filter on financials[Country] that was set with the “All” clause was translated to the following DAX code:
[Country] == “Mexico” && [Country] == “Canada”
This leads to no visible values since no row in the “financials” table will contain a value of both “Mexico” and “Canada” at the same time.
By correcting the clause set to the value “All” and selecting the value “Any”, we arrive at a coherent and sensible underlying DAX code.
This type of change is to go from a logical AND “&&” to an OR “||” which, in other words, is equivalent to “enabling” the rows of the “financials” table that will contain the value “Mexico” or the value “Canada” in the “Country” column (see figure 7).
This time, in fact, the DAX code underlying the operation performed is the following:
[Country] == “Mexico” || [Country] == “Canada”
To observe the effect of applying the “Test” role, save and close the editor and then operate on the menu enabled by the “View as” command (1), select the “Test” role (2) and confirm with “OK” (3).
The visualization of the data appears limited as foreseen by the role (4) (see figure 8).
Complex filters through groups
Before concluding, it may be useful to take a look at the possibility of expressing more complex conditions by proceeding to group “blocks” of conditions that will have to be coordinated internally (1) and externally (2), i.e. with respect to the other blocks (see figure 9 ).
This type of setting is obtained by selecting the individual conditions (1) and then clicking on the “Group” button (2) (see figure 10).
In the final example of this article, the selection and formation of groups was set up as can be seen in Figure 11.
The DAX code that was automatically generated to satisfy the request expressed through the “facilitated” editor is as follows:
([Country] == “Mexico” || [Country] == “Canada”) && [Month Name] == “April”
This time the brackets have been added to allow a correct evaluation of the expression which is to enable all the rows in which it is simultaneously verified that the “Country” column is equal to “Mexico” or “Canada” and the “Month Name” column equals “April “.
The result can be seen in figure 12.
Conclusions
In my opinion, this small evolution appears to be a clear sign of Microsoft’s desire to increasingly facilitate business users’ access to Power BI functions.
In fact, establishing “who sees what” has a lot to do with logics that are often discussed by management figures, even if implemented by the IT world.
This type of facilitation seems to encourage the “business” analyst to independently implement certain types of corporate policies.
Nonetheless, even “pure” IT professionals will have an easier time setting up static RLS logic.
Thanks for reading and see you next time!
Autore del Post
