Compare two measures without filtering one

[SimonSeez]

Hello, 

I want to display a performance-related statement on a report I am working on. So I wrote a DAX statement like this. 

= If ('Stat DB' [AVG Tran Time] > 'Stat DB'[Total AVG Tran Time], "You need to improve on your transaction time", "You are doing a great job") 

'Stat DB' [AVG Tran Time] = Is expected to show average transaction time for the person viewing the report

Stat DB'[Total AVG Tran Time] = Is the total average transaction time for everyone. 

The problem is because I use row-level security, when users view the report, both times are always the same result hence, users all get the second option in the DAX above. I think I need to find a way to make Stat DB'[Total AVG Tran Time] not change based on who is viewing the report. 

Any ideas of how I can implement this? 

Best Regards, 

Simon 

[Greg_Deckler · Accepted solution]

@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. 

[PaulDBrown]

@SimonSeez 

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.