Forum Discussion

jelleschuurman's avatar
jelleschuurman
Icon for Helper I rankHelper 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- 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

 
 
Show and Tell

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity 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.