RLS Aggregation of not shown values

[artur4]

Hi everyone,

I am dealing with RLS and I can't get out of it with the result I want.

I need to set some RLSs which show the values according to specific rules, but at the same time I want to show all the other values summed up together. I give you an example. 

So, I have a matrix which on the rows and columns has the countries and as values the impact each country has on the others (it is the sum of multiples rows under IMPACT column in a table). Particularly, the rows are the impacted countries while the columns the impacting ones.

My Visual looks like this:

	SPAIN-	-FRANCE-	-CHINA-	-JAPAN
SPAIN	0	10	5	7
FRANCE	7	0	4	5
CHINA	11	6	0	3
JAPAN	6	7	8	0

I created some roles depending on the regions (EUROPE and ASIA) as I do not want that European countries see the Asians details and viceversa. Moreover, (this is the part in which I am having some difficulties) I want to create an additional lable , OTHERS, which sums up all the values of the non-shown countries and display it on the Visual. For instance, for the European RLS countries I want to get a matrix like this:

	SPAIN -	-FRANCE-	-OTHERS
SPAIN	0	10	12
FRANCE	7	0	9
OTHERS	17	13	3

FYI, the countries are even mapped at regional level, REGION column. So, maybe it can be easier to use this column rather than the name of each single country.

Do you have an idea on how to manage this?

[artur4]

Solved in a similar way suggested above but with a workaround. 

[Smalfly · Accepted solution]

Hi @artur4 ,

users that are impacted by RLS have no access to the rows they are not allowed to see.

So there is no way to add values in these rows to a measure.

The only workaround that exists, is that you somehow add a calculated measure e.g. Total to every single row in your source table (not by using a measure because the measure will not take into account rows the user has access to). You can then use this total value and subtract everything the user has access to from it. This blogpost explains a similar situation.

[artur4]

Thanks @Smalfly  for your message. 
But, how can I create the OTHERS label? The link you've posted relates to the Total Row of the matrix.

[Smalfly]

You could do something like this:

others = 

var total_impact = sum(total_measure) ,

 RLS_impact = CALCULATE(SUM(impact), ALL([Country table]))

return total_impact - RLS_impact 

Please note that the above is pseudo code to express how you could organize your measure. It will not take into account any of the filters that have been put on the Country table.

Just to say that you'll have to adjust this pseudo code to your situation.

[artur4]

Thanks for your advice. 
I do not get it honestl since for me SUM (impact ) and sum(total_measure) are the same number.
Then, I shoul get different values for Others on the column and row (as in my example above).

[artur4]

And, the countries in the column headers and row headers are in different column in the source:
- Rows countries are in a column named "Impacted countries"
- Column countries are in another column named "Impacting countries"