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
mark_endicott thanks for your reply and that's a good question.
I made the dimDate[Execution Date] <> agreement[Received Date] inactive. So all relationships between dimDate and agreement were inactive relationships. The results was, on my page I have other line-chart visuals, the line-chart visuals that use a (any) relationship between dimDate and agreement all altered and all became non-cumulative line. You'll note the measure is to calculate a cumulative total. So, yes, I think the active relationship was having some affect.
Having re-made the dimDate[Date] <> agreement[Execution Date] active again, I attempted your REMOVEFILTERS suggestion but didn't make the slighest difference. The line graph's number remained the same.
Just to add. If a date dimension table and another table have more than one relationship present, I tend to include USERELATIONSHIP even for the active relationship.
Do you have any furthers ideas? Thanks.
D_PBI - yes, if you make all relationships between dimDate and agreement inactive, you will need to add a USERELATIONSHIP clause in every measure that will specify which one it should use. This will work with cumulative measures too. I can only assume you did not do this, so this is why allow your other charts broke.
Also USERELATIONSHIP only works on inactive relationships, so there is no need to use it in a measure that follows an active relationship.
As a suggestion, if you can't work out why USERELATIONSHIP is not working for you, perhaps it would be better to use two date tables. One for each date and both connected through Active relationships.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!