cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RichardV Advocate II
Advocate II

Undo (dynamic) row level security with a DAX filter?

Hi,

 

At the moment I am having the following challenge. I hope someone can help me.

 

I would like to create a report, and embed it in the portal. I am planning to use (dynamic) role level security to make sure each user sees only their own data. However, it is a requirement that users want to see average values of the other users as a benchmark. In other words, we need to "undo" the row level security filter for one or two measures.

 

One way to overcome this issue, is to create different tables, and apply role level security to selected tables only.

 

However, this will make modeling more complex. Therefore my question:

 

Is it possible to undo the dynamic role level security filter with a DAX formula?

 

That way we can create one report for multiple users, and each can make use of benchmark data, in which data of the other users will be used.

 

Many thanks in advance,

 

Richard

6 REPLIES 6
Microsoft
Microsoft

Re: Undo (dynamic) row level security with a DAX filter?

@RichardV

 

Once Row Level Security is applied, the table context is filtered, so your calculation will only take the context with accessing users' own data only. And the internal working of Power BI is a Tabular model, there's no "Enable Visual Total" feature like Multidimensional Model. So it's not possible to undo RLS on any specific DAX formula.

 

For your requirement, I suggest you add a calculated column to get the average on ALL table. Since the calculated column is along with row leve, you can still get correct result when RLS is applied.

 

44.PNG

 

45.PNG

 

Regards,

RichardV Advocate II
Advocate II

Re: Undo (dynamic) row level security with a DAX filter?

@v-sihou-msft

 

Many thanks for your response. I can see that it has taken some time to make an example for me, which I and my team highly appreciate. I have just another requirement.

 

In our benchmark values E.G. average sales amount of all users, which can be created by a calculated column we need to filter, we need to exclude the sales amount of the users viewing the report. That way, the user can benchmark him/herself to other users.

 

Do you know if a calculated column can be made to exclude the data from the user viewing the data via RLS?

 

If this is not possible, we might have to think about copying parts of our tables or do no use RLS at all, but just make 100 different reports for our users.

 

Many thanks in advance!  

RichardV Advocate II
Advocate II

Re: Undo (dynamic) row level security with a DAX filter?

By the way, we are using a SSAS tabular model

waltheed Solution Supplier
Solution Supplier

Re: Undo (dynamic) row level security with a DAX filter?

Hi Richard, 

 

I am having exactly the same requirement. 

Were you able to solve this? I'd really appreciate your help. 

 

Cheers, Edgar Walther
Power BI User Group Netherlands
mpsrshl Helper II
Helper II

Re: Undo (dynamic) row level security with a DAX filter?

Hey there, 

 

Depending on your RLS model applied, in my case I had to apply a measure that compares the value of an employee filtered after RLS in a separate and detached table against the data model. The new Filter experience helps as well if you just want to turn visuals into RLS by using a measure with a detached employee table by adding that filter to the visual and hiding it for view. Whichever way there will be some challenges. 

 

I hope it helps. 

jimmy7377
Frequent Visitor

Re: Undo (dynamic) row level security with a DAX filter?

I am too having the same issue with a small varient:

 

I have global data that I need everyone to see, it contains geographical information. Its the same values that everyone must see but then everyone must then see there own data. RLS its giving me all or nothing and i need this section for everyone to see then everyone get their own data. 

 

So: Provider to be sourced, all to see 

but under the same column is the individual providers which is what im filtering on my RLS.

 

Any help would be gratefully received.

 

Kind regards,

 

Jimmy

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors