DAX: The RELATED() Function

Introduction

Today, a blog about the RELATED() function of DAX (Data Analysis Expressions) in PowerPivot. The RELATED function is a function that combines a field from another table into the table where you enter this this expression. In this blogpost I'll use an example of the Contoso retail example, that you can download from the Microsoft site.

Data model

Below is the datamodel shown, where there is a 1:n relation between DimProduct -> DimProductSubCategory -> ProductCategory. So one product can have one ProductSubCategory and multiple ProductSubCategories can belong to multiple Products.These relationships are called chained relationships because they form a chain that you can follow from the beginning
to the end in order to relate many tables. And in the multidimensional (and tabular) world we recognize this as a hierarchy construction.

In the screenshot above you can see fields ProductCategory and ProductSubCategory in the DimProduct table and these are not actually native fields but related columns from the DimProductSubCategory and ProductCategory.

Even if the original data model of Contose Sales has two distinct tables for category and
subcategory (which is correct for a standard database system), the data model is much easier
to query if we integrate certain fields in the DimProductSubcategory and ProductCategory in de DimProduct dimension and hide the DimProductCategory and ProductCategory.

RELATED() function

The RELATED() function relates columns from other tables within the table where you use the RELATED() function. Below you can see the expression that is used in the Dim Product table.

And this is the expression:

=RELATED(ProductCategory[ProductCategoryName])

You can only use the RELATED() function  when a relationship is defined.

Conclusion

This is a great function for combining data from multiple tables..

Greetz,

Hennie

Installing PowerBI Desktop V2

Introduction

This week PowerBI Desktop (formerly known as Power BI designer) is released by Microsoft, in case you haven't noticed by now;-) this Desktop version is the Self Service BI stack and is the counterpart of the more IT related stack SSIS, SSRS and SSAS. This new BI suite (PowerBI Desktop) is combination of PowerQuery, PowerPivot, PowerMap and PowerView. One of  the reasons that PowerBI Desktop is a separate solution, is because of the slow release cycles of the Power tooling in Microsoft Office (because of the huge install base of Office extensive testing has to be done in order to release a new version). With PowerBI Desktop faster new versions of the Power tooling is possible.

In this blogpost I'll describe the intallation process of PowerBI Desktop. In later blogposts I'll dive deeper into the functionality of PowerBI Desktop and DAX.

Installation

1. First download the PowerBI Desktop to your system. The start the PBIDesktop_x64.msi (In my case). Then click on Run to start the installation process.

Wait until the initialisation process has ended.

2. Click on Next to begin the installation process.

3. Accept and click on Next

4. Determine the installation path and enter Next

5. Click on the Install button.

The installation process continues with installing the files.

6. The installationprocess has ended and now you have to click on the Finish button.

PowerBI is starting up now

The starting window is opened and ready for usage.

Conclusion

First impression of Power BI Desktop are great. Looking forward to work with PowerBI Desktop
in projects....

Greetz,
Hennie