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
D_PBI - it is possible that the active relationship with ExecutionDate is still pasing a filter onto your table.
You can try using two inactive relationships (this would always be my suggestion when using USERELATIONSHIP() - but you will need to ensure all your measures then specifiy which relationship they will use) OR you could try adding REMOVEFILTERS( agreement[Execution Date] ) as an additional filter condition on your DAX measure. This should remove any filter context that is being placed through your active relationship.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!