DAX Functions for Querying BISM tabular models
Hello!
We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.
This post comes as a supplementary addition to the earlier post which describes how to configure SSAS Tabular Project.
Consider the SQL as stated below:
select
DimProduct.EnglishProductName
,DimProductCategory.EnglishProductCategoryName
,DimProductSubcategory.EnglishProductSubcategoryName
from dbo.DimProduct
inner join dbo.DimProductSubcategory
on DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryK inner join DimProductCategory
on DimProductSubcategory.ProductCategoryKey
=DimProductCategory.ProductCategoryKey
This SQL will return the product name, the category name and the sub category name because there is a relationshipbetween these three entities.
Now in DAX, we have a similar feature or rather a function available to the data modeler.
Name of function: RELATED {RELATED(<qualified_column_name>)}
Description: This function can be used to return or derive a column from an external table provided there exists a relationship between the said data entities.
I will be using the same project solution that we created here. If not, please create one and follow the example below:
Now, the product tabular data set does not have a product category name and a product sub category name. This can be achieved in two steps.
Step 1: Create or Insert a new column and rename it as “Product Category Name”
Step 2: In the formula expression bar enter this “=RELATED(‘Product Category'[Product Category Name])”
On successful execution of this expression, the product table in our tabular model will now have a new column.
This is only possible because the tables have a relationship between them.
Relationships can be managed (Create, edit or delete) by clicking on the Table Menu and select the Manage Relationship option.
That’s all. Happy Learning!
Regards
Raunak Jhawar
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
