Related tables

It's possible to reference other tables in the data model from within a row context via the RELATED() and RELATEDTABLE() functions. In the following screenshot from Data View of an import mode dataset, three calculated columns have been added to a Date dimension table with expressions referencing the Freight column of the Internet Sales fact table:

Row context with RELATEDTABLE()

 The DAX expressions used for each column are as follows:

Related Internet Freight Cost (Sum) = 
SUMX(RELATEDTABLE('Internet Sales'),(SUM('Internet Sales'[Freight])))
Related Internet Freight Cost (Measure) =
SUMX(RELATEDTABLE('Internet Sales'),[Internet Sales Freight Cost])
Internet Sales Freight Cost Measure = [Internet Sales Freight Cost]

Only the Internet Sales Freight Cost Measure returns the correct freight cost amount for each date. The Related Internet Freight Cost (Sum) column computes the total freight cost on the entire Internet Sales table and uses this value for each related row before summing the result. For example, nine rows on the Internet Sales table have a date of 1/3/2016 and the sum of the Freight column on the Internet Sales table is $618,839. Given the SUMX() function, the $5,569,554 value is the result of 9 (rows) multiplied by $618,839.

The Related Internet Freight Cost (Measure) also overcounts the freight cost for the day, specifically, whenever multiple rows of the same date have the same freight cost, the sum of these values is counted for each row. For example, five rows on the Internet Sales table have a date of 1/2/2016 and three of these rows have the same freight cost of $89.46. Given the SUMX() function, the value $268.37 (3 * $89.46) is added three separate times prior to adding the other two freight cost values ($17.48 and $85.00) to produce $908.

The RELATEDTABLE() function is used to reference tables on the many sides of one-to-many relationships. Likewise, the RELATED() function is used to reference tables on the one side of many-to-one relationships. For example, a calculated column or the row context of an iterating function such as SUMX() on the Internet Sales fact table would use RELATED() to access a dimension table and apply logic referencing the dimension table per row of the Internet Sales table. 
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.225.72.245