topic Re: Need help with DAX expression on RLS in DAX Commands and Tips

Need help with DAX expression on RLS

JustinDoh1 — Thu, 03 Apr 2025 21:46:49 GMT

I have attached PBIX file here.

I am trying to modify the bottom DAX so that it meets the condition of AccessToAllLocations = 'Yes' as I intended.

Bottom is current DAX that I have for RLS:

VAR _Role = MAXX(

FILTER( 'tblPBIGroup', 'tblPBIGroup'[User] = USERPRINCIPALNAME() ),

'tblPBIGroup'[AccessToAllLocations]

)

RETURN IF

( _Role = "Yes", TRUE(),

[User] = USERPRINCIPALNAME()

)

I have two tables that are related to RLS:

1. 'tblPBISecurity' - this has all locations listed.

2. Second table is 'tblPBIGroup', which indicates that "AccessToAllLocations" is either 'Yes' or 'No'.

The issue that I am having is when "AccessToAllLocations" is 'Yes', it should allow access to both locations 'ART' and 'CAL', but it only allows access to the location that is/are listed on tblPBIGroup (so in this case, it would only allow access to 'CAL' even though "AccessToAllLocations" is 'Yes').

Bottom is expectation of results (indicated on inside screenshot):

How can it be achievable?

Thanks for help.

Re: Need help with DAX expression on RLS

v-pnaroju-msft — Fri, 04 Apr 2025 05:28:51 GMT

Hi JustinDoh1,

Thank you for reaching out to the Microsoft Fabric Community Forum.

Please find the revised DAX expression to be applied to the tblPBISecurity table for Row-Level Security (RLS). Kindly check if it resolves the issue:

VAR _User = USERPRINCIPALNAME()

VAR _AccessToAll = MAXX(

FILTER( 'tblPBIGroup', 'tblPBIGroup'[User] = _User ),

'tblPBIGroup'[AccessToAllLocations]

)

VAR _UserLocation = MAXX(

FILTER( 'tblPBIGroup', 'tblPBIGroup'[User] = _User ),

'tblPBIGroup'[Location]

)

RETURN

IF(

_AccessToAll = "Yes",

TRUE(),

'tblPBISecurity'[Group] = _UserLocation

)

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Thank you.

Re: Need help with DAX expression on RLS

JustinDoh1 — Fri, 04 Apr 2025 17:20:19 GMT

v-pnaroju-msft

Thank you so much for your help.

I am having an error recognizing a table value ('tblPBISecurity'[Group]) because, I think, the DAX is currently pointing to tblPBIGroup on the expression Window on the right side.

How do we fix this issue?

Thanks.

Re: Need help with DAX expression on RLS

v-pnaroju-msft — Sat, 05 Apr 2025 08:38:29 GMT

Hi JustinDoh1,

As mentioned earlier, please apply the DAX expression to the tblPBISecurity table for implementing Row-Level Security (RLS), and verify if it resolves the issue.

If you find our response helpful, kindly mark it as the accepted solution and consider giving kudos. This will be beneficial for other community members who may have similar queries.

Thank you.

Re: Need help with DAX expression on RLS

JustinDoh1 — Mon, 07 Apr 2025 22:45:55 GMT

v-pnaroju-msft

I am getting this error:

Do I need to unclick this (Apply security filter in both directions), then?

Sorry.

I also have two more questions:

1. If I want multiple locations to be in the condition for _UserLocation, how do I modify the MAXX with?

2. On bottom DAX. What does 'TRUE()' mean?

IF(

_AccessToAll = "Yes",

TRUE(),

'tblPBISecurity'[Group] = _UserLocation

)

Thanks.

Re: Need help with DAX expression on RLS

v-pnaroju-msft — Tue, 08 Apr 2025 11:42:04 GMT

Hi JustinDoh1,

Based on my understanding, the Security Cross Filtering error arises due to the Apply security filter in both directions setting. Kindly uncheck this option in the relationship between tblPBIGroup and tblPBISecurity, and set the direction to Single from tblPBIGroup to tblPBISecurity. Then, reapply the DAX expression to tblPBISecurity. This should help resolve the issue.

For handling multiple locations, please use the updated DAX expression below:
VAR _User = USERPRINCIPALNAME()

VAR _AccessToAll = MAXX(FILTER('tblPBIGroup', 'tblPBIGroup'[User] = _User), 'tblPBIGroup'[AccessToAllLocations])

VAR _UserLocations = FILTER('tblPBIGroup', 'tblPBIGroup'[User] = _User)

RETURN

IF(

_AccessToAll = "Yes",

TRUE(),

'tblPBISecurity'[Group] IN SELECTCOLUMNS(_UserLocations, "Location", 'tblPBIGroup'[Location])

)

This logic allows users to access multiple locations as listed in the tblPBIGroup table.

2.The TRUE() function grants unrestricted access to all rows in tblPBISecurity when AccessToAllLocations is set to Yes. Otherwise, it restricts access based on the specific locations.

If you find this response helpful, kindly mark it as the accepted solution and consider giving kudos. This will be beneficial for other community members facing similar queries.

Thank you.

Re: Need help with DAX expression on RLS

JustinDoh1 — Tue, 08 Apr 2025 17:19:21 GMT

v-pnaroju-msft Thank you so much for all your help.