Forum Discussion

RJ's avatar
RJ
Icon for Resolver II rankResolver II
11 years ago

Linking 2 or more dates to one Master Calendar (USERELATIONSHIP)

The master calendar is created as per this thread   http://community.powerbi.com/t5/Desktop/Possible-Tip-Trick-Dynamic-Date-Dimension-Table/m-p/8795#M1543   But sometimes there are two dates like...
Show and Tell
Tips and Tricks
alexchseng's avatar
alexchseng
Regular Visitor
10 years ago

Hi Anonymous, thanks for the hint. I found out that those 6 anamolies were due to the time stamps. Because I have used DateTable = CALENDAR(MINX('Application','Application'[Application Received Date]), TODAY()) to generate the Date table, the default timestamp is 12:00:00 AM. Hence, any row in the Offer Sent Date without 12am could not be matched.

 

Is there a way to force USERELATIONSHIP() to only match on the date portion and not the time?

 

Anonymous's avatar
Anonymous
Not applicable
10 years ago

I don't think so. The relationship exists on the tables themselves, not the particular measure being executed on them. I recommend separating date and time into separate columns. If you need a full date/timestamp then perhaps just make a custom column containing only the date from that, and use that to create the relationship.

  • alexchseng's avatar
    alexchseng
    Regular Visitor
    10 years ago

    Thanks for the suggestions. I have created a custom column containing only the date part, and am using that to create the relationship. Everything is working as expected now. 

    • BKnecht's avatar
      BKnecht
      Icon for Kudo Kingpin rankKudo Kingpin
      10 years ago

      I have been having the same exact problem, but the changing of the Date columns to "Date" instead of "Date/Time" didn't work (after I read this post I was confident that would fix it) - I'm getting a (BLANK) when I create the measure I'm looking for. 

       

      I have an inactive relationship between the columns, and my measure looks like: 

       

      New Oppys Created = CALCULATE(SUM(Opportunity[Annual_NCVI__c]),Opportunity[New_Renew__c]="New",USERELATIONSHIP(Opportunity[CreatedDate],'Date'[Date]))

       

       

      Any ideas? This is driving me nuts and I can't figure out what's wrong, everything looks like it should to me. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        BKnecht Does it help if you change that middle argument to a full FILTER statement?

         

        New Oppys Created = CALCULATE(

        SUM(Opportunity[Annual_NCVI__c]),

        FILTER(Opportunity, Opportunity[New_Renew__c]="New"),

        USERELATIONSHIP(Opportunity[CreatedDate],'Date'[Date])

        )

         

        Also as previously suggested make sure that your date table's range actually covers all possibilities in the Opportunity table.