Forum Discussion

JamesMF1982's avatar
JamesMF1982
Frequent Visitor
3 years ago
Solved

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 activ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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.
NanDeb's avatar
NanDeb
Helper I
3 years ago

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.