RLS filter with multiple values assigned?

Question

I need to filter my Power BI report by the App IDs associated for the current user (using the USERPRINCIPALNAME function). So I have three tables in my model, DimApp, DimUser and FactRegisters, where a User_Id may be related to 1 or more App_Ids in my Fact table.

DimApp table

DimUser table

FactRegister table

As you can see in FactRegisters table there are two App_Ids (3 and 1) for User_Id 201. The following is the DAX rule defined in App_Id column from DimApp table to filter the data:

VAR userId =
LOOKUPVALUE (
    DimUser[User_Id],
    DimUser[Email], USERPRINCIPALNAME()
)
VAR app = 
LOOKUPVALUE ( 
    FactRegisters[Application_Id],
    FactRegisters[User_Id], userId
)
RETURN DimApplication[Application_Id] IN {app}

Verifying the DAX expression doesn't return an error, however, when I choose to "View as" that role I'm not able to see the data in the visuals. The error states: "Couldn't load the data for this visual. An error was encountered during the evaluation of the row level security expression defined on table DimApp. A table of multiple values was supplied where a single value was expected."

Cannot display the visual viewing as role

However, when a single App_Id is associated to the User_Id, I'm able to visualize the data on the report visuals using the same DAX rule. Here is how FactRegisters table looks like when User_Id 201 has a single App_Id (3) associated:

FactRegisters table when User_Id with single App_Id

User_Id with a single App_Id visual

Now I'm I able to visualize data in the report. This is not a suitable case scenario as a User_Id can have many App_Ids.

I also tried the following static DAX rule in my App_Id column from DimApp just to test and pass multiple values to that column, and I succeed in visualizing data for multiple App_Ids:

DimApplication[Application_Id] IN {1,3}

Static RLS with multiple values by App_Id column

But this is not the goal (not is dynamic). The goal is to visualize the data from all the Apps associated to the current user. Is it possible? Can't I pass more than one value to a column while filtering in RLS?

RLS filter with multiple values assigned?

0 Answers