I have been wanting to start writing my own blog posts for around a year now in order to share my knowledge and experience – and I have finally gotten around to writing my first blog post! In this blog post, I will be discussing a question that was posted on the Power BI Community forum. If you are not aware of what the Power BI Community forum is, I highly recommend that you check it out at community.powerbi.com. It is a great place to learn from others, ask your own questions, learn about best practices, and hone your skills in Power BI.

Single Select Slicer

Single select is an option in the slicer settings that allows the user to select only one value in a slicer. Once “Single select” is turned on, the user must always have one default value selected in the slicer – you cannot have no values selected in the slicer.

To turn a slicer into “Single select,” go to the “Format” tab and toggle “Single select” to “on” (as depicted in the image below).

So, what are the issues when using a Single select slicer with RLS? Here is the problem that a user posted on the Power BI Community forum:

I have a report based on RLS with two slicers one Department  and second Jobs. Users can see only jobs which they belong to theirs department. Everything works just fine (for example user A can see jobs belong to Dept 10 and user B can see jobs belong to dept 11) The problem starts when I modify the Job slicer to single select for example if I publish the report I need to select at least one job which belong to department that only users with RLS can see. For example, I published a report with Department = ALL and Job 10 When user B try to browse the report he / she see an empty report because theirs department is not on theirs RLS list.

My question – how do I reset the single select slicer so on the first time user can see theirs department and jobs? 

Here is the link to the post on community forum.

One important thing that I want to mention is that I will not be explaining Row Level Security in this blog post. If you want more information regarding RLS, I will share some resources at the end of this post that explain what it is and how you can set it up.

Now let’s discuss the solution that I came up with. First, let’s look at the very simple data model. There is one table called Investment, containing a “Team” column and some other number columns, and one table called Users, containing a “User” column along with which “Team” data they can see.

The image below depicts the data model:

Note: We don’t want to set any relationship between these two tables.

Below is what our Users table looks like – we have a special row in this table where both the “Team” and the “User” value is ALL. This row will later be used for our solutions, but for now it is important to know that there is a special row in our Users table.

First, we are going to add a simple measure to identify our special row (ALL) in the Users table:

ALL Team = IF ( MAX ( Users[Team] ) = "ALL", 1, 0  )

The next step is to add a role for RLS that identifies which users can see which teams. This utilizes the logic that every user can see the ALL row. There are seven steps to add this simple RLS filter:

1. Go to “Modelling” tab
2. Click “Manage roles”
3. Click “Create”
4. Enter role name
5. Select Users table
6. Enter role formula
7. Click “Save”

Once we have created our role for RLS, we can quickly test it by dropping a slicer visual onto the canvas and adding the “Team” column from the Users table onto the slicer. To test a role, click “View as” on the “Modelling” tab and enter the user name. It will list only those teams which the selected user can see.

An important point to note here is that you will always see an “ALL” option in the slicer – it doesn’t matter which user is currently looking at the data.

Now in the next step we are going to filter our Investment table based on the current user. To achieve this, we will create a measure that will show only those teams which the currently selected user can see.

Filter Team =
VAR __isAllTeamSelected = [ALL Team]
RETURN
CALCULATE (
    COUNTROWS( Investments ),
    KEEPFILTERS(
        TREATAS (
            SUMMARIZE (
                FILTER(
                    ALL ( Users[Team] ),
                    IF ( __isAllTeamSelected = 1, TRUE(), Users[Team] = MAX ( Users[Team] ) )
                ),
                Users[Team]
            ),
            Investments[Team]
        )
    )
) + 0

Let’s look what above measure is going to return. It is going to give us count of rows from Investment table for only those teams which a user can see based on Users table, and if a user cannot see a team, above measure will return a Zero value and in case “ALL” value is selected we will get row count for each team a user can see.

If ALL value selected in the slicer

If single team selected in a slicer, only that team shows row count, other teams has Zero value.

We will use this measure as visual level filter – see screen shot below to exclude teams which we don’t want to show, or in other words only show selected team, and case of “ALL”  we will see all the teams of the user.

So how does this fit in our original problem.  Didn’t I mention that special row with team “ALL” is important as this row will be always available regardless what user is viewing the data. In our “single select” slicer we will select “ALL” as default selection and regardless what user is looking at the data, they will always see their teams with “ALL” as default selected team.

Power BI file is attached with above solution. As I’m always learning, looking forward to your valuable feedback or share how you will solve it.

Useful Links

Learn more about RLS here.
Link to USERNAME() and TREATAS function