Look up based on 2 seperate columns

[mdrew9]

Not sure if my title is the proper way to describe this.
I have a data table it has 2 columns, Activity and Element. I am trying to "look up" what this record is based on these values. This would be very simple if it was just activity, but its conditional first on element.
What I want to do is this
=IFERROR(VLOOKUP(E6,tblElement,2,FALSE),VLOOKUP(D6,tblActivity,2,FALSE)) In excel this works.

=IFERROR(related(tblElement[Color]),related(Activity[Color])) In Powerpivot this does not work because there is no error

I could also put this in Access as a query before bringing it into PP, so that is also an option.

Also typically in my pivot table I would just include the Color column as on of my fields, so I am calculated what each record is at each line. Not sure if there is a way around this.

 

[Mavericks334]

Create a relationship between both these tables.

and in Powerpivot use related function. it would work

 

[JavierGuillen]

Hi mdrew9,

You can achieve this type of conditional lookup in PowerPivot with a measure like the one below:

IF(
HASONEFILTER( Table1[colA]) ,
CALCULATE( VALUES(Table2[colB]) , Table1 ) &
CALCULATE( VALUES(Table3[colB]) , Table1 )
)

This assumes you have an actual relationship between your base table and your two lookups; in your case those lookups would be tblElement and tblActivity.

 

[mdrew9]

Create a relationship between both these tables.

and in Powerpivot use related function. it would work

Mavericks tblelement and tblactivity are not related they refer to 2 different types of codes.

 

[mdrew9]

I couldn't quite get that to work, but taking out the if and hasonefilter and just concatenating the two calculates together gives me what I am looking for. Any reason I wouldn't want to do this? And can I do this as a measure and not just put it as a column?

Hi mdrew9,

You can achieve this type of conditional lookup in PowerPivot with a measure like the one below:

IF(
HASONEFILTER( Table1[colA]) ,
CALCULATE( VALUES(Table2[colB]) , Table1 ) &
CALCULATE( VALUES(Table3[colB]) , Table1 )
)

This assumes you have an actual relationship between your base table and your two lookups; in your case those lookups would be tblElement and tblActivity.

 

[JavierGuillen]

as a calculated column, you can use

Related(tableA[column]) & Related(tableB[column])

as a measure you would have to use something like what I posted above.

No reason you would want to go with one or the other, except usability.

 

[mdrew9]

Upon further examination, I am getting duplicate values for those cells that are could be populated with both fields.

 

[JavierGuillen]

I see. In those cases, do you have any business rule that would prefer one over the other one?

 

[mdrew9]

I see. In those cases, do you have any business rule that would prefer one over the other one?

YES! Mirroring this function "=IFERROR(#1 VLOOKUP(E6,tblElement,2,FALSE),#2 VLOOKUP(D6,tblActivity,2,FALSE)) In excel "

each section ordered by priorty.

 

[JavierGuillen]

I see. So tblElement has priority, correct?

Check out this link - At the bottom, I discuss a solution for this type of lookup in which one element would be prioritized if found in two lookup tables.

VLOOKUP conditional flow equivalent in DAX « Javier Guillén