top of page
  • Writer's pictureAntriksh Sharma

ALL Functions vs Security in DAX

One thing that we are used to is ALL functions ignore filters and return either a unique value or complete table when used as a table function, and when used as a CALCULATE modifier they remove filter from the Column/Table or Expanded tables and we might think that this should hold true in every scenario.


However, when security is applied the same concept won't hold true. Meaning if you have a RLS that filters for Year = 2023 and you have a Measure that returns sales for ALL years (2020, 2021, 2022, 2023) then you won't get grand total sales for the years before 2023.


The reason being Security is activated when you connect to the report and dataset. The DAX code of a Measure is evaluated when Security is already there, that means the Dates table is already filtered for Year = 2023 so ALL can't return values that don't exist. The same doesn't happen for Calculated Tables and Calculated Columns as these two are processed when Security isn't available, that's why they can be potential source of security issues.


If we test a query that computes Sales Amount by Removing filter over the year, we only get Year = 2023 and the grand total sales is also the sales amount of that year and not other years.

To process a DAX Query the engine applies the RLS filter in each query that it sends to the PowerBI's internal columnar database called VertiPaq.

Therefore even if you think the code should work fine, the Security might cause issues. The reason is simple, Security filters are more restrictive than the DAX code!

313 views0 comments

Commentaires


bottom of page