Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Stephen_Connell
New Member

Model Help Multiple Facts Filter Dim

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:

Stephen_Connell_0-1728050897778.png

Here is my RLS Query:

Stephen_Connell_1-1728051047439.png

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:

  • Slicers should filter when RLS is applied.
  • Slicers must be linked so from page to page and fact to fact the selection in a slicer must be maintained.
  • I have more than the two Dim tables so although I'm showing User I have other similar requirements for other Dim tables
  • There is another RLS table that follows a similar pattern but has completely different set of rules so cannot be combined.
    • These other facts join the same Dims
    • Are at a different, higher, grain

Really looking for advice and here's what I have considered

  1. Combine the Fact tables. A bit of work here as multiple sources and lots of CTE at source to get it where it needs to be. 
  2. Duplicate the Dim Tables and sacrifice the linked Slicer functionality.
  3. ...?

Any other options of avenues for exploration I should consider?

 

1 ACCEPTED 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.

View solution in original post

5 REPLIES 5
Stephen_Connell
New Member

@lbendlin 
The user has as explained a 1-N relationship with Budget. The RLS rules PATH comprises either 2 or 3  components:

  1. Easy rules:
    1. The users Management Hierarchy when they are the owner of fact record
    2. The Budget Hierarchy of the fact record - this can be different for the same user.
  2. Harder rules:
    1. The users Management Hierarchy when they are part 1 owner of fact record
    2. The users Management Hierarchy when they are part 2 owner of fact record
    3. The Budget Hierarchy of the fact record - this can be different for the same user.

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.  

Stephen_Connell_1-1728210595259.png

 

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.

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.