Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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]),
I am not quite sure why USERELATIONSHIP isn't working here and ANY help/guidance in achieving the above result would be great.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreat @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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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!
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.
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!
"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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
42 | |
37 | |
22 | |
22 | |
21 |