Static RLS not showing data in Power BI Service

[DeanUW]

I have implemented a static RLS based on office key on a dimension table of a star schema.  The RLS works perfectly in Power BI desktop when I "View as".  The RLS works perfectly fine when I "Test as Role" in the dataset security setting in Power BI service.  However, end users that are impacted by the RLS recieve the following error:

"The query encountered security filters on a table relationship that cannot be reconciled for the current user. Please contact..."

I have changed the RLS multiple time, moving to the different dimension tables that have the appropriate office name or office key.  I have also put the RLS directly on the Fact table & still recieve the same error.  

The error began when I published a new version of a previous report, replacing the old version that had basically the same type of RLS in place filtering the data based on office name.  I rebuilt the permissions lists for the new report but not having any luck getting the RSL to show anything.

Current Desktop Software Version: 2.107.683.0 64-bit (July 2022)

Any hep would be appreciated!

**UPDATE**  After additional testing the users impacted are any users that have their names added to multiple locations in the dataset security setting in Power BI service.  If I limit the user to see only one location the data appears but if they are supposed to see multiple offices based on their position the error appears.  Was under the impression that the new July update added multi RLS capabilities.

[GilbertQ]

Hi @DeanUW 

As far as I am aware this is not possible and you need to have the users in a single role.

The update was for Power BI Composite models to the Mashup connectors include Snowflake, RedShift, Databricks, and others.

Power BI July 2022 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

[DeanUW]

Hello GilbertQ

Thanks for the response.  Below is a quote from the July 2022 Feature Summary you attached to your response to me:

"We’ve also made a major improvement to composite models this month by enabling support for multi-role RLS. Model owners can now assign a single user to more than one RLS role in a composite model. Prior to this release, users who set up RLS in their composite models this way would likely hit a query failure."

Seems to me the feature isn't working as promised.

[GilbertQ]

Hi @DeanUW 

As per the link this is only for Composite models who connect to data sources OUTSIDE of the Power BI Service.

[DeanUW]

Apologies I am still somewhat new to Power BI.  By my understanding the report in question is a composite model; as it has multiple many-to-many relationships within the tables and it has mutliple sources including ODBC imports from different databases and Excel spreadsheets.  As always, I appreciate the responses & the support!

[GilbertQ]

Hi @DeanUW 

No worries for the questions, that is how we all learn.

A composite model is where you have an imported Power BI Dataset, with another dataset from another underlying system which is then added to the Power BI dataset.

In your example you have a single Power BI dataset which has got imported data from ODBC and Excel spreadsheets. This is called a Power BI Dataset. And it is not a composite model.

I hope that makes sense.

[DeanUW · Accepted solution]

Hi GilbertQ,

That explination made total sense, thank you again.  I was able to resolve the issue by modifying the DAX using the double pipe ||.  Allowing me to add the additional office locations to a single RLS role, then assigning the appropriate people to that single RLS role.

Example:  [Office Name] = "XXXX" || [Office Name] = "YYYY"

Hope this may help others!  Thank you again!