Forum Discussion
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 holds the ID of the person table to establish to whom this event is related.
I want to have a measure or columns on events that would hold the age the person was when the event occured.
This
Age = DATEDIFF(Event[Date],Contacts[DateOfBirth],YEAR)
Tells me that
A single value for column 'DateOfBirth' in table 'Contacts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
I also tried including "RELATED" in there without any success.
I find it weird as it should absolutly be able to find a single record...
Thanks
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
6 Replies
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
What happens if the two tables only have an indirect relationship between them? like -------- (dotted) lines?
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?
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...
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
With this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci9KTVfSUTLWNzTSN7Q0N1KK1YlWcsrMyQEKgoQMgKKmBmDRoPzk7NQSoLiZvpm+kYGBmVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Column1", type date}}) in #"Changed Type"and this, related on Person (many-to-one Events to People)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci1LzSsxVNJRMjTQNzTVNzIwNABy3ItS05VidXBJB+UnZ6eWIBQYISswwdCPIU1Qv1NmTg5C2hhZ2gwuHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Event = _t, Date = _t, Person = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"Date", type date}, {"Person", type text}}) in #"Changed Type"I did this in a column and it worked
Age = DATEDIFF(RELATED(People[Column1].[Date]),Events[Date].[Date],YEAR)
