Filter Dimension in Tabular model

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
• [email protected] is allowed to view Legal Company XY, and [email protected] is allowed to view all Legal Companies.

As an example, when [email protected] 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:

VAR CurrentUser = USERPRINCIPALNAME ()
-- Get a list of companies for current user from RLS table
VAR CurrentUserCompanyKey =
    CALCULATETABLE (
        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 =
    CALCULATE (
        NOT ISEMPTY ( FactTable ),
        TREATAS (
            CurrentUserCompanyKey,
            'Legal Company'[Legal Company Key]
        )
    )
RETURN
    CurrentCustomerRequired

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)?

Regards,

Owen