Lookup Text Value

[swingr45]

I have what (I think) should be a very simple problem to solve. Still just getting my feet wet with Powerpivot so forgive me...

I have 2 tables, linked by "Entry No."

The first table has Global Dimension 1, Global Dimension 2, Global Dimension 3, Global Dimension 4 fields. The second table has a single "Dimension Value Code" field, with another field denoting the "Dimension Code" which corresponds to Global 1,2,3,4... Using another post, I learned how to bring in the Text Values of the GD1...4

=IF(COUNTROWS(VALUES(Table[TextColumn])) > 1, BLANK(), VALUES(Table[TextColumn]))

Now I need to bring in the "Dimension Value Code" from the other table so that I can compare them...

Table1:

Entry No	GD1	GD2	GD3	GD4
200000	CI	LG	OFC	COACH

<tbody>
</tbody>

Table2:

Entry No	Dimension Code	Dimension Value Code
200000	COMPANY	CI
200000	DEPART	LG
200000	BILLABLE	BL

<tbody>
</tbody>

Outcome:

Entry No	GD1	GD2	GD3	GD4	COMPANY	DEPART	REGION	PRODUCT
200000	CI	LG	OFC	COACH	CI	LG

<tbody>
</tbody>

I thought that this formula would work, but I receive an error saying it is invalid:

LOOKUPVALUE('Table2'[Dimension Value Code],'Table2'[Dimension Code], "COMPANY")

Thanks in advance for the help!

 

[scottsen]

Are you doing this in a calc column? (or a measure / calculated field?)
Can you give us the full error msg?

Just guessing... but maybe you need to also include the entry no in your lookup value so that you get back a unique row?

That, or you really did forget the = at the beginning

 

[swingr45]

I'm doing it as a Calculated Field. I wish I had simply forgotten the =, but it is in there, just missed it on the paste.
First I received this error.

After closing and re-opening, the calculated field is available, but when I try to use it, I receive the following error.

 

[scottsen]

Ya, pretty sure that is the error you get when you have not uniquely identified the row. (There are multiple rows with Dimension Code = "Company").

 

[swingr45]

Correct. There are going to be lots of Entry No's with a "COMPANY", a "DEPART, etc... So I thought that in the Pivottable, if I selected Entry No as the Rows, the Calculated Field would factor that in... is that incorrect? Each Entry No. will have at max, 1 of any particular Dimension Code.

to give an expanded snapshot of my previous one:

Entry No	Dimension Code	Dimension Value Code
200000	COMPANY	CI
200000	DEPART	LG
200000	BILLABLE	BL
200001	COMPANY	CS
200001	DEPART	IT
200001	PRODUCT	COACH
200002	COMPANY	CS
200002	DEPART	IT
200002	PRODUCT	SCAN
200003	COMPANY	LS
200003	DEPART	LG
200003	PRODUCT	COACH
200004	COMPANY	LS
200004	DEPART	IT
200004	BILLABLE	NB

<tbody>
</tbody>

 

[scottsen]

I admit to not finishing my first cup of coffee yet, so I am not SUPER sure what you are trying to do. Do these two tables have a relationship?

Maybe something like this would work for you?
=LOOKUPVALUE('Table1'[Dimension Value Code],'Table1'[Dimension Code], "COMPANY", Table1[Entry No], MAX(Table2[Entry No]))

 

[swingr45]

Thank you! Worked perfectly. Hopefully I can start figuring these formulas out myself.