Dynamic RLS combination

[Sandy_22]

Hi everyone,
Need your help!

I am designing a report where users must be restricted across three dimensions: Study, Sub-Category, and Country.

My security permissions come from two separate database tables linked by a common study key:
user country table(Contains: User Email, study key, country key)
sub category table (Contains: User Email, study key, sub category key)

Initially, I applied RLS directly to my dimension tables and letting those filters flow down to my Fact tables as a star schema model.
However, because the security rules are split across two tables, and evaluates them independently. It ends up cross-joining the permitted values, creating a combination leak.

Example :

A user is explicitly allowed to see only Study 1 + Country A AND Study 2 + Country B.
Because the dimensions filter independently, the report mistakenly allows them to see Study 1 + Country B (an unauthorized combination).

After searching for so long, I got only 2 options:

1. To use bridge table between 2 security table and fact tables with bi directional filters. For 2 security tables, that would be 16 relationship with 8 bi-directional relationship between security table and bridge table.
2. Another option is to implement the RLS directly on fact table as it has all the combinations available.
Please suggest me with right option or any other options to implement this.

Note: we have split the security table because of large data.

Thanks in Advance!

[v-anbandari]

Hi @Sandy_22,

Just checking in again on your issue. Could you please confirm if the solution worked?

Please let us know if you need any further assistance. Sharing an update can really help others facing the same thing.

Thank you.

[v-anbandari]

Hi @Sandy_22 ,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @oussamahaimoud for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community

[oussamahaimoud · Accepted solution]

Hi @Sandy_22,

Go with Option 2. It's the only approach that correctly enforces combination-level security without model complexity or filter ambiguity.

Your Fact table naturally holds the exact valid combinations of Study + Country + Sub-Category. This is precisely what you want to enforce. You're not inferring permissions from two independent dimensions, you're directly validating each row against what the user is explicitly allowed to see.

The DAX for your RLS filter on the Fact table would look like this:

VAR __UserEmail = USERPRINCIPALNAME()

VAR __AllowedCountries =

    CALCULATETABLE(

        VALUES( UserCountryTable[StudyKey] ),

        UserCountryTable[UserEmail] = __UserEmail

    )

RETURN

    CONTAINS(

        CALCULATETABLE(

            SELECTCOLUMNS(

                UserCountryTable,

                "sk", UserCountryTable[StudyKey],

                "ck", UserCountryTable[CountryKey]

            ),

            UserCountryTable[UserEmail] = __UserEmail

        ),

        [StudyKey], FactTable[StudyKey],

        [CountryKey], FactTable[CountryKey]

    )

    &&

    CONTAINS(

        CALCULATETABLE(

            SELECTCOLUMNS(

                SubCategoryTable,

                "sk", SubCategoryTable[StudyKey],

                "sck", SubCategoryTable[SubCategoryKey]

            ),

            SubCategoryTable[UserEmail] = __UserEmail

        ),

        [StudyKey], FactTable[StudyKey],

        [SubCategoryKey], FactTable[SubCategoryKey]

    )

This enforces Study+Country as a pair from the first table, and Study+SubCategory as a pair from the second, exactly matching your permission model, with no cross-join leakage.

You mentioned the tables were split because of large data. The CONTAINS approach filters per-user at query time, so only the current user's rows from both security tables are evaluated, it doesn't scan the full tables. Performance remains acceptable in practice, especially if both security tables have UserEmail indexed or in a relationship that Power BI can leverage.

If performance becomes an issue at scale, the right optimization is a pre-joined security view at the database level (Study + Country + SubCategory per user, in a single flat table), which you then use as a single RLS source on the Fact table. This keeps the model clean without any bidirectional relationship complexity.

Assisted by AI for clarty of wording!