# Lookup With Multiple Criteria in PowerPivot



## rksample (Feb 10, 2015)

Hi Gurus,

I have a query to solve in Power Pivot. Seems like a small formula in Excel, I am unable to crack it on Power Pivot.

I have 2 tables.
Table1:       ID, DateIn, DateOut, Value
Table2:       ID, DateAsOn

There are multiple values for the Same ID, but falls in different DateRange (DateIn - DateOut)

I need a DAX formula which can lookup in the Table1 and fetch me the Value specific to an ID on that Particular Date.

Something like this criteria:
1. Table1.ID = Table2.ID
2. Table1.DateIn <= Table2.DateAsOn
3. Table1.DateOut >= Table2.DateAsOn

Please help me on this trick.

Thank you,
Ravi.


----------



## scottsen (Feb 10, 2015)

It's going to depend a bit on where you want to use this, if there are dupes, and if you want a measure vs calculated column, but... it will probably look something like...

=CALCULATE(MIN(Table1[Value]), FILTER(Table1, Table1.Id = MIN(Table2.ID) && Table1.DateIn <= MIN(Table2.DataAsOn) && Table1.DateOut >= MIN(Table2.DateAsOn))


----------



## rksample (Feb 10, 2015)

Thanks Scottsen for formula. I shall try this first thing morning.

And to answer you, yes there were duplicates (ID-Value combinations). Though ID-Value-DateIn-DateOut combination will be unique  So for any specific Date an ID as one specific value which is to be Fetched.

Thanks again, will keep you posted on the result.

Cheers,
Ravi.


----------

