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:

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 TwitterFollow me on FaceBook