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?