Row Level Security for many to many relational lookup

[EWright06]

Hi everyone,

I currently have two tables producing a report and am attempting to create RLS which filters the data according to roles. Example tables are below. The columns in the Attributes table were unpivoted and are therefore non-unique. Several tables in the report visualization use Animals[Species] as the key, and due to the relationship between the tables, I'm able to use a slicer to filter according to descriptors. E.g. I can select Carnivore and the visualizations which exclusively use Animals-table values will also filter to only show species with the Carnivore descriptor - working as expected.

What I'm unable to achieve is applying row-level security to the Animals table using a lookup from the Attributes table. DAX expression 'Attributes'[Descriptor] = "Carnivore" filters appropriately for visualizations which use Attributes values, but when I attempt to add the same expression to the Animals table, I receive an error stating that a single value for Descriptor cannot be determined (which makes sense). Other attempted expressions have similarly failed.

Attributes          Animals

Species       *:*   Species

Attributes Table

Descriptor	Species
Mammal	Goat
Mammal	Whale
Mammal	Lion
Carnivore	Whale
Carnivore	Lion
Herbivore	Goat

Animals Table

Species

Goat

Whale

Lion

Cat

The goal is to have a boolean which applies a filter to both tables and all related visualizations based on Species which have a given Descriptor. Is this possible, or will the data need to be restructured? I'd thought the relationship should be writable as 1:* as the Species values in Animals table are distinct, but the model is forcing a many to many selection.

Thank you for any advice!

[v-jingzhang]

Hi @EWright06 

It's strange that you cannot change the relationship to 1:*. Please check if values in Animals[Species] are distinct. Perhaps there are some duplicate values there.

For the relationships, you may add a third table to have distinct Descriptor values as a Dim Descriptor table. Create a 1:* relationship between this table and Attributes Table on Descriptor column. So the relationships between three tables will be:

• Animals[Species] -> 1:many -> Attributes[Species]
• Descriptors[Descriptor] -> 1:many -> Attributes[Descriptor] 

Then build your RLS expressions for Animals table and/or Descriptors table. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[EWright06]

Thank you, the values were indeed distinct - I was able to convert to 1:* after swapping the cardinality order.

However neither that, nor adding a distinct Descriptor table, has enabled me to use RLS for tables which pull in data from the Attributes table. Whenever I try to make a reference to a specific Descriptor, it returns an "a single value for x could not be determined" error. I'm thus unable to show data for only species which are associated with a particular descriptor, which is the ultimate goal. Maybe I could create a column in the Animals table which concatenates all descriptors it finds, and then set an RLS reference to that column with a contains search, but I think there must be a better/simpler solution than that?

Edit: struggling with that as well. There's a slicer which achieves exactly what I'm looking for: I can select a descriptor and all visualizations - whether they're based on Attributes or Animals values - update to only show animals associated with that descriptor. I'm trying to achieve the same thing with RLS, being able to pre-filter results based on role assignment. It seems like one of the primary purposes of RLS, but clearly I'm not understanding something.