RELATEDTABLE
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
One of the most useful DAX functions in Power BI is RELATEDTABLE. This function allows you to retrieve data from a related table and use it in calculations and measures. In this article, we’ll take a closer look at how to use RELATEDTABLE in Power BI.
Understanding Relationships in Power BI
Before we dive into the specifics of how to use RELATEDTABLE, it’s important to understand the concept of relationships in Power BI. In Power BI, you can create relationships between tables based on a common field or set of fields. These relationships allow you to combine and analyze data from multiple tables.
For example, you might have a table of sales data that includes a field for customer ID. You could create a second table of customer data that also includes a field for customer ID. By creating a relationship between these two tables based on the customer ID field, you can combine and analyze data from both tables in a single report.
Using RELATEDTABLE to Retrieve Data from a Related Table
Once you have created a relationship between two tables in Power BI, you can use the RELATEDTABLE function to retrieve data from the related table. The syntax for the RELATEDTABLE function is as follows:
RELATEDTABLE(❰TableName❱)
Where `❰TableName❱` is the name of the related table that you want to retrieve data from.
For example, let's say you have a table of sales data and a table of customer data, and you have created a relationship between the two tables based on the customer ID field. If you want to retrieve the customer name from the customer table and use it in a calculation in the sales table, you could use the following formula:
= CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, RELATEDTABLE(Customer)[Name]=”John Smith”))
In this formula, we are using the RELATEDTABLE function to retrieve the customer name from the customer table, and then filtering the sales data based on that name.
Using RELATEDTABLE in Measures
One of the most powerful features of RELATEDTABLE is its ability to be used in measures. Measures are calculations that are used in Power BI visualizations, such as charts and tables. By using RELATEDTABLE in measures, you can create complex calculations that combine data from multiple tables.
For example, let's say you have a table of sales data and a table of product data, and you have created a relationship between the two tables based on the product ID field. If you want to calculate the total revenue for each product category, you could use the following measure:
Total Revenue = CALCULATE(SUM(Sales[Revenue]), ALL(Product), RELATEDTABLE(ProductCategory))
In this measure, we are using the RELATEDTABLE function to retrieve the product category from the product table, and then using the ALL function to remove any filters on the product table. This allows us to calculate the total revenue for each product category, regardless of any filters that may be applied to the product table.
The RELATEDTABLE function is an incredibly powerful tool in Power BI that allows you to retrieve data from a related table and use it in calculations and measures. By understanding relationships in Power BI and how to use the RELATEDTABLE function, you can create sophisticated analyses and visualizations that combine data from multiple tables.
