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

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