Lookupvalue with multiple results, filter the right one without unique identifier

[jelleschuurman]

Hi all,

I have an database with 2 tables. Adres and AdresHistory. When the adres changes, the old address is copied to AdresHistory with the AdresID from Adres. In AdresHistory there is also a Start- and Enddate column (they do not overlap). 

How to get the unique row AdresHistory data based on that dates? I need this unique one for a LOOKUPVALUE to get the actual Adres on a specific date. AdresHistory does not have a unique ID, but the tables are related.

The formula i'm now using:

_woonplaats =

if(Verrichting[_uitvoerDatum].[Date] >= Verrichting[_woonplaatsDatum].[Date],
LOOKUPVALUE(Adres[plaats],Adres[adresId],Verrichting[__getAdresid]),

LOOKUPVALUE(AdresHistory[plaats],AdresHistory[I'm looking for this, based on dates],Verrichting[__getAdresid]))

Thanks in advance.

Kind regards,

Jelle

[v-jingzhang]

Hi @jelleschuurman 

Not sure what's your data model like, you may refer to below measure to get a specific address during a period from AddressHistory table.

OperationAddressHistory = 
MAXX (
    FILTER (
        ALL ( AddressHistory ),
        AddressHistory[AddressId] = MAX ( Operations[AddressId] )
            && AddressHistory[Start Date] <= MAX ( Operations[OperationDate] )
            && AddressHistory[End Date] >= MAX ( Operations[OperationDate] )
    ),
    AddressHistory[Address]
)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.