Forum Discussion
Tabular - Security Filtering Behaviour = None
So I've made it do what I wanted by setting the relationship between Person and Results as Many Many Cardinality with Person filtering results. (That reset the Security Filter = None so had to put it back). Everything now works as I wanted. Would love to know why though! Might be something to do with the Many Many relaitonship breaking table expansion? AlexisOlson, marcorusso, smpa01, parry2k any ideas?
Updated version with this in place here
Correct. Limited relationships are not used in table expansion.
This is mentioned in both of these articles:
https://www.sqlbi.com/articles/strong-and-weak-relationships-in-power-bi/
https://www.sqlbi.com/articles/relationships-in-power-bi-and-tabular-models/
Thanks Alexis, thought that was the case.
What I'm trying to understand is the behaviour of the "Security Filtering Behaviour = None" and how/why it's behaviour changes with a limited relationship.
I went back and watched some videos from SQLBI's Mastering Tabular. As a result I wondered if actually the setting only kicked in with a bidirectional relationship. Have a look at:
One to One Example
Relationships are setup as:and the security filtering behaviour between Person and Demographic is set to none.
Connecting to the model with DAX studio as Group 1:
Then running the following:
EVALUATE Results EVALUATE { COUNTROWS ( Results ) }gives:
(6 rows)
vs
Even thinking in terms of expanded table I can't come up with anything close to even a loose explanation of this.
How does this work as an explanation. Might be miles off so please shoot it down but works in my head.
When Filtering Behaviour is set to None in the latest example the rows in Person table don't go into the filter context and so COUNTROWS returns 12. However when EVALUATE returns 'Results' it returns only rows present in the expanded table.If filtering behaviour is left as default (Single) then rows from Person go into filter context and so COUNTROWS and EVALUATE match.
If the relationship is many to many the relationship is limited and so expanded table plays no part.
No, talking to myself but that logic breaks down when other predicates like Test[Test Name] = "Test 1" are thrown into the filter context. Going to ponder on it further.