Forum Discussion

jelleschuurman's avatar
jelleschuurman
Helper I
5 years ago

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

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...
Show and Tell
v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

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.