Need help with DAX expression on RLS

[JustinDoh1]

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.

[v-pnaroju-msft · Accepted solution]

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.

1. 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.

[JustinDoh1]

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

[v-pnaroju-msft]

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.

[JustinDoh1]

@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.

[v-pnaroju-msft]

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.

[JustinDoh1]

@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.