Alternative to USERELATIONSHIP
From time to time you want to add RLS to your Power BI model. If you do, then the filtering option USERELATIONSHIP isn’t supported.
Another scenario is that you filter on a date and you want to show things that has anything to do with that date, not just the primary relationship connection.
OK so let’s visualize what I mean to better understand the issue here.
I have 2 tables:
- dUsers
- dCalendar
- fOpportunities
They look like this:
My calendar table is just a table with dates in a column.
The Issue
You can only have 1 active relationship between 2 tables. Power BI wouldn’t be able to know what to visualize otherwise. In my example there are multiple ways the table connect though! You can see I’m able to create the relationships either way, but only 1 between 2 tables will have a filled line, representing an active relationship. The other ones are called inactive relationships. They exist, but don’t really do anything at the moment.
I’ve set the active relationship between the user table and the opportunity table based on the user ID being the Owner of the opportunity.
If we create a measure now, like this, it would reflect the opportunities owned by the user.
We could then make other measures that activates the other relationships! It would look something like this:
I’m using the same expression as before but this time I tell the model to make use of the otherwise inactive relationship with the USERELATIONSHIP-filter. I need to specify what inactive relationship to activate so I specify that User ID goes to Created by.
I can now show the same opportunities as before over my user table and it looks like this:
The problem is that RLS is a rule set that will go through the tables and evaluate its expression over each row to determine if that record should be part of the users dataset or not. A measure is calculated in its context, so how do the model know if a certain record should remain in the dataset or not?
I’m setting up a role called self that looks like this:
I then want to test this role:
Oh no! Disaster! My list of opportunities by user no longer works.
Let’s review what’s going on here. When applying RLS the way I just did, the user table will only have 1 row:
This row is connected with an active relationship to the owner of opportunities. This means that my opportunity table only have rows where my user ID is the owner now.
Apart from the fact that I don’t want broken visuals in my report when applying RLS, I also want a user to see opportunities regardless of their involvement with it. One solution to this would be to create a copy of the facts table of opportunities for each relationship I have but I would like to offer you a different solution here!
The solution
Just like a slowly changing dimension table, you can use an unpivoted table in the middle between the dimension and facts! This method means you only make use of 1 relationship in the model and you use a field to determine what kind of relationship we’re currently looking at. This also simplifies the situation if we have many, many different relationships or if we want to combine a bunch of relationships and say “Show me the opportunities of this table given that either of these 4 attributes have anything to do with it”.
If we’re doing this in Power Query, we can either reference the facts table and create this middle table (in this case, the date columns remain in the facts too) or we could make a duplication, fetching the data twice from the some table.
Referencing the table
In the new table, select all the fields with a User reference as well as the ID Key column. Remove the rest.
Unpivot all other columns on the ID, like this:
Finally, go into the formula field and change the column names. Instead of Attribute and Value, I’d name them “Relationship Type” and “User ID”.
Finally I call the query something that makes sense, like “fOpportunitiesUserRelate”
Now look at what the relationship looked like before and after:
It’s important that you set the relationship both ways and if you’re going to use RLS, make sure to check this checkbox!
Now instead of making measures that use USERELATIONSHIP, the measure simply use “Filter” to determine the relationship. Here’s the old and new measure:
This time, if I activate the RLS rule again, I can see any row in Opportunities as long as my user had anything to do with it!
At this point, you might want to set up a calculation group as well. I’ve created a measure for each relationship, which is what I would’ve had to do regardless if I was using the USERELATIONSHIP-feature.
This is useful because I’m now able to show the values for the same user on the same table!
Note how the first one says 7. That’s unfiltered! This is useful because unless you are looking into comparing the same values from a facts table in the same table over dimensions, you can simplify by using visual filtering levels!
There’s also another advantage. If you want to visualize the matrix I did above and you don’t want to use lots of measure, you can just use the relationship type as columns in a matrix!
Another advantage is that you can let your users decide on what they want to filter on. By adding the relationship type as a slicer to the page, the user of the report can now decide what type of relationship they want to have with the opportunity for it to show:
I have 7 in total like this:
I’m the owner of 4:
I closed 3
And if I select both closed and owned, I should have 7, right?
Wrong. I get 6. 4 + 3 is still 7, don’t worry! It’s just that I’m actually both the owner as well as the one who closed one of the opportunities, so it’s in both selections. Can you spot the one?
And for my final trick! Let’s say you don’t want all the relationship types. You’re uninterested in who modified an opportunity, they shouldn’t show in the report for a user. Well one way is to remove the column along with the other in the referenced query above. If you, however, want to give some users the ability to filter on this but the RLS showing your own data should not include it, you can alter the RLS code like this:
The role now need to match the Username in the user table as well as the relationship type must not be “Modified by”.
In my report, the option for modified have been removed!
As always, if you have any kind of questions feel free to reach out using either the comment section below or find me on LinkedIn!
Cheers!
About The Author
