Forum Discussion
Compare two measures without filtering one
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
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.
2 Replies
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.
