INDEX MATCH MATCH equivalent in Power Pivot DAX

Jay Man

New Member
Joined
Oct 6, 2014
Messages
6
Hi,

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

Table 1 (source/raw data):

CategoryType1Type2
A12
B34
C56

<tbody>
</tbody>

Table 2 (lookup):

CategoryTypeLookup Result?
AType22
CType15

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

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Oct 6, 2014
Messages
6
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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Oct 6, 2014
Messages
6
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

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,986
Messages
5,526,065
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top