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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors