filtering by the user without model RLS?

[fsim]

Hi there !

is there any workaround to be able to have one report that can show all the data or only the data of the end user ?

in other words, I would like to have in a single report, two different "views". One for all the opportunities (from the crm) and one for the end user's owned opportunities.

I wanted to create a column that check if the userprincipalname()= owner.domainname, but it's forbidden by the system 😕

I would like to avoid publishing the same report twice, one with RLS and one without.

thanks in advance

[mattww]

Hi @fsim , 

You could use USERPRINCIPALNAME() in your measures, I'm not sure how advisable or secure this is since RLS was designed for exactly this purpose.

MySales = CALCULATE(SUM(Opportunities[SalePrice]),Opportunities[Owner] = USERPRINCIPALNAME())

You'd also have to apply this logic to every relevant measure, not sure how feasible that is, although Calculation Groups could help make that easier.

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[mattww]

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[fsim]

Hello Mattww,

thanks for the answer.

this could indeed resolve the measure parts but not the list and the row filtering.

thanks anyway !

[mattww · Accepted solution]

Yeah I agree, you could also use a COUNTROWS() on your Sales table and include a filter on every visual to only show results where the row count > 0, this would effectively hide any inapplicable dimensions.

The other option if you're looking for RLS to show all results to certain people (e.g. senior leaders) is to create a role in RLS (e.g Sales Director) where the condition is always true (i.e. 1=1), anyone assigned to this role will see everything

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.