DAX to ignore row level security

DAX DAX Calculations
1

Hi Guys,

Does anyone know what DAX measures are needed to allow RLS security to be ignored.

I’ve implemented RLS successfully but would like the end users to be able to compare themselves against the rest of the organisation.

I’ve tried wrapping the orginal measure with a ALL and ALLEXCEPT but when i drill through the data the results from the measure does not change.

Normal DAX

 = calculate(([NN] + [SN] + [MN] + [ND] + [SD] + [MD]) / sum(Fact_Sickness[WD]),('Fact_Sickness'))

Wrapped DAX to attempt to ignore RLS

= calculate(([NN] + [SN] + [MN] + [ND] + [SD] + [MD]) / sum(Fact_Sickness[WD]),ALL('Dim_Team Hierarchy'),all('Fact_Sickness')

The measures I’m measure branching of are just countrows using filter.

Any help would be appreciated.

2

I’ll have to do more research on this one.

But I have to say the first thing that comes to my mind is why are you doing this? Seems to make life way more difficult than it needs to be…

If you want people to compare why not just not use RLS. Maybe create a seperate report that has all the functionality required without the RLS?

I’m just one for always keeping it simple rather whereever possible so just sharing my view on this one.

I don’t immediately know how to do this so I’ll have to research and come back.

Maybe someone else has some input here.

Chrs

3

I also just did some research on this one to understand it a little more myself.

ALL and ALLSELECTED are unlikely to work in this case. RLS places an overall filter on the data, kind of like a parameter does in the query editor.

But, I think there is another way where you can hard code values in a calculated column.

I found the technique in this forum post. Check it out, I’m relatively sure this is the best way to do what you need.

4

Hi Sam,

Thanks for the solution posted. Looks like I misunderstood ALL/ALLSELECTED. I’ll give it a go and see how it goes.

Another option I though of would be to either duplicate the or summarise the fact table without connecting it to table that has the RLS on it. Not the most efficient way I know but would probably work.

Thanks for your time.

5

Nice one, I think you idea also sounds like a good solution also

6

Hello, I have come across a similar situation where the business wants the users to see their data only but also see high level numbers of the overall business to compare themselves against. Reading about your solution of creating a separate fact table, i wanted to check how did that go or did you come up with another approach in this time ??

Regards
Grace

1 Like
7

We have the same issue at our site. We need to compare an individual’s score against his/her department average score. Obviously with RLS the individual can no longer see the department average score.

The solution of loading the data twice - firstly at a detail level (to satisfy RLS requirement) and then second a slightly more aggregated level (to satisfy comparative requirement) seems to be the best solution at this time.

If anyone knows of a more recent, or better solution, please let me know.