Filter Dimension in Tabular model

[SteenSP]

Hi,

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.

Regards

Steen 

[OwenAuger]

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
• userXY@contoso.com is allowed to view Legal Company XY, and ceo@contoso.com is allowed to view all Legal Companies.

As an example, when user01@contoso.com 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