Forum Discussion
USERELATIONSHIP Issue
Hello,
I'm having an issue diagnosing where the issues lies for this. But essentially my measures using USERELATIONSHIP aren't giving the correct values.
Take these two measures
CollectionActionEventTimeuse = CALCULATE ( MAX ( 'ActionEvent'[Created] ), USERELATIONSHIP( 'Action'[CollectionActionEventKey], 'ActionEvent'[ActionEventKey] ) )
CollectionActionEventTimeTreatas = VAR BookingSlotKey = SELECTEDVALUE ( 'Action'[CollectionActionEventKey] ) RETURN CALCULATE ( MAX ( 'ActionEvent'[Created] ), TREATAS ( { BookingSlotKey }, 'ActionEvent'[ActionEventKey] ) )
conceptually they should be making the same calculation (under the hood i know its calculated differently).
However the TREATAS variant is calculating the correct value whilst the USERRELATIONSHIP variant is not.
Below is one example from DAX studio. 24/07/2026 12:13:26 pm is the correct value, the USERELATIONSHIP variant is pulling back the value from a different row (i don't know how, only way is if it was using the ACTIVE relationship, which it shouldn't based on the documentation on how USERELATIONSHIP should be working).
Example output from DAX Studio
ActionId: AAA172-2d186cbb63e84381bbf3f20e2e7e25b0
CollectionActionEventKey: 3737987690717871021
TREATAS result: 24/07/2026 12:13:26 pm
USERELATIONSHIP result: 24/07/2026 1:33:53 pm
When I test the relationships in SQL everything looks good, the keys are distinct with no duplicates. I can write up the equivalent joins in SQL and it works just fine. The relationships in the semantic model are set up correctly (one to many from ActionEvent to Action)
10 Replies
Apologies i did have this formatted nicely but Microsoft would not let me publish that
"
Content Not Published Your post contains invalid HTML. Remove the following invalid tags before publishing: div, li-image"I'm almost certain this is a microsoft bug. The joins succeed everywhere but the DAX measure itself
Most common cause is that USERELATIONSHIP requires relationship where it activates to the already existing inactive relationship in the model. If there is no inactive relationship defined between Action[CollectionActionEventKey] and ActionEvent[ActionEventKey] then USERELATIONSHIP will fail to apply and calculation falls back to what the active relationship path is
Ensure you have the inactive relationship exists in model view with dotted line between exactly those two columns in the correct direction with one side single and many side on ActionEvent. If it does not exist then USERELATIONSHIP will not work
Also check for ambiguous or multiple relationship paths. If there is more than one relationship path between Action and ActionEvent even indirectly through other tables then USERELATIONSHIP can behave differently
If CollectionActionEventKey and ActionEventKey are different data types like one is text and other one is whole number DAX relationship can fail to filter correctly
Yes i have the inactive relationship defined.
There is no intermediary tables between Action and Actionevent there is 1 active relationship and then about a dozen inactive relationships.both types are whole numbers 64 bit integers
Hi Oliver_Buchanan ,
- Check cross filter direction of 'Action'[CollectionActionEventKey] and 'ActionEvent'[ActionEventKey]
- If its not possible to change it permanently, then change cross filter direction using below DAX example.
CollectionActionEventTimeuse = CALCULATE ( MAX ( 'ActionEvent'[Created] ), CROSSFILTER ( 'Action'[CollectionActionEventKey], 'ActionEvent'[ActionEventKey], BOTH ) )I have given example of both cross filter direction you can change and try accordingly.
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Thanks for the suggestion, i had tried changing the relationship to both ways already, i hadn't tried using crossfilter in the measure itself.
Unfortunately neither of these methods fix the issue.
The CollectionActionEventTime column in the 'Action' table below is the measure using TREATAS and it is pulling the correct value from the ActionEventTable.The CollectionActionEventTimeuse in the below screenshot is using your suggested measure, but as you can see its still producing the same result it has this whole time. Which is the MAX from the whole set. This is the value it should produce when using the active relationship, but specifically USERELATIONSHIP should ignore the active relationship.
FYI ActionEventKey is distinct for those rows, PowerBi in the visual just struggles to show the full 64 bit integer value. ie see below screenshot
Praful_Potphode Hey Praful, did you get a chance to see my reply?
Hi,
Please share the download link of the PBI file and show the problem there clearly.
Hi Ashish,
This is running off a fabric semantic model, not in import mode.
