Create a Relationship Between Tables

[shanekenny]

I have two tables in PowerPivot that I want to create a relationship between.


Table 1 [Project Name] and Table 2 [Project Title]

Both contain the same project names. One contains additional project names that the other does not contain.

I have created the relationship which the data model has accepted but when trying to use different column data from the tables in PowerView to create a pivot chart, the relationship is not working.

According to the MS support pages:

Creating table relationships requires that each table has a column that contains matching values. For example, if you are relating Customers and Orders, each Order record would need to have a Customer Code or ID that resolves to a single customer.

Would really appreciate if someone explained this to me in lay terms, should the tables contain identical information or not?

Obviously the Project titles are text, does the model need a numeric value? In which case I tried to match to the Project Case numbers which are identical and that didn’t work.

Is it a case that each entry in a column must be capable of being resolved to a matching value?

Thanks
Shane

 

[scottsen]

Relationships have a direction. There is the "many" side and the "one" side of the many to one relationship. The prototypical example is with Sales and Products. Sales are your many side (your "fact" or "data" table). Products are the "one" side (the "lookup" or "dim" table). Each product would show up exactly 1 time in the products lookup table. its the "product master". But, obviously, each product will show up many times in your Sales table, because you sell the same product... over and over.

That is pretty much how you want it. You want one lookup table that has EVERY project, exactly once. Then you would relate that to other tables where the projects may show up many times.

 

[shanekenny]

Thanks Scottsen, appreciate that explanation, by far the best I have got. It actually seems to be an issue with the KPI's, they dont like being used in a chart / table with the relationship, so I am prompted to create a relationship (even though one exists).. thanks again