Anonymize column based on RLS (Only show names of own team)

[Sebast1aan]

Hi,

We have scores on multiple brokers. Each manager can see the score of his/her own team members (broker). And should be able to see the overall score of other teams, but not be able to see the score of team members in the other team.

With RLS I can get to the first part: Show scores of own team. But how do I get the scores per team without seeing the individual scores in that team as a manager?

Table with scores:

Column "Manager" is successfully used for RLS

Current result:

What I try to achieve:

Any suggestions?

Kind regards,

Sebastiaan

[v-kkf-msft · Accepted solution]

Hi @Sebast1aan ,

1. First create the following calculation table. 

SubTotal = 
SUMMARIZE(
    Scores,
    Scores[Country],
    "Ave_Score", AVERAGE(Scores[Score])
)

2. Then create the inter-table relationships.

3. Create the following measure. 

Measure = 
IF (
    ISFILTERED ( Scores[Broker] ),
    CALCULATE (
        AVERAGE ( Scores[Score] ),
        USERELATIONSHIP ( Scores[Country], SubTotal[Country] )
    ),
    SUM ( SubTotal[Ave_Score] )
)

4. I created two roles for testing and looked at the scores for countries Belgium and Germany respectively. 

  

5. Here are the results of my test. 

        

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bcdobbs]

This sort of pattern might help:

Implement Non Visual Totals with Power BI security roles - SQLBI

I have also been playing with another way of doing it which I think might be more elegant but I can't find any documentation on it so not sure how supported it is.
Re: Tabular - Security Filtering Behaviour = None - Microsoft Power BI Community