DAX using USERELATIONSHIP is not working for an inactive relationship

[RavitPBI]

Hi Folks,

Table1: Dim_LaborShift

Table2: Fact_HoursPlanner

Data model:

I am trying to create matrix which shows "Total Shift Length" for a particular User on a particular Date. As you can see from the data model, I have an INACTIVE relationship between "Name" (Dim_LaborShift) and "TEXT_VAL" (Fact_HoursPlanner) and I am using this relationship in the following DAX with USERELATIONSHIP to establish the relation but it is summing all ShiftLengths in the Dim_LaborShift table and populating those for ALL Users on ALL Dates.

Basically, in the matrix I want to show only values where the User has been assigned one/more Shift(s) and the Total Length of those shift(s) for a given date. Per the example in the screenshot, it should be:
Matrix:
User: 11/21/20 | 11/22/20 | 11/23/20 | 11/24/20 | 11/25/20 | 11/26/20 | 11/27/20

1457178 : 15.5 (i.e 7.5 + 8.00) | | | | | | |

295949: 5 | | | | | | |

DAX usage: Total Shift Length = CALCULATE(SUM('OLAP LPP_Dim_LaborShift'[ShiftLength]),

USERELATIONSHIP('OLAP LPP_Dim_LaborShift'[Name],

'OLAP CUBE_LPP_Fact_HoursPlanner_WB'[TEXT_VAL]))

I am not quite sure why USERELATIONSHIP isn't working here and ANY help/guidance in achieving the above result would be great. 

@amitchandak 

[edhans · Accepted solution]

Great @RavitPBI if you could mark one or more of the responses in this thread as the "Solution" that would show others it is closed. Hope your project continues to go smoothly.

[edhans]

It 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.

[RavitPBI]

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!

[daxer-almighty]

This 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.

[RavitPBI]

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]

"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.

[RavitPBI]

Thanks again, @edhans, for sharing your thoughts. I implemented the former design strategy and it is working as it should. Like you said, in the long term there is minimal-to-no risk of any surprising results with this approach.