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.
Your suggestion of creating a data table per date relationship is something I have thought to do but I was hoping to understand why my USERELATIONSHIP isn't working. I would need to create several indivdual data tables that all feed into a single date table and it's this single date table that is used in the slicer visual on the page. The page has several line graphs which all use different relationships but I'm trying to allow the user only needing to filter one date slicer visual - if this makes sense.
Before I go down this path, I'll show you what's happening. I've remove other line graphs so to focus just on the Execution Date and Received Date relationships.
This is the model showing the relationships between the dimDate and agreement tables. The active relationship is dimDate[Date] <> agreement[Execution Date].
The measure used in the AUTM Agreements Graph is below. Notice, even though the dimDate[Date] <> agreement[Execution Date] relationship is the active one, I've used USERELATIONSHIP too (I note you state it won't be used as the relationship is active). This measure/graph is correct in its output.
The measure used for the Consultancy Agreements graph is below. This measure uses the inactive dimDate[Date] <> agreements[Received Date] relationship. This measure/graph is incorrect in its output.
Now altering the relationships to make the dimDate[Date] <> dimDate[Execution Date] inactive (so all relationships between dimDate and agreement are inactive), but leaving both measures untouched, changes the graphs to show the below.
See how the graphs have become non-cumulative totals. This shouldn't be the case - they should be cumulative.
Some questions here:
1) Why is setting the dimDate[Date] <> agreement[Execution Date] to inactive affecting the graph when the measure uses USERELATIONSHIP?
2) The original question, why isn't the dimDate[Date] <> agreement[Received Date] measure not working as expected?
Surely, this is why the USERELATIONSHIP is available, to handle requirements such as this.
Any help will be great. Thanks.
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!