USERELATIONSHIP problems. Possibly due differing date formats (DD/MM/YYYY and DD/MM/YYYY HH:MM:SS)

[JamesMF1982]

Hi there,

I was hoping for some advice on an inactive connection I currently have between two tables (Calendar and EnquiryDetails). Calendar has a number of fields relating to dates with an active connection between CalendarDate and LeadDate. Both of which are in DD/MM/YYYY format.

Other inactive connections have been set up between the CalendarDate and other fields in EnquiryDetails, in particular, EnquiryDebameRejected which is in the format DD/MM/YYYY HH:MM:SS. I've created a USERELATIONSHIP in a measure between the two with the following:

CRM_Rejected_Enquiries = calculate(COUNTROWS(ENQUIRYDETAILS),ENQUIRYDETAILS[EnquiryCurrentStageID] = {4}, USERELATIONSHIP('CALENDAR'[CalendarDate],ENQUIRYDETAILS[EnquiryBecameRejected]))

However, when I select anything in the date slicer no information appears as it should. Am I right in thinking that the difference in date formats is causing this issue? I don't have access to change the field to DD/MM/YYYY so is there a way I can change and link this without getting a new column added in? Or if this is not the issue can anyone advise where I am going wrong?

[NanDeb]

USERELATIONSHIP takes the "many" side of the inactive relationship as the first argument and the "one" side as the second. 

Then you would also need both columns to be the same type. Can you convert the daytime to a date? If you need to retain the timestamp I would duplicate the column, convert that to a date type and then adjust my inactive relationship to use this new column. 

[FreemanZ · Accepted solution]

Hi @JamesMF1982 

1) try this:

CRM_Rejected_Enquiries = 

calculate(

    COUNTROWS(ENQUIRYDETAILS),

    ENQUIRYDETAILS[EnquiryCurrentStageID] = 4,

    USERELATIONSHIP(

        'CALENDAR'[CalendarDate],

        ENQUIRYDETAILS[EnquiryBecameRejected]

    )

)

not sure if the {} is necessary. 

2)Date with HH:MM:SS could lead to filtering failures, as they are not exactly the same. Changing the format doesn't help as the value is still the old. Would suggest you  to add new pure date column in Power Query.