Microsoft Office Tutorials and References
In Depth Information
Although the solution of the first point is straightforward (it is enough to change the name of
the columns as displayed by PowerPivot in the PowerPivot window), the second one is much
more interesting because it lets us introduce a very simple yet powerful DAX formula: RELATED.
You are now going to remove the tables ProductCategory and ProductSubcategory from the
field list in the PivotTable editor, replacing them with two new columns in the Product table,
named Category and Subcategory. Moreover, in doing this, you will solve both the points
The problem you need to face is that the original Product table does not contain the textual
description of category or subcategory, it only contains the ProductSubcategoryID, which is
a technical column used to create the relationship with the ProductSubcategory table. You
definitely need a way to create a calculated column in a table that contains the value of a
column in another table, following a relationship. This is exactly what the RELATED function
has been built for.
The RELATED function returns the value of a column from another table if it has a valid
relationship with the current one. You can define two new columns inside the Product table
using these formulas, as shown in Table 2-1.
TABLE 2-1 using the RELATED function.
The SubCategory calculated column contains the value of the Name column in the
ProductSubcategory table, while the Category calculated column contains the name
of the category, taken from the ProductCategory table.
Note We do not need to worry about the fact that the relationship between Products and
ProductCategory is a chained one, which makes it necessary for PowerPivot to follow two
relationship steps to gather the correct category value. PowerPivot already knows about the existence of
chained relationships and handles this complexity by itself.
This simple definition leads to a much better user experience because now you see two new
columns inside the Products table that hold the value of the category and subcategory. So
you can safely hide all the columns in the lookup tables (which, in turn, makes both tables
disappear from the field list). The report looks like the one in Figure 2-28.