Create DAX Filter Expression for RLS seup with Multiple Parameters and Multiple Rows per user

[Anonymous]

RLS with multiple parameters (Regions, Cost centers, Countries, Currencies, User profiles) and multiple values also assigned for each category/parameter. In that case I need to write a DAX expression which ensures that the user can see the data that matches any of the regions, any of cost centers, any of countries, any of currencies, and any of user profiles associated with them.

For Example, For User : User1@xyz.com    

Assigned Values:

• Regions: "East", "West“, “North”
• Cost Centers: "CC0001", "CC0002"
• Countries: "USA", "Canada” , “Mexico”
• Currencies: "USD", "CAD”, “MXN”
• User Profile: “President“, “Analyst”

In this case ,user1@xyz.com will only see  data for the East, West and North Regions, CC0001, CC0002 Cost Centers , USA, Canada and Mexico Countries, USD,CAD and MXN Currencies and with the President and “Analyst” user Profiles.

Below is my DAX expression that filters Fact tables based on the parameters from the User table.

VAR currentUser = USERNAME()

VAR userRegion = FILTER(UserTable, UserTable[Username] = currentUser)

VAR userCostCenter = FILTER(UserTable, UserTable[Username] = currentUser)

VAR userCountry = FILTER(UserTable, UserTable[Username] = currentUser)

VAR userCurrency = FILTER(UserTable, UserTable[Username] = currentUser)

VAR userProfile = FILTER(UserTable, UserTable[Username] = currentUser)

RETURN   

--- Filter data where Region, CostCenter, Country, Currency, or UserProfile matches the user’s associations

   COUNTROWS(

       FILTER(Account,

           Account[Region] IN VALUES(UserRegion[Region]) &&

           Account[CostCenter] IN VALUES(UserCostCenter[CostCenter]) &&

           Account[Country] IN VALUES(UserCountry[Country]) &&

           Account[Currency] IN VALUES(UserCurrency[Currency]) &&

           Account[Profile] IN VALUES(UserProfile[Profile])

       )

   )

> 0

But this DAX is not working for me. How to store multiple values to one variable and execute the DAX expression to apply the filter correctly. Need help here. 

TIA,

Tanisha

[Anonymous]

Hi @Anonymous ,

Thanks for reaching out to the Microsoft Fabric Community.

As correctly suggested by @bhanu_gautam , the use of CALCULATETABLE(VALUES(...), ...) to retrieve the list of permitted values per user is the right approach when implementing RLS with multiple values across multiple dimensions.

This pattern ensures that the RLS rule correctly evaluates all records where each of the attributes — Region, Cost Center, Country, Currency, and User Profile — matches any of the values assigned to the user.

If you’re still experiencing issues:

• Please confirm that your UserTable contains a separate row for each unique [Username] and parameter value combination.
• Verify that the data types between the Account table and the UserTable columns match exactly (e.g., no mismatched text vs. numeric types).
• Ensure there are no unexpected blank or null values interfering with filtering.

If the above conditions are met and the issue still persists, we can consider alternative setups, such as building many-to-many relationships via bridge tables or using TREATAS for more complex scenarios.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[bhanu_gautam · Accepted solution]

@Anonymous , Try using

VAR currentUser = USERNAME()

VAR userRegion = CALCULATETABLE(VALUES(UserTable[Region]), UserTable[Username] = currentUser)
VAR userCostCenter = CALCULATETABLE(VALUES(UserTable[CostCenter]), UserTable[Username] = currentUser)
VAR userCountry = CALCULATETABLE(VALUES(UserTable[Country]), UserTable[Username] = currentUser)
VAR userCurrency = CALCULATETABLE(VALUES(UserTable[Currency]), UserTable[Username] = currentUser)
VAR userProfile = CALCULATETABLE(VALUES(UserTable[Profile]), UserTable[Username] = currentUser)

RETURN
COUNTROWS(
FILTER(
Account,
Account[Region] IN userRegion &&
Account[CostCenter] IN userCostCenter &&
Account[Country] IN userCountry &&
Account[Currency] IN userCurrency &&
Account[Profile] IN userProfile
)
) > 0

[Anonymous]

Hi @bhanu_gautam @Anonymous It's all working now. I already incorporated CALCULATETABLE for this scenario late yesterday. But Issue was with my join condition.Thank you so much for your help and suggestions. Appreciate for your quick reply.

Thanks,

Tanisha