Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi I have a multi fact model that I have inherited.
I have a dim which supplies an RLS path file that I use to limit the data in the fact tables. What I would like to happen also is that the slicers from other Dim tables should also be limited to that which is governed by the RLS:
Here is my RLS Query:
I use Dim_User to display on table visuals on my report and also for slicers. However the list of users is long and I would like it limited by RLS too.
The PATH field for RLS uses both Management Hierarchy and Budget Ownership for other users and work that they complete in a range of budgets. As such the relationship on User to Budget is 1 - * other wise I would have filtered on Dim_User. This is what I inherited and led to user complaint that they could not see records that were relevant because it was based solely on Management Hierarchy.
My Requirements are:
Really looking for advice and here's what I have considered
Any other options of avenues for exploration I should consider?
Solved! Go to Solution.
When you apply slightly contradictory RLS rules the more permissible rule will win. When you apply completely contradictory rules then both rules are ignored.
Say, one rule says "Everything except country A" and the other rule says "Only country A". As you can imagine, both rules will be OR combined, resulting in a combined TRUE, and making these rules pointless.
@lbendlin
The user has as explained a 1-N relationship with Budget. The RLS rules PATH comprises either 2 or 3 components:
So in a given scenario I might need to see a user I have management responsibility for OR budget owner ship for. As such I dont think I can apply this logic to the user. Or are you suggesting that I make the PATH field include every Budget Hierarchy they are in AND their Management Hierarchy they might have 20-30 budgets on their record?
What might the DAX look like in that context? Struggling to conceptualise that. However I think you make a good point on some other the ones e.g.
Dim_Budget:
PATHCONTAINS(Dim_Budget[PATH],
MaxX(
Filter(
Dim_User,
[Email]=USERPRINCIPALNAME()
)
,Dim_User[SearchID]
)
)
It's a simple concept. RLS needs to be applied to the outer rim of your data model. The filters need to flow inward and need to be able to affect child dimensions and fact tables.
If you have an arrow pointing into an RLS table then you know you messed up (for example). Follow the arrows.
OK so in this case where I have 2 sets of rules with conflicting OR clauses would you recommend 2 copies of the dim tables one for each set of rules and apply the rules to these dim table too.
Sacfrifice the consistent selection on the dim table?
Or is there some guidance you can point to that would offer I understand what you have said here:
@lbendlin wrote:It's a simple concept. RLS needs to be applied to the outer rim of your data model. The filters need to flow inward and need to be able to affect child dimensions and fact tables.
If you have an arrow pointing into an RLS table then you know you messed up (for example). Follow the arrows.
But not sure its helping me understand how to square the contradicary rules. I don't have anything in my RLS tables that currently joins to my Dim_User so would need to change that. I'm happy enough to do this but not sure I understand what you are suggesting.
Given the rules I described above what you have said poses more questions that providing answers.
When you apply slightly contradictory RLS rules the more permissible rule will win. When you apply completely contradictory rules then both rules are ignored.
Say, one rule says "Everything except country A" and the other rule says "Only country A". As you can imagine, both rules will be OR combined, resulting in a combined TRUE, and making these rules pointless.
What I would like to happen also is that the slicers from other Dim tables should also be limited to that which is governed by the RLS
Apply your RLS rules to these dimension tables as well.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
110 | |
109 | |
93 | |
69 |
User | Count |
---|---|
171 | |
135 | |
132 | |
96 | |
94 |