Cannot filter in Analyze in Excel as a RLS user - Hybrid Table

[AM_BSL]

Hi 

We are facing the below issue -

An unexpected error occurred (file 'xmvsquery.cpp', line 11083, function XMVSMeasure Validate

We are creating a dataset where 1 table is hybrid table (Sales Table) it has relation with CompanyCodes which has relation with User table ( which contains info related to userid and the companies they have access to e.g. user Alex has access to Company 0010,0020,0030)

Dynamic RLS has been implemented as Email = Userprincipalname()

The datamodel is as below-

The end user Alex has viewer role and has read and build permission on dataset

Measure for Salesamount has been created as - Sum(SalesAmount)

While the end user analyze in excel - facing issue when Company Column from Sales table is dragged to  Filter pane of pivot table,

it works fine when multiple values are selected 

but when only one value is selected gives the below error -

We are using Microsoft SQL Server 2019

our full SQL version is

[jaweher899 · Accepted solution]

It looks like you are facing an issue with your Power BI dataset and the dynamic row-level security that you have implemented. The error message that you are seeing is related to the XMVSQuery component of the dataset.

To troubleshoot this issue, you can try the following steps:

1. Check the data type of the Company column in your Sales table. Make sure that it matches the data type of the CompanyCodes table's CompanyCode column. If there is a mismatch, you can try changing the data type of the Company column to match the CompanyCode column.

2. Check if the email address used in the dynamic RLS matches the user principal name of the end user. Make sure that the email address used in the RLS is the same as the email address used by the end user to log in to Power BI.

3. Check if there are any missing or incorrect values in the CompanyCodes table. Make sure that all the company codes used in the Sales table have a corresponding entry in the CompanyCodes table.

4. Try recreating the SalesAmount measure to see if it resolves the issue. You can also try creating a new measure using the DAX function SUMX to see if it resolves the issue.

5. If none of the above steps resolve the issue, you can try refreshing the dataset or re-importing the data into the dataset.