Forum Discussion
DAX using USERELATIONSHIP is not working for an inactive relationship
Community ChampionIt isn't working because you have ambiguity in the model. Hard to see exactly how it is flowing, but it appears to be this:
Get rid of those bi-directional relationships and see how it goes. Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema
You could - could - try turning off the bi-di in measures by using CROSSFILTER(table1[field], table2[field], ONE) (or NONE to just disable the relationship) but your best bet is get rid of all of the bi-di and only turn it on in ither measures via CROSSFILTER.
THe first thing I do when someone asks for my help with a model that has any bi-directional relationships is copy the file, turn them all back to single, then tweak measures and slicers to work as if they had bi-di turned on by only turning them on in measures, almost never ever in the model itself.
Hi edhans,
Thanks for the response! Yes, you are correct in identifying the flow in the data model (blue arrows). 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 thoughts, comments and/or suggestions would be really helpful to either resolve the DAX, or implement RLS in a better way, or a different solution altogether.
Thanks again!
