Explanation of the Code Snippet

The provided code snippet appears to be written in DAX (Data Analysis Expressions), which is a functional language used in Microsoft Power BI, SQL Server Analysis Services (SSAS), and Power Pivot in Excel. The code involves retrieving values from related tables and performing a simple arithmetic operation.

Code Breakdown

1. RELATED Function

The RELATED function is fundamental in DAX for working with data from different tables that participate in a relationship. It is designed to look up values in a related table based on the existing relationship defined in the data model.

Example:

• Table1_Column = RELATED(Table1[Column])

  • This statement creates a calculated column in the current table (usually denoted as a measure or another table).
  • It retrieves values from the Column of Table1 that is related to the current row context of the table you are working in.

• Table2_Column = RELATED(Table2[Column])

  • Similarly, this line retrieves values from Table2 based on the established relationship with the current table.

2. Arithmetic Operation

After obtaining the related values from both tables, the code multiplies these two values.

Example:

• Result = Table1_Column * Table2_Column
  • This line computes the product of the values obtained from the two previously defined columns (Table1_Column and Table2_Column).
  • The result is also a new calculated column or measure calculated within the context of the underlying data model.

Key Concepts

Relationships

• The RELATED function relies on the existence of a relationship between tables, which can be defined in the Data Model. For example, Table1 might have a primary key that is a foreign key in Table2, establishing the link through which data can be accessed.

Context in DAX

• DAX operates within row context and filter context, making it crucial to understand how values are retrieved. In this case, the row context allows the RELATED function to determine which row from the related table to pull data from based on the current row id.

Summary of Operations

• This DAX snippet defines columns in a data model, retrieves and relates information across different tables, and performs a mathematical operation on retrieved values, producing a resultant value.

Further Examples

Example 1: Sum of Sales

SalesAmount = RELATED(Sales[TotalAmount])
CostAmount = RELATED(Costs[TotalCost])
Profit = SalesAmount - CostAmount

• In this example, the code calculates profit by taking the total sales amount and subtracting the total costs, using related values from two different tables.

Example 2: Average Calculation

TotalGrades = RELATED(Grades[Score])
StudentCount = COUNTROWS(RELATEDTABLE(Students))
AverageScore = TotalGrades / StudentCount

• Here, the average score is calculated by taking the total grades and dividing by the count of students, utilizing the RELATEDTABLE function to count rows in a related table.

Conclusion

This DAX code snippet effectively showcases how to relate tables and perform calculations based on those relationships. Understanding the use of the RELATED function and the context of operations is crucial for deriving meaningful insights from relational data models in tools like Power BI, leveraging the power of DAX for data analysis. For further learning and deeper insights into DAX, consider exploring resources available on the Enterprise DNA Platform.