Row Level Security for related tables (1:*)

[EWright06]

Creating a new post as my previous one had red herrings (what I thought was the issue turned out not to be the issue).

I have a report with visualizations from two different related tables. Obfuscating, Table1 is a distinct list of buildings and a series of its attributes (e.g. square footage, occupancy limit). Table2 is an unpivoted list of teams and the buildings they occupy. One team may occupy multiple buildings, and one building may have multiple teams, so both columns have non-distinct text values.

Table1[Building]   Table2[Building]
                        1 : *

Table1

Building	SqFt	Occupancy	Altitude
A	10k	100	150'
B	15k	180	-10'
C	12k	125	30'
D	5k	50	-3'
E	7.5k	70	5'

Table2

Team	Building
Development	B
Development	C
Finance	B
Finance	D
HR	C
Legal	A
Sales	A
Sales	E

The report has visualizations based on values from both tables: cards with summed square footage, a table with many of the building attributes, etc. I have a slicer on Table2[Team] and it functions perfectly - I can filter by 1+ teams and all visualizations update accordingly, including those which only show Table1 building data. I'm trying to achieve the same thing with RLS: create roles for each team such that they'll arrive to the report with the data pre-filtered, unable to see data for buildings they don't occupy. A DAX for Table2 is easy enough, but only restricts access to visuals using T2 data. Whenever I try to apply a DAX to Table1 based on Table2 team name, I get a "cannot resolve to a single value" error. I'm hoping someone can point me to the (probably obvious) thing I should be doing instead.

Thank you!

[OwenAuger · Accepted solution]

Hi @EWright06 

You can place a RLS Filter rule on Table1 similar to this example (for a role where only the "Development" team is visible):

VAR TeamList =
    { "Development" }
RETURN
    CALCULATE (
        NOT ISEMPTY ( Table2 ),
        TREATAS ( TeamList, Table2[Team] )
    )

This tests whether Table2 is nonempty with the relevant Team filter applied, along with a filter corresponding to the current row of Table1 (due to CALCULATE).

Note that RLS filter expressions are evaluated for each table independently, in an "unfiltered" context.

Does this or something similar work for you?

[EWright06]

Thank you so much, it works!

To make sure I'm understanding why it works, is this rule effectively:

1. Create a variable of team name so that it can be evaluated
2. Create a T2 sub-table filtered to only rows where Team matches the variable
3. If that filtered table is non-empty, return TRUE

I'm missing where it then evaluates the current row's T1[Building] value - is that part of the TreatAs, Calculate, or built-in logic to the RLS expression itself?

Thank you again!

-Eric

[OwenAuger]

Glad to hear it 🙂

Youre explanation above is essentially correct!

I'll just explain the DAX code in my own words in case it helps:

1. Create a variable TeamList which can contain one or more Teams that should be visible for this RLS role.
2. Evaluate Table2 with two filters applied:
   1. The above TeamList filter applied (via TREATAS)
   2. The current row of of Table1 applied as a filter (due to CALCULATE which triggers context transition since this is evaluated in the row context of Table1)
3. If Table2 is non empty with these filters applied, return TRUE, in which case the current row of Table1 will be visible.

A simpler version with the same effect for a single Team could be:

CALCULATE (
    NOT ISEMPTY ( Table2 ),
    Table2[Team] = "Development"
)

Regards,

[EWright06]

Thank you, your framing got things to click in place for me - especially in the simpler formula, which I've substituted since roles will only be assigned a single team.