Forum Discussion

DominicBrien's avatar
DominicBrien
Frequent Visitor
9 years ago
Solved

Cross table Datediff

Hello, Lets say I have a person table where I can find date of birth for every person in the system. I also have an event table where I can find the date an event occured at and another field that ...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    DominicBrien

     

    What’s the warning message when you try including “RELATED” in the calculate column expression?

    If Event and Contacts table has relationship with the PersonID key, you should be able to get the desired result with following column formula.

     

    Age = 
IF (
    ISERROR ( DATEDIFF ( Event[Date], RELATED ( Contacts[DateOfBirth] ), YEAR ) ),
    DATEDIFF ( RELATED ( Contacts[DateOfBirth] ), Event[Date], YEAR )
)

     

    Best Regards,

    Herbert

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

DominicBrien

 

What’s the warning message when you try including “RELATED” in the calculate column expression?

If Event and Contacts table has relationship with the PersonID key, you should be able to get the desired result with following column formula.

 

Age = 
IF (
    ISERROR ( DATEDIFF ( Event[Date], RELATED ( Contacts[DateOfBirth] ), YEAR ) ),
    DATEDIFF ( RELATED ( Contacts[DateOfBirth] ), Event[Date], YEAR )
)

 

Best Regards,

Herbert

  • GHasan's avatar
    GHasan
    New Member
    7 years ago

    What happens if the two tables only have an indirect relationship between them? like -------- (dotted) lines? 

    • S184019's avatar
      S184019
      Advocate III
      6 years ago

      What about if there is no relationship at all.  For example, how do I do a datediff between two completely independent tables?  Let's say I want to make sure the tables refreshed on the same day.  I would put a date in each table (sql) then I want to compare the dates from each table.  

       

      I am open to ideas, please?

  • DominicBrien's avatar
    DominicBrien
    Frequent Visitor
    9 years ago

     

    Hello thanks for the help,

    In fact I assumed it was not working because the "RELATED" function and the field appeared with both underlined with red squedlies.

     

    Buth when I use the column it does hold the right value.

     

    What are the squedlies supposed to tell me?

    I didnot mention but I am in direct query so I aparently cannot use ISERROR...

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      9 years ago

      DominicBrien

       

      If you use it as measure, it cannot determin a signle value for a column. So you’ll see red underlines. You can take a look at this article which explains the difference between calculated column and measure.

      To make ISERROR to be supported in DirectQuery mode, you can try to enable following option in File - Options.

       

      Or you can try to update the column expression as below.

      Age = 
IF (
    Event[Date] <= RELATED ( Contacts[DateOfBirth] ),
    DATEDIFF ( Event[Date], RELATED ( Contacts[DateOfBirth] ), YEAR ),
    DATEDIFF ( RELATED ( Contacts[DateOfBirth] ), Event[Date], YEAR )
)

       

      Best Regards,

      Herbert