Row level Security (RLS)

  Hey There! 

In this article I will be explaining row level security in detail. 

Firstly, what is row level security? When you have a dataset where not all groups can view all the data, we need some kind of security to show only that data that's appropriate for viewing by each group. And we have to do it based on who has logged in. Since the security is applied at row level (only some rows of data are available for a certain group) they call it row level security.

Use case:

Report that shows the claims of the employees. But the Geo leads can only see the claims from their regions. 

Solution:

IN PBIX:

Setting up the data:

1. Load the data from the file shown above. 

2. Load another inline mapping table to map the geo leads with their respective geos. (alternatively, you can maintain this data in another excel file outside power bi. Advantage of this approach is that for changes with the geo leads, you dont have to open pbix and alter the file)

2.a. To load this geo leads table click on Enter data. 

2.b. Create a column for Geo and one for the leads. You can either give the lead email id or the name with domain like domain\username. But the next step will be dependent on what you give here. For my example I am taking emailid.

2.c. Click OK. Close and apply. 

3. Create relationship between the two tables with both as the direction.

4. Create a measure as follows in the claim sheet table

Currentuser = USERPRINCIPALNAME()

In step 2 if you had chosen to use name in the created table the measure has to be Currentuser = USERNAME()

5. Check if the value of current user is right by using this measure in a table. You must see the logged in user email id (we are taking Currentuser = USERPRINCIPALNAME() for this example) 

Managing Users:

1. Click on Modeling and Manage user

2. Click create and give a name for the user group: I am giving Geo Leads as the name. Click on the Mapping Table and type the DAX in the right pane as  

                    

Lead=[Currentuser]

This will filter the Lead column from the mapping table with the logged in user. thereby filtering the original claim data for the geo assocaited. 

3. Create another user group called "Super User" (name anything you want) and dont give any dax expression. This helps you manage a super user group, can be userself for data validation, who can view all regions without any filter. 

4. Test the roles by clicking on view as and giving the test email ids.

loaded data for reference:

click ok to see the following filtered result

Now that everything is working fine, publish the file. 

Cloud Service:

1. Navigate to Data sets +data flows. Click on the 3 dots near the data set associated with the file we just published. Select security.

2. Add people in their groups. For example the ones listed as geo leads in our table need to be listed undder geo leads for the RLS to work. 

3. To test it, click on the  three dots and test as a role. Enter the email id you want to test the RLS for. 

If this works, we have done all the steps right. 

Hope this article helped you. Please leave a thumbs up below if it did. :)

Loading multiple files from a folder

   Hey there! 

In this article I will show you how you can load multiple excel files from a folder. This is not incremental load as such but this approach picks up data as and when a new file is uploaded.

Exampe use case: 
A team generally reviews orders of the week in an excel, add comments and uploads in a sharepoint location. 
Files are named as "Book Week X" (Book Week 1, Book week 2)

Power bi requirement: To visualize the values in all these files and pick up new files that are being uploaded every week. 

Solution:

Here I have filtered the files that start with the word "Book". 

If you are not sure how to use advanced editor to do this:

1. Click the filter drop down for column named "Name".

2. Select one of the file names

3. You will have a Filtered Rows step generated at the right pane under "Applied steps"

4. Click on it and look at the formula bar.  Say it has : [Name]="Book1.xlsx". Edit it to Text.Contains formula shown below.

Click on combine and select combine files from the drop down. 

Now select one sample file, load the right sheet. The same sheet will be loaded from all files existing in the folder (that were the result of the previous filter step). As the dataset is refreshed, the new files that match the filter query (that is the files in the folder with the word "Book"), the sheet 1 of these files will be loaded. 

A few steps will get auto added. Click on the unique identifier column and remove duplicates to avoid duplication due to uploading same file twice by mistake in the folder.

The final output will have a column Source.Name auto added with the file from which the row is added. 

Let me know in comments if this article was useful.