RLS using

[Katerina_SL]

Hello Everyone!

Could someone please help me with such issue:

I have a Power BI report that tracks Salesman Performance, including KPIs such as profit, number of orders, number of clients by status, and other metrics.

For each KPI, I also created an additional measure that displays the same KPI for all other salesmen (e.g., the average value excluding the currently selected salesman).

The report is built around a User table that is related to the other tables in the data model and is used to retrieve the required information.

After publishing the report to a workspace, I configured Row-Level Security (RLS) based on user_id. The security works correctly when filtering data for a single salesman. However, all of the "Other Salesmen" KPI measures return blank values.

I understand that this happens because RLS filters out all other salesmen, but I'm looking for a way to preserve the security model while still being able to calculate averages or KPIs across the other salesmen.

How can I achieve this in Power BI?

Thank you for any advice,
Katarina

[v-hjannapu]

Hi @Katerina_SL,

I would also take a moment to thank @Gabry  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @Katerina_SL,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[oussamahaimoud]

In Power Query, duplicate your User table and name it something like DimSalesmanRef. Do not create any relationship between this table and the rest of the model. It exists purely as a DAX filter vehicle.

Rewrite your "Other Salesmen" measures

Profit Others Avg =

VAR CurrentUser =

    SELECTEDVALUE( DimUser[user_id] )

VAR Result =

    CALCULATE(

        AVERAGEX(

            FILTER(

                ALL( DimUser[user_id] ),

                DimUser[user_id] <> CurrentUser

            ),

            [Profit]

        )

    )

RETURN Result

If your RLS is email-based rather than user_id-based, use:

VAR CurrentUser =

    CALCULATE(

        SELECTEDVALUE( DimUser[user_id] ),

        FILTER( DimUser, DimUser[email] = USERPRINCIPALNAME() )

    )

This makes the "current user" lookup immune to slicer context, which matters if you ever expose a user selector to managers.

Rememer that the FILTER( ALL( DimUser[user_id] ), ... ) pattern is the go-to solution, it gives you a full scan of all salesmen for the "others" calculation while leaving the RLS filter intact everywhere else in the report.

[Gabry · Accepted solution]

Hello @Katerina_SL ,

I think one solution could be to create a pre-aggregated table that contains the global totals but does not contain the user_id or salesman_id or anything related to user / salesman. So, because the user ID is missing, you won't apply RLS to this table, allowing everyone to see the global totals . It's better if you do this using power query. Of course then you should build the total measures on this pre-aggregated table
Hope this helps you 😉