ignore RLS for a aggregated measure

[Anonymous]

HI 

I HAVE A PBIX FILE I CAN SEND TO YOU IF YOU WANT A TEMPLATE 

I have a aggreate  sum measure that dosent work as entented after i apply RLS

Sales amount= sum(sales)

before RLS is applied

1) the company is chossen in "Company"

2) the employee is chossen in the table "Employee". In this case Frederik. 

3) an employee has different "sales" in different companies. see in the “sales by company”

4) the “sales by company” has no interaction to the company visual (made in format -> edit interaction)

After RLS is applied
Now we have a problem in the “sales by company” when I have chosen Frederik in “sales by employee”

When I chose an employee the sales amount only show the value for one company even tough that Frederik has sales in other companies.

the data model

The RLS applied

SEARCH(","&LEFT(USERPRINCIPALNAME(),(FIND("@",USERPRINCIPALNAME(),1)-1))&",", [User] , 1, 0) > 0

the problem:

i cant add "TEST" to multiple companies in the user because TEST can only view for specific company in the report EXCEPT the “sales by company” then they need to see all the companies that an emplyee has sales.

The goal:

Get the same output as in the first picture AFTER i apply RLS and i chose an employee i have to see all sales for each company.

possible solutions:

writa a measure that over write the RLS. 

write a more complex RLS code

[AilleryO]

Hi,

Could it be possible to make a pre-consolidated table of your data ?

If you create a calculated table with your consolidated calculations it should not be affected by RLS.

Do not hesitate it this is not clear enough.

[Anonymous]

do you mean to merge the two tables or to use the function calculatetable?

[AilleryO]

I was thinking of CALCULATETABLE. That's what I did in a project to keep the display of the percentage of different region, even when RLS was applied to a region.

The RLS should not affect the calcultation of your calculatedtable.

[Anonymous]

do you have a sample of the code. I will for sure look into the function and try to solve my problem with calculatetable

[AilleryO]

Hi,

Here is the code to create my consolidated table :

Nb Products (%age byOrigin) =
VAR ListCountry =TREATAS(VALUES(Products[Origin]),Products[Origin])//Create list with level of conso you need
VAR NbProducts =COUNTA(Products[SKU_Number])//Count of product number/ref
RETURN
FILTER(SUMMARIZECOLUMNS
(
ROLLUPADDISSUBTOTAL
(
Products[Origin], "IsCountruSubTotal", ListCountry ),//Create sub totals
"Share in %", //Name of column created
DIVIDE(COUNTA(Products[SKU_Number]),NbProducts )),//Calculation of %age
NOT(ISBLANK(Articles[Origine]))//option (Filter to get rid of blanks)
)

And then I create my visuals based on this table, and not on the original one.

Let us know

[Anonymous]

It is applied to the User table. 

[lbendlin]

RLS is ineffective as the arrow points into the Users table. Change the relationship.

[Anonymous]

i tried making it a one direction between the user and company table but that is not allowed. i tried connecting the user and koncern table directly but that did not solve the problem either

[lbendlin]

Change the relationship from 1:1 to 1:*  , then you can make it single directional.

[Anonymous]

i can now change it to single direction but it did not solve the problem

[lbendlin]

Which table is the RLS applied to?