Forum Discussion

awff's avatar
awff
Icon for Helper III rankHelper III
4 years ago
Solved

USERELATIONSHIP with date not working as expected

Hello,

 

I'm trying to do a very simple count measure with USERELATIONSHIP with my date table and column and for some reason the values are not showing. 

 

There is 100% certainty that there is a many to one relationship between these two tables. I've ensured the data types are correct. I've got plenty of other measures with USERELATIONSHIP with my date table, but this particular column does not want to work...

 

Weird thing is, if i change the Opportunity[CreatedDate] column to another date column in the same table, it shows results; but obviously at the wrong date ranges.

 

 

Would anyone have any ideas??

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    It does look like your Dates table is not covering the range of Opportunity[Created Date].  Please confirm that.  Also, make sure your Created Date column is type Date (not DateTime); if DateTime, it needs to be changed in the query editor to type Date.

     

    Pat

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    awff can you provide a screenshot of the columns involved, their data types, and their relationships?  It sounds like you're checking the right things, but without any more information it's somewhat impossible to speculate what could be causing your issue.

    • awff's avatar
      awff
      Icon for Helper III rankHelper III
      4 years ago

      Thanks, 

       

      Relationship (sorry for the mess):

       

      Type:

       

      Date Dim:

       

      Could it be because i'm using the same table (OPPORTUNITY[CreatedDate]) to referencethe date table?

       

      edit: changing

      _Dates = Calendar(Date(year(min(Opportunity[CreatedDate])),7,1),(Date(year(max('_fact (Products)'[End_Date__c])),6,30)))

      to

      _Dates = Calendar(Date(2008,7,1),(Date(year(max('_fact (Products)'[End_Date__c])),6,30)))

       

      did not do anything.

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        4 years ago

        It does look like your Dates table is not covering the range of Opportunity[Created Date].  Please confirm that.  Also, make sure your Created Date column is type Date (not DateTime); if DateTime, it needs to be changed in the query editor to type Date.

         

        Pat

  • jpperez's avatar
    jpperez
    Frequent Visitor
    4 years ago

    Have you made sure that both date fields are in Date Format in PowerQuery? I have this issue when one of the dates is in Date/Time format, thus it keeps trying to match only records that ocurred at the same time 12:00