# INDEX MATCH MATCH equivalent in Power Pivot DAX



## Jay Man

Hi,

I'd like to do a 2 dimensional lookup between 2 tables in my Power Pivot  data model:

Table 1 (source/raw data):


CategoryType1Type2A12B34C56

<tbody>

</tbody>
Table 2 (lookup):


CategoryType*Lookup Result?*AType22CType15

<tbody>

</tbody>
Question....how do I get the values in Table 2's 'Lookup Result' column? ie - what is the DAX syntax to do this as a calculated column in table 2 without transforming/rearranging the raw data in table 1? Normally i'd use 2 nested MATCHs within an INDEX in Excel.....

Thanks in advance guys!


----------



## scottsen

My fear that you are trying to force the hot-new-freshness of Power Pivot look like the old n busted ways... is growing.  

Anyway...
* in your power pivot model are these two tables *related *on the Category?
* How many of these Type columns do you actually have?  Asking to know if hard-coding some IF() is gonna work out...
* Why do you want the calc column on the lookup* table, instead of on the data table?


* Note: the term "lookup table" has actual meaning in Power Pivot, not sure you intended that or not.


----------



## Jay Man

Hi ScottSen

Thanks for replying on this thread so quickly! My responses below...

* in your power pivot model are these two tables *related *on the Category?

Yes..they are both referring to the same category

* How many of these Type columns do you actually have?  Asking to know if hard-coding some IF() is gonna work out...

There are many type columns....this is very simplified version of the real data!  . Unfortunately IF statements will not hack it.

* Why do you want the calc column on the lookup* table, instead of on the data table?

Table 2 is used to drive further calculations. Lets assume Table 1 cant be changed/transformed/transposed in any way. 

* Note: the term "lookup table" has actual meaning in Power Pivot, not sure you intended that or not.[/QUOTE]

whoops I didnt mean to. By 'lookup table' I meant  that Table 2 _looks up_ values from Table 1.


----------



## scottsen

Again, the term "related" has actual meeting in Power Pivot.  Columsn in tables can have "relationships" that magically do things.  

Without IF() or tranforming the data (I'd suggest Power Query personally), I think you might be in trouble actually...


----------



## Jay Man

scottsen said:


> Again, the term "related" has actual meeting in Power Pivot.  Columsn in tables can have "relationships" that magically do things.
> 
> Without IF() or tranforming the data (I'd suggest Power Query personally), I think you might be in trouble actually...



...Thanks for investigating anyway. I'm keen to avoid putting a 'square into a round hole' here...but am enthusiastic to use Powerpivot great in-memory efficiency and speed in looking up large files.  Do you know why this 'index match equivalent' functionality isn't available in Powerpivot? Is it simply not designed for such operations?


----------



## scottsen

I can only tell you that I have worked on dozens of models and never missed it    I would need to know more about your problem to really advise.

If you really have lots of the Type1,Type2,...  I would certainly unpivot for performance reasons (lots of columns are not your friend in a fact/data table), which also makes your problem much easier.


----------

