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):

Category	Type1	Type2
A	1	2
B	3	4
C	5	6

<tbody>
</tbody>

Table 2 (lookup):

Category	Type	Lookup Result?
A	Type2	2
C	Type1	5

<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]

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.