Microsoft Office Tutorials and References
In Depth Information
Now you need to face another simple issue: the relationship you want to create is based on
two columns (country and weight) although, in PowerPivot, you can use only one column to
define a relationship. This problem is easily solved by creating new calculated columns in all
three tables with appropriate formulas that concatenate the country to the weight. The formula
is very easy:
CountryWeight = PriceList[Country] & FORMAT( PriceList[Weight], "00" )
You can call those columns CountryWeight, and you can see in Figure 10-31 the CountryWeight
column for the PriceList table.
FIguRE 10-31 The PriceList table with the new CountryWeight column.
Using the CountryWeight column, you can create the relationships for the three tables and
create the many-to-many data model. You can see the three relationships in Figure 10-32.
FIguRE 10-32 The relationship you need for the many-to-many data model.