Skip to content

Latest commit

 

History

History
94 lines (65 loc) · 4.86 KB

related-function-dax.md

File metadata and controls

94 lines (65 loc) · 4.86 KB
Raw
description title
Learn more about: RELATED
RELATED function (DAX) | Microsoft Docs

RELATED

[!INCLUDEapplies-to-measures-columns-tables]

Returns a related value from another table.

Syntax

RELATED(<column>)

Parameters

Term Definition
column The column that contains the values you want to retrieve.

Return value

A single value that is related to the current row.

Remarks

  • The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. If a relationship does not exist, you must create a relationship.

  • When the RELATED function performs a lookup, it examines all values in the specified table regardless of any filters that may have been applied.

  • The RELATED function needs a row context; therefore, it can only be used in calculated column expression, where the current row context is unambiguous, or as a nested function in an expression that uses a table scanning function. A table scanning function, such as SUMX, gets the value of the current row value and then scans another table for instances of that value.

  • The RELATED function cannot be used to fetch a column across a limited relationship.

Example

In the following example, the measure Non USA Internet Sales is created to produce a sales report that excludes sales in the United States. In order to create the measure, the InternetSales_USD table must be filtered to exclude all sales that belong to the United States in the SalesTerritory table. The United States, as a country, appears 5 times in the SalesTerritory table; once for each of the following regions: Northwest, Northeast, Central, Southwest, and Southeast.

The first approach to filter the Internet Sales, in order to create the measure, could be to add a filter expression like the following:

FILTER('InternetSales_USD'
, 'InternetSales_USD'[SalesTerritoryKey]<>1 && 'InternetSales_USD'[SalesTerritoryKey]<>2 && 'InternetSales_USD'[SalesTerritoryKey]<>3 && 'InternetSales_USD'[SalesTerritoryKey]<>4 && 'InternetSales_USD'[SalesTerritoryKey]<>5)

However, this approach is counterintuitive, prone to typing errors, and might not work if any of the existing regions is split in the future.

A better approach would be to use the existing relationship between InternetSales_USD and SalesTerritory and explicitly state that the country must be different from the United States. To do so, create a filter expression like the following:

FILTER( 'InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")

This expression uses the RELATED function to lookup the country value in the SalesTerritory table, starting with the value of the key column, SalesTerritoryKey, in the InternetSales_USD table. The result of the lookup is used by the filter function to determine if the InternetSales_USD row is filtered or not.

Note

If the example does not work, you might need to create a relationship between the tables.

= SUMX(FILTER( 'InternetSales_USD'  
            ,  RELATED('SalesTerritory'[SalesTerritoryCountry])  
               <>"United States"  
             )  
     ,'InternetSales_USD'[SalesAmount_USD])

The following table shows only totals for each region, to prove that the filter expression in the measure, Non USA Internet Sales, works as intended.

:::no-loc text="Row Labels"::: :::no-loc text="Internet Sales"::: :::no-loc text="Non USA Internet Sales":::
Australia $4,999,021.84 $4,999,021.84
Canada $1,343,109.10 $1,343,109.10
France $2,490,944.57 $2,490,944.57
Germany $2,775,195.60 $2,775,195.60
United Kingdom $5,057,076.55 $5,057,076.55
United States $9,389,479.79
Grand Total $26,054,827.45 $16,665,347.67

The following shows what that you might get if you used this measure in a report table visual:

:::no-loc text="Row Labels"::: :::no-loc text="Accessories"::: :::no-loc text="Bikes"::: :::no-loc text="Clothing"::: :::no-loc text="Grand Total":::
2005 $1,526,481.95 $1,526,481.95
2006 $3,554,744.04 $3,554,744.04
2007 $156,480.18 $5,640,106.05 $70,142.77 $5,866,729.00
2008 $228,159.45 $5,386,558.19 $102,675.04 $5,717,392.68
Grand Total $384,639.63 $16,107,890.23 $172,817.81 $16,665,347.67

Related content

RELATEDTABLE
Filter functions