TL;DR Given the following cardinality, here are the most efficient ways for your security table to relate to and filter your reporting data:
- If you're relating your security table to a "1", like the primary key of a dimension table, bidrectional is required if you want the related table to be filtered. And unless it's a 1:1, you will have to enable the special bidrectional security setting for the relationship.
- If you're relating to a "many", like the foreign key of a fact table, single direction crossfiltering is sufficient to filter the related table.
If you have configured row-level security (RLS) in Power BI before, you have probably noticed that relationships don't behave quite the same way as typical semantic model relationships, hence the "Apply security filter in both directions" relationship setting's existence.
To organize our thinking around this, remove guesswork, and generate a simple recommendation for each originating join cardinality, I systematically tested all of the relevant variables for each cardinality between a security table and a hypothetical tableA, a directly related reporting table.
One bit of foundational knowledge that explains some of the behavior is this nugget from the documentation:
By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional. You can manually enable bi-directional cross-filtering with row-level security by selecting the relationship and checking the Apply security filter in both directions checkbox. Note that if a table takes part in multiple bi-directional relationships you can only select this option for one of those relationships.
Here are all of the results, but I summarized the high-level takeaways at the top of this post.
