Forum Discussion
Helper IIRLS using
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 😉
Memorable MemberIn 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.