Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JamesMF1982
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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.

View solution in original post

2 REPLIES 2
NanDeb
Helper I
Helper I

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
Super User
Super User

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.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.