Requests to imple­ment Row Level Secur­ity (RLS) in Power BI dash­boards are on the rise as the applic­a­tion con­sol­id­ates its pos­i­tion as a favour­ite report­ing tool.

Most dash­boards are designed for a very spe­cific audi­ence, but what if they need to cater to a broader group with vary­ing require­ments for data gran­u­lar­ity? Or what if dif­fer­ent data needs to be dis­played based on RLS in a par­tic­u­lar visual?

In this blog post, we are going to present a stand­ard solu­tion to the chal­lenges out­lined above. Our goal is to modify data gran­u­lar­ity visu­al­isa­tion in a bar chart by chan­ging the X‑axis data accord­ing to the organ­isa­tion level to which the user belongs.

Solu­tion

Ini­tial Requirements:

  1. Dis­play the pro­ject stage for each com­pany divi­sion and depart­ment using a bar chart.
  2. A user can only view their own pro­jects, or those asso­ci­ated with their lower organ­isa­tion hierarchy.
  3. The data should be visu­al­ised accord­ing to the user’s hier­archy, with the option to drill down for more gran­u­lar­ity if a lower organ­isa­tion level is available.

We’ll use the fol­low­ing hierarchy:

Fig­ure 1: Organ­isa­tion Level

The sample data used for this rep­res­ent­a­tion is com­posed of:

  • Employee Table (D): Organ­isa­tion employee data.
  • Pro­jects Table (F): Pro­ject-related data, like name, stage, or status.

It is import­ant to note that the Employee Man­ager Key Path (EMP_PATH_TX) must be impor­ted into the Pro­jects Table. This is because RLS will fil­ter data for a single user only, and as stated in the second point of the Ini­tial Require­ments, we also aim to cap­ture pro­jects man­aged by other users that belong to lower levels.

To assign the neces­sary organ­isa­tion level to each user, we have added a cal­cu­lated column named ORG_LEVEL_TX, with this logic:

IF (ORG_LEVEL1_TX = ORG_LEVEL2_TX, ORG_LEVEL2_TX, ORG_LEVEL3_TX)

Fig­ure 2: Employee Table

Fig­ure 3: Pro­jects Table

Once these tables have been impor­ted into the Power BI model, we have to cre­ate a cal­cu­lated table, named Para­meter, to define the columns that we want to switch based on the RLS set­tings; this will be used as the X‑axis in our visualisations.

As out­lined below, three fields are neces­sary within the func­tion to define this table:

  1. Column Name: the first field spe­cifies the name the column will take visu­ally. For ini­tial test­ing to ensure the visual changes accord­ingly, we will keep the Employee Table nomen­clature and only change the names for columns that will be repeated in drilldowns.
  2. Data Source: the second field is the phys­ical column from which we want to extract data, util­ising the NAMEOF() function.
  3. Index Num­ber: the third field is an index num­ber, used to add addi­tional columns that act as drill­down levels, facil­it­at­ing a deeper data analysis.
Parameter (Calculated Table)
Parameter = {
    ("ORG_LEVEL2_TX", NAMEOF('Employee'[ORG_LEVEL2_TX]), 0),
    ("Department [Directorate Level]", NAMEOF('Employee'[ORG_LEVEL3_TX]), 0),
    ("ORG_LEVEL3_TX", NAMEOF('Employee'[ORG_LEVEL3_TX]), 1)
}

The last table to be defined is the Bridge Table, which will serve as the link between the Para­meter and Employee Tables. This is neces­sary because a many-to-one rela­tion­ship is not allowed when the join column is used as the primary key.

All column names spe­cified in the Para­meter Table will pop­u­late the Join Para­meter column, and the organ­isa­tion level to which the respect­ive column name cor­rel­ates will pop­u­late the Join Employee column:

Fig­ure 4: Bridge Table

Now that all the tables are in the model, let’s join them. As all the logic is based on employee data, the Employee Table fil­ters the Pro­jects Table, which acts as the fact table, based on EMP_ID_TX. At the same time, the Employee Table applies a fil­ter to the Bridge Table on ORG_LEVEL_NUM = Join Employee. Finally, the Bridge and Para­meter Tables are filtered in both dir­ec­tions on Join Para­meter = Parameter:

Fig­ure 5: Power BI Model

RLS will be con­figured in both the Bridge and Pro­jects Tables. In the Bridge Table, we will get the organ­isa­tion level from the user access­ing the sys­tem, which leads to fil­ter­ing the Para­meter Table:

CONTAINSTRING([Join Employee], LOOKUPVALUE('Employee'[ORG_LEVEL_TX], 'Employee'[EMAIL_TX], UPPER(userprincipalname())))

For the Pro­jects Table we will fil­ter each pro­ject where the user is part of the organ­isa­tion hier­archy path:

CONTAINSTRING([EMP_PATH_TX], LOOKUPVALUE('Employee'[EMP_ID_TX], 'Employee'[EMAIL_TX], UPPER(userprincipalname())))

Fig­ure 6: Secur­ity Bridge Table

Fig­ure 7: Secur­ity Pro­jects Table

The last step is to add data to the visual, which in this case will be a stacked column chart:

• X‑axis: ‘Para­meter’ [Para­meter].
• Y‑axis: Count of ‘Pro­jects’ [PROJECT_ID_TX] as # Pro­jects.
• Legend: ‘Pro­jects’ [PROJECT_STAGE] as Stage.

Res­ult

If Employee 1 is access­ing the data, who accord­ing to the Employee Table should see ORG_LEVEL2_TX, the first organ­isa­tion level in the bar chart X‑axis is Divi­sion. Since Employee 1 is part of both Divi­sions, all data is avail­able. Note that the drill-down fea­ture is enabled and will show all the depart­ments to which the user belongs in the X‑axis:

Fig­ure 8: First level view as User: Employee 1

Fig­ure 9: Second level view as User: Employee 1

If Employee 2 or Employee 3 access the data, they should, accord­ing to the Employee Table, view ORG_LEVEL3_TX. Depart­ment will be the default view. Fig­ures 10 and 11 illus­trate that Depart­ments 1, 2, and 3 are access­ible to Employee 2, while Depart­ments 4 and 5 are avail­able to Employee 3:

Fig­ure 10: Level view as User: Employee 2

Fig­ure 11: Level view as User: Employee 3

If the user is Employee 7, who should see ORG_LEVEL3_TX, Depart­ment will be the default view as in the pre­vi­ous example, but in this case only Depart­ment 4 will be visible:

Fig­ure 12: Level view as User: Employee 7

Con­clu­sion

This con­fig­ur­a­tion facil­it­ates the reuse of a single visu­al­isa­tion for diverse pur­poses in Power BI. Although in this instance we opted to modify only one axis, based on the organ­isa­tion level of the user access­ing the data, we could also alter the dimen­sion dis­played, where ‘dimen­sion’ refers to a column in a data­set. The abil­ity to present dif­fer­ent gran­u­lar­it­ies and dimen­sions within the same visual sim­pli­fies con­tex­tual under­stand­ing, as each user views data that is famil­iar to them.

For addi­tional cla­ri­fic­a­tion or sup­port, please don’t hes­it­ate to con­tact our con­sult­ants here at ClearPeaks. Our experts are ded­ic­ated to help­ing you har­ness the full poten­tial of your data visu­al­isa­tion tools, ensur­ing optimal results.

Article-Banner-Visualizations-synvert