Forum Discussion
Helper IIICompare two measures without filtering one
SimonSeez - The way I have done this in the past is to create an aggregation table that does not interact with RLS. So, for example, let's say that you have a table called 'Table' with a column called "Value", you could do this in DAX:
Aggregation Table = VAR __Table = { "Measure" } RETURN ADDCOLUMNS( __Table, "Average",AVERAGE('Table'[Value]) )This will result in a 2 column table with an "Average" column and a "Measure" column. Measure column is meaningless, you just have to have a row to get values to calculate. Average column has your average. Because this is calculated at the time of data load/refresh, RLS does not apply. You can thus use Average column to compare in your formula without hitting RLS issues, duplicating all of your data or exposing sensitive data.
Community ChampionSimonSeez - The way I have done this in the past is to create an aggregation table that does not interact with RLS. So, for example, let's say that you have a table called 'Table' with a column called "Value", you could do this in DAX:
Aggregation Table =
VAR __Table = { "Measure" }
RETURN
ADDCOLUMNS(
__Table,
"Average",AVERAGE('Table'[Value])
)This will result in a 2 column table with an "Average" column and a "Measure" column. Measure column is meaningless, you just have to have a row to get values to calculate. Average column has your average. Because this is calculated at the time of data load/refresh, RLS does not apply. You can thus use Average column to compare in your formula without hitting RLS issues, duplicating all of your data or exposing sensitive data.