Forum Discussion
USERELATIONSHIP not working - why?
D_PBI - your measure for the Consultancy Agreements graph is incorrect when you have the dimDate[Date] <> agreement[Execution Date] active, because this relationship is still filtering the data even though you have used USERELATIONSHIP to activate the other inactive relationship. This is why I suggest de-activating all date table relationships.
Once you have done this, you need to reconfigure how your filters work, in order for the correct context to be used in your cumulative totals. I would suggest chaining your measures together. This means splitting your logic out into two measures. See below:
Measure 1 = CALCULATE( DISTINCTCOUNT ( item ), USERELATIONSHIP( dimDate[Date], agreement[Execution Date] )) Measure 2 = CALCULATE ( [Measure 1], FILTER ( ALLSELECTED ( Date logic........This ensures the filter context for date is already applied when you ask the 2nd measure to route through your dates.
I would also suggest you mark your dimDate as a date table, this helps Power BI understand where time intelligence is being used.
If you still can't get this to work and need me to help any more, you will need to supply some anonymised test data in the exact structure of your model so I can do some testing of my work, and send you back an example file that shows you how this works.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Hi D_PBI,
Thanks for your update. I completely understand the constraints around creating a dummy file from scratch. However, to properly troubleshoot the issue with USERELATIONSHIP, we really need a sample .pbix file ideally with the same data model structure or a simplified version that still reproduces the issue.
Even just a file with mock data that replicates the relationships and expected behavior would be incredibly helpful. This will allow us to test and provide a more targeted solution.
Please do share it whenever you're able. We're happy to help further once we have something concrete to work with.
Warm regards,
Prasanna Kumar
No, unfortunatley this matter for me still isn't resolved.
The USERRELATIONSHIP function doesn't seem to be working correctly. I don't know why. I appreciate mark_endicott help on this matter. I can't mark it as resolved because it isn't. I also cannot provide Mark with his requested dummy file as I'd have to build the file from scratch with dummy data and I just don't have the capacity to do this currently.
D_PBI - in my last message I gave you EXACT instructions for how you should structure this.
Deactivate all of your date table relationships and follow the chained measure structure for ALL measures where date analysis is needed and your problems will be resolved.
It may be a lot of work, but you will have to do this to ensure the correct date filter context is supplied to the logic you need to implement. Trying to do this inside a single measure is causing all of your problems.
I would consider this solved seeing I have done as much as I can to help you through this, it is now on you to work through it, or supply more information for me to work on it for you.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!