POWERPIVOT lookup based on 2 columns/conditions

Hello,

I hope the tables below can give you an idea of what I'd like to accomplish.

Basically, given 2 tables, the transaction table and lookup table, In POWERPIVOT, I'd like to create a column where in the 'Transaction Table', I can do a function where I can do a lookup and return the value from another table.

So how can I go about doing it?

Transaction Table

GL	Functional Area
1	Z000
1	Z001
1	Z002
1	Z003
1	Z003
1	Z004
2	Z000
2	Z001
2	Z002
2	Z003
2	Z003
2	Z004

Lookup Table

Group	GL	Functional Area
Grocery Expense	1	Z000, Z001
Restaurant Expense	1	Z002
Entertainment Expense	1	Z003
Other Expense	1	Z004
Mortgage Expense	2	Z000, Z001
Utilities Expense	2	Z002
Interest Expense	2	Z003
Amortization/Depreciation	2	Z004

Desired Result in Transaction Table

GL	Functional Area	Group
1	Z000	Grocery Expense
1	Z001	Grocery Expense
1	Z002	Restaurant Expense
1	Z003	Entertainment Expense
1	Z003	Entertainment Expense
1	Z004	Other Expense
2	Z000	Mortgage Expense
2	Z001	Mortgage Expense
2	Z002	Utilities Expense
2	Z003	Interest Expense
2	Z003	Interest Expense
2	Z004	Amortization/Depreciation

Crosspost: http://www.excelguru.ca/forums/showt...y-relationship

Ideally, LU table should have relationship with Transaction table.

Without it DAX becomes needlessly complex and/or impossible.

In your example I'd recommend either doing Lookup in PowerQuery stage. Or restructuring your LU table like below.


Lookup Table

Group	GL	Functional Area
Grocery Expense	1	Z000
Grocery Expense	1	Z001
Restaurant Expense	1	Z002
Entertainment Expense	1	Z003
Other Expense	1	Z004
Grocery Expense	2	Z000
Grocery Expense	2	Z001
Utilities Expense	2	Z002
Interest Expense	2	Z003
Amortization/Depreciation	2	Z004

You can then add calculated column to each table ([Key]) with formula...
=[GL]&[Functional Area]

Then add relationship on that column.

Then in Transaction table add calculated column.
=RELATED(Lookup[Group])

text to column on column C

After that make a table of that data => insert => table.

After that CTRL + H find space => replace by leave blanc (on column D and further)
After that run the attached macro.
The result is on sheet Output

See the attached file.

Please Login or Register  to view this content.

PS: I don't have POWERQUERY installed

So I thought you where looking for another solution to solve your problem.

Reading your reply I probably did not understand the request.

@dluhut

Try following. LU = Lookup Table, Tran = Transaction Table

DAX_CalculatedColumn

Please Login or Register  to view this content.

Though I really wouldn't recommend this approach.

Alway good to let us know you crosspost the item.

https://www.mrexcel.com/forum/power-...ationship.html

Originally Posted by CK76

@dluhut

Try following. LU = Lookup Table, Tran = Transaction Table

DAX_CalculatedColumn

Please Login or Register  to view this content.

Though I really wouldn't recommend this approach.

Thanks CK76!!! I tried to put it on a sample that's above and it works.

However, when I try to put it onto my live data, it's giving me #ERROR value.

When I click on the 'Show Error', it says, "Calculation error in column '2017sapbw'[]: A table of multiple values was supplied where a single value was expected."

I checked on '2017sapbw'[GL Account] and '2017sapbw'[Functional Area] and they're all single value.

So may I know what went wrong?

PS: '2017sapbw' is the same as Tran in this case.

PS2: Reps up to you

Edit: I realized that it wasn't specifically the 'Trans' table that has duplicate values, but it was the LU table that has the duplicate values but different Group.