Creating Relationships

[susjoe3]

I'm new to PowerPivot and am trying to create relationships within multiple tables. When I create the relationships, it allows me to do so but warns me that they cannot be created in the order requested. Does this matter? It appears to me that it does as I am missing some records from one of the table in my pivot table.

 

[masplin]

Usually this is because when it creates a relationship one of the tables must have unique records i.e. no repetititions. When you create a relationship if you put them in the boxes the wrong way round powerpivot will automatically switch them and you get this message. To check what it has done go to the powerpivot window menu bar/design/manage relationships whee it wil list your relationships. The "related lookup table" is the one that contains unqiue data and is being looked up from.

Mike

 

[susjoe3]

Thanks Mike

 

[JavierGuillen]

Susjoe3,

Also, the direction of the relationship matters (which can be seen in the diagram view which the arrow pointing at the 'lookup' table). When using DAX, filtering can propagate by default from the lookup table to the non-lookup (or base) table. Is important to keep this in mind when creating measures that use fields from multiple tables. I wrote a blog entry with a more detailed description of this behavior here:

Working with relationships in PowerPivot & SSAS BISM Tabular « Javier Guillén


Javier Guillen
Senior Consultant, Mariner
Email: javier.guillen@mariner-usa.com
PowerPivot Blog: http://javierguillen.wordpress.com
Twitter: @javiguillen