USERELATIONSHIP is not working - What am I doing wrong?

[HelenBrunyee]

Hello Everyone,

I was hoping someone could help me with this - I've been banging my head against the desk with this one, I can't seem to figure it out!

Context:

I have a date table, linked to my data table with an active relationship to 'Issued Date'. There are also a couple more Date columns, for which I have also created two more inactive relationships to the date table for. One of these is 'Debt Recovery Sent Date'.

Problem:

When using the USERELATIONSHIP function to calculate my query (how many issued tickets were sent to debt recovery each month), it doesn't seem to want to link to the date table, and just throws back blank date in my table.

The DAX meaure I have used is:

Total sent to Debt Recovery= CALCULATE([Total Issued], USERELATIONSHIP('Date Table'[Date],R_Issued[Debt_Recovery_Sent_Date]))

[Total Issued] = COUNTROWS('R_Issued')

I have also tried switching the USERELATIONSHIP part round, as such:

Total sent to Debt Recovery= CALCULATE([Total Issued], USERELATIONSHIP(R_Issued[Debt_Recovery_Sent_Date],'Date Table'[Date],))

Both produce the following, the 'Date' column is the Date column in the Date table (shown here with and without the Total Issued measure):

For reference, the relationships look like this:

I'm at a loss at what to try next, so any help would be much appreicated!

Thank you! 

Helen

[ValtteriN · Accepted solution]

Hi,

This kind of behaviour appears when the relationship is not actually functioning. I recommend checking testing the data types and formatting between the columns. E.g. Here I formatted date column to Text and the relationship broke down:

Back to date format:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[HelenBrunyee]

Aha! That seemed to do it! My Debt Recovery Sent Date column was set to Date/Time in the Query Editor, so I changed it to Date & this seemed to do the trick

Thank you so much for your help - lifesaver! 😄

Best wishes,

Helen