DAX and relationships

In one of the earlier recipes was discussed about Data model, tables that we’re adding to it and relationships between them. This relationships are not very useful when when we’re writing DAX expressions. If we wan to use, as a formula argument, column from related column you should somehow emphasize it. This is done by using functions RELATED and RELATEDTABLE, and about which you can read more in the following text …

Let’s see how to use them through a practical example! Let’s open window of Data model (Manage option within PowerPivot ribbon) and choose the table Transakcije (transactions). This table is related to the tables Artikli (Items) and Objekti (Objects), and it is used to record transactions- turnover in retail stores. In this table we have a column KOL in which is recorded the quantity sold, and it is necessary to create a calculated column that will contain the amount of sales, as product volumes and selling prices (KOL*PC). Here’s how the formula should look like:

=[KOL]*Artikli[PC]

But if we would enter such formula Excel should show an error, although there is a relationship between these two tables. Yet, Excel can not see this within DAX expression. To avoid this error we should use function RELATED in a formula:

=[KOL]*RELATED(Artikli[PC])

RELATED function has an argument the column name of related table. If you enter a formula in this way, Excel will not report an error. Now, it would be good to change the name of the calculated column in IZNOS (Amount) and we will be able to use it as a reporting dimension in PowerPivot table.

If we are to create the expression that shows number of rows in the related table, we will use the function RELATEDTABLE. Let’s create a measure:

BrojArtikala:=COUNTROWS(RELATEDTABLE(Artikli))

As you can conclude, function RELATEDTABLE has also only one argument, which is a table name. The result is number of records within given table.