Forum Discussion
DAX using USERELATIONSHIP is not working for an inactive relationship
Solution SageThis model doesn't look good. It's ambiguous (I can say this at first sight), so I wouldn't trust a single figure you get from it. If you try to enable the inactive relationship, one of the tables will immediately be able to be filtered from another table via 2 different paths and this is probably why the engine does "strange" things. If I were you, I'd deeply re-think the model and stick religiously to a proper star schema with no bi-dir filtering. Bi-dir filtering and inactive relationships are not something to be taken lightly. They can create all sorts of problems in a badly designed model. Your model certainly is ambiguous when you try to enable the inactive relationship.
Hi daxer-almighty ,
Thanks for your inputs! As I outlined in the reply to edhans - I agree the usage of bi-dir, many-to-many and/or inactive relations should be sparingly used (even if one is a DAX expert), however, the reason I have bi-directional relations between (Dim_LaborShift, Dim_Organization) and also (Fact_HoursPlanner, Dim_User) is because I am trying to implement Row-Level Security in the model (see Dim_UserRLS) and I am trying to use Dim_UserRLS to filter out [based on the flow] Dim_LaborShift --> Dim_Organization --> Fact_HoursPlanner --> Dim_User so the user only sees his/her organization's & user's data. This is working fine, however, since I needed to have a bi-directional b/w Dim_LaborShift and Dim_Organization, I had to change the relationship between Dim_LaborShift and Fact_HoursPlanner to inactive. Consequently, my "Total Shift Length" DAX which was working just fine previously, isn't anymore. In my model, I need to have multiple Facts & Dimensions and the dimensions do not have an active relationship with other dimensions (since they already have active relationship with the Fact) which I need them to have for implementing Row-Level Security to flow through them.
The only option I see that avoids, both, bi-directional and inactive relations is to have multiple RLS tables attached to the necessary dimensions to filter them down based on logged-in user.
Any suggestions around implementing better DAX, RLS or a different solution would be helpful.
Thanks!
- edhans
Community Champion
"The only option I see that avoids, both, bi-directional and inactive relations is to have multiple RLS tables attached to the necessary dimensions to filter them down based on logged-in user."
Without more info on the model and looking at it, I cannot say if that is the only option, but it sounds reasonable. And without question I would implement that strategy vs the bi-di strategy. One is a little more front end work. The latter has all sorts of side effects, that are currently causing issues and will continue to do so in the future.
