DAX to ignore row level security


#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