Get row ID of related table based on Date

[RaffaeleCostanz]

I have the following situation:

Person:

- ID

- Full Name

House:

- ID

- Name

- Type

- Purchase Date

A Person can have one or more Houses, so it's a one to many.

For every Person I need to retrieve the first House they bought, so the ID of the first House based on Purchase Date, actually I also need the Name of the House and the Type (so other data of the house, not only the ID).

Right now, I retrieved the first Purchase Date for every Person with this DAX formula in a Calculated Column:

FirstPurchaseDate =
CALCULATE(
MIN('House'[PurchaseDate]),
FILTER(
ALL('House'),
'House'[Person ID] = EARLIER(Person[ID])
)
)

It works, but as I said I need also the Type of the House, the Name, etc.

Can you help me? Is a Calculated Column the right solution? Do I have to create an other table by summarizing it? If yes how?

Thank you!

[RaffaeleCostanz]

I came to the conclusion that for what I need is better to create a new summarized table.

Thank you for your contribution, you helped me a lot!

[RaffaeleCostanz]

Thank you very much for your answer, it works!

The thing is that I have to bring House Name and Type to an other Entity, Payment.

Payment

- ID

- Person ID

- Payment Date

It's a one to many, a Person can have on or more Payments, but I have to do a calculation based on Payment Date.

For example: take all the payments of [houses purchased in the 2020], where the payment has been made after the purchase date. So I think I need it as column, otherwise I cannot bring the measure.

Do you agree? Thank you

[selimovd · Accepted solution]

Hey @RaffaeleCostanz ,

that depends on what you want to do. I personally would use a measure, as it gives you more flexibility. For example if later you want to slice for a limited period of time or a special type of house. A calculated column won't change anymore.

The following approach should work:

First House =
-- get the houses of the currently selected person
VAR vRelatedHouse = RELATEDTABLE ( House )
RETURN
-- return the house name with the earliest purchase date
    CALCULATE (
        MAX ( House[Name] ),
        House[Purchase Date] = MINX ( vRelatedHouse, House[Purchase Date] )
    )

This then returns always the value for the first data:

Here you can download my demo file:

https://www.swisstransfer.com/d/4ffa15ca-8e95-4319-87a9-1fa8a76074c4

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic