Forum Discussion
Compare two measures without filtering one
- 6 years ago
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.
One way of doing this is duplicating your fact table, and don't create a relatioship with the Dimension(s) used for RLS. You can then do the overall calculations on this new fact table for comparison purposes.