Insights / RLS in Power BI / Part 11
Part 11 of 20 · Build itWhy % of total changes per user with RLS
The problem. Your "% of total" measure means something different for every user.
% of Total =
DIVIDE ( [Sales], CALCULATE ( [Sales], ALL ( DimRegion ) ) )
North, South and East each sell 100. An admin sees North = 100 / 300 = 33%. A North user under RLS sees North = 100 / 100 = 100%.
Why. ALL, ALLEXCEPT and REMOVEFILTERS clear filters from slicers, visuals and rows, but they can't remove the RLS filter. So ALL(DimRegion) only gets back to the rows that user is allowed to see.
It's not a leak and it's not a bug. It's a security feature: you can't use ALL() to peek at hidden rows. But "% of total" has quietly become "% of what I'm allowed to see".
The fix.
- Label the measure clearly, for example "% of your regions".
- If restricted users really need a company-wide benchmark, build a small summary table without the secured column, and decide on purpose to show it. That's a disclosure decision, not a DAX trick.
- Test with View as, and check the denominator.
Source: SQLBI, "Computing accurate percentages with row-level security in Power BI".
Check before go-live
- Measures with ALL/REMOVEFILTERS were tested as a restricted user Desktop
- Any company-wide benchmark shown to restricted users was a deliberate decision
Want a second pair of eyes on your own model? The RLS review checks your roles, relationships and workspace access against this checklist, at a fixed price.