Hello,

This is my first day using PowerPivot, and I'm trying to add a relationship between two tables where both tables don't have unique values, but that seems not to be supported. Here is a simple example of my tables, and the pivot result I'm trying to accomplish.

I have no clue of how to do this?!
Please help /Magnus

Hi Magnus,

this is a "many to many"-issue, you'll find lots of articles about it, i.e.: http://www.powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many/A

You should create a bridge-table that contains the unique account-numbers (or take you Chart of account if available) and then follow the instructions.

Tanks for your answer Imke,

I have tried to add my charts of account as table between these tables. So both tables mentioned above are now related to account in my accounts table. But then all rows get the total amount when showing HFMAccount and amount, as:

HFMAccount   amount
s12300           14
s12400           14

It also says "Relationships between tables may be needed".

I don't find any further instructions at your link. What else to do to solve this?

Best regards /Magnus

Excel 2010 with free PowerPivot and Power Query Add-Ins.
Compatible with Office 2013 Pro Plus.
Power Query Tables can be imported into PP Data Model directly in 2013.
With my own more expansive data base.
http://www.mediafire.com/view/cyhr9dl9cvsa1qx/03_07_15a.xlsx

Sorry, I really don't understand how this fit in with my question. I use Excel 2013, with PowerPivot, and my problem is about many to many relationship. These tables are not related in a database.

/Magnus

Hi Magnus,

make sure that your measure is defined like this:

Calculated Field 1:=CALCULATE(SUM([amount]); MappingView)

and not like this:

Calculated Field 1:=CALCULATE(SUM([amount]); ChartOfAccount)

Hello Magnus,

As far as I know, PowerPivot for Excel does not support many-to-many relationships, and you cannot simply add junction tables in PowerPivot.

Please see this article:

https://msdn.microsoft.com/en-us/library/gg399148(v=sql.110).aspx

As a workaround, we can use DAX functions to model many-to-many relationships.

http://social.technet.microsoft.com/wiki/contents/articles/22202.a-practical-example-of-how-to-handle-simple-many-to-many-relationships-in-power-pivotssas-tabular-models.aspx

Regards,

Hi, and thank you all,

I succeeded to use your calculate formula Imke. And I will continue to read about DAX etch, to learn more.

Best regards /Magnus