Relate tables in Power Pivot without using relations

RELATED function in Power Pivot is equivalent to  common Vlookup/Hlookup function in pivot table. But the most important factor to note that before using RELATED function in Power Pivot one must create a relationship between fields containing lookup values in both tables.

 

Here we are using 3 tables  from Database AdventureWorkks2008r2

Ø  Product

Ø  ProductCategory

Ø  ProductSubCategory

Now  create two calculated columns in the Product table, and then you hide the product category and subcategory tables from the model. First, create a calculated column in the Product table with these DAX expressions:

 

ProductCategory=Related(ProductCategory[Name])

ProductSubCategory=Related(ProductSubCategory[Name])

 

 

 

At this point there is no need to show ProductCategory and ProductSubCategory  tables in Pivottable  because their content is now  available in Product table. For this reason it is better to hide them from data model. To do so select table in Diagram view, right-click it and choose Hide from Client tools