Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Filter Dimension in Tabular model



I know this isn't strictly Power BI, but maybe someone in here are able to help.


I have a number of Tabular models, where I'd like to restrict which dimension values a user can see. I already have Dynamic RowLevel Security defined to restrict the Fact table, but since it's a "global" Tabular model that contains data for several companies, I'd like to "filter out" the dimension values for the user so he don't have to see data for other companies than his own.

 The DRLS is created ,so it matches the userID in a table, and this then filters a Legal Company table which then have a relationship to the Fact table. That part works fine and the user only see fact records from his own legal company.

I'd then like to so something similar with the dimension data, but since I can't create relationships from the Legel Company table to the dimension tables, then I was thinking about doing it in the Security Role. I was thinking about a rule looking at the Legal Company for the dimension  and then "filtering" based on the data from the Legal Company table that only contains the legal company the user are allowed to see.


I'm not that experienced in DAX yet, and I can't really figure out how to do it? I have tried various scripts with no luck, so I'd be happy with some idea and guidance.




Super User
Super User

Hi Steen,


This is an interesting one 🙂

Just restating the situation, to make sure I have understood things correctly:

  • You already have Dynamic Row-Level Security defined, so that the 'Legal Company' dimension table is filtered based on the current user.
  • This, in turn, filters the fact table due to the 1:many relationship from 'Legal Company' to fact table.
  • However, the RLS filtering currently has no effect on other dimension tables. This means that a particular user may see values in other dimension tables that do not correspond to visible rows of the RLS-filtered fact table.
  • You would like to filter those other dimension tables so that rows are only visible if they correspond to visible rows of the fact table.

Hopefully the above is correct! 🙂


There are no doubt a few ways to approach this. I have attached a PBIX showing one example of how the RLS filter expressions could be set up.


In my constructed example, I have Legal Company 01 to Legal Company 10.

For each XY = 01..10:

  • Legal Company XY relates only to Product XY and Customer XY
  • is allowed to view Legal Company XY, and is allowed to view all Legal Companies.

As an example, when accesses the dataset, only Legal Company 01 (and related fact rows) are visible, as well as only Customer 01 and Product 01.



To achieve this, the logic I have applied in the RLS filter expressions for the other dimension tables (Customer and Product in my case) is:

  1. Get a list of 'Legal Company' values that should be visible for the current user.
  2. For a given row of the dimension table, determine if the fact table is non-empty when filtered by that particular dimension row AND with the current user's 'Legal Company' filter applied.
  3. If the fact table is non-empty with these filters, then make that row of the dimension table visible.

For example the RLS filter expression for the Customer table is:



-- Get a list of companies for current user from RLS table
VAR CurrentUserCompanyKey =
        VALUES ( RLS[Legal Company Key] ),
        RLS[Email] = CurrentUser

-- Check if FactTable nonempty when applying filter on current Customer row
-- plus RLS filter on Legal Company
VAR CurrentCustomerRequired =
        NOT ISEMPTY ( FactTable ),
        TREATAS (
            'Legal Company'[Legal Company Key]



In your tabular model, the way that you determine CurentUserCompanyKey may well be different (and will be based on the 'Legal Company' RLS expression), but the code after that could be pretty similar.


Does something like this work at your end (I'm assuming you're working with Azure Analysis Services or SQL Server Analysis Services Tabular)?




Owen Auger
Did I answer your question? Mark my post as a solution!

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors