POWERPIVOT lookup many to many relationship && 2 columns/conditions

Anything related to PowerPivot and DAX Formuale
dluhut
Posts: 3
Joined: Thu Jun 01, 2017 1:50 am

POWERPIVOT lookup many to many relationship && 2 columns/conditions

Postby dluhut » Thu Jun 01, 2017 2:18 am

Hello,

This is my first post and also am new to POWERPIVOT/DAX.

So hopefully experts and gurus here are able to help me with my question.

I hope the image 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?

Image

dluhut
Posts: 3
Joined: Thu Jun 01, 2017 1:50 am

Re: POWERPIVOT lookup many to many relationship && 2 columns/conditions

Postby dluhut » Thu Jun 01, 2017 2:20 am

I don't know why the image is broken, but the URL is http://i.imgur.com/P3sOqtW.png

MattAllington
Posts: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: POWERPIVOT lookup many to many relationship && 2 columns/conditions

Postby MattAllington » Thu Jun 01, 2017 6:31 am

The image link doesn't work. I am not really sure what you are asking, but if I understand, you are trying to pull data from the lookup table into the transaction table asa anew calculated column. If that is what you mean, then you don't need to do this. In fact I even recommend against it. Once the 2 tables are joined, they behave as one WITHOUT having to do what you say. Just create a pivot table and use the original column from the lookup table in your pivot.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

dluhut
Posts: 3
Joined: Thu Jun 01, 2017 1:50 am

Re: POWERPIVOT lookup many to many relationship && 2 columns/conditions

Postby dluhut » Thu Jun 01, 2017 7:00 am

Please note that I don't have PowerQuery Add Ins as it's company laptop, and I tried to have it installed, but my ticket was turned down indicating that it's not 'tested' and that I need higher level approval.

This how the table looks like.

Transaction Table
GL | FA
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
GL | FA | Grouping
1 | Z000;Z001 | Grocery Expense
1 | Z002 | Restaurant Expense
1 | Z003 | Entertainment Expense
1 | Z004 | Other Expense
2 | Z000;Z001 | Mortgage Expense
2 | Z002 | Utilities Expense
2 | Z003 | Interest Expense
2 | Z004 | Amortization/Depreciation


Desired result in Transaction Table
GL | FA | Grouping
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

MattAllington
Posts: 849
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: POWERPIVOT lookup many to many relationship && 2 columns/conditions

Postby MattAllington » Thu Jun 01, 2017 7:11 am

maybe you are psychic. This is not a task for Power Pivot because the join columns are not the same. The second one needs to be reshaped so that there is 1 row for each FA. One you have done that, it looks to me like the final table, so I don't see a role for Power Pivot. If I were doing this, I would do it in Power Query.

Of course it is ridiculous for IT to not allow you to install Power Query given it is a Microsoft Addin for a Microsoft product. But I know how hard it can be.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest