Understanding DAX Language: Basics and Beyond

Introduction to DAX

Data Analysis Expressions (DAX) is a formula language used in Power BI, Analysis Services, and Power Pivot in Excel. DAX includes functions, operators, and constants that you can use in a formula, or expression, to calculate and return one or more values. DAX helps you create new information from data already in your model.

Basic implementations

Table functions:

Table functions are typically used to create tables of data and store them in varying different ways. The ALL function in DAX is a very powerful filter function that allows you to manipulate the context of your formulas in many different ways.

EVALUATE
FILTER (
ALL (Students),
Students[Age]> 21
)

In this example, we fetch all Students with age > 21. The ALL function is used to remove all context filters in the table.

Aggregate functions:

Aggregate functions perform a calculation on a set of values and return a single scalar value. The AVERAGE function numerically averages all the non-blank rows in a column, in the provided set of values:

EVALUATE
SUMMARIZE (
Students,
"Average Age", AVERAGE ( Students[Age] )
)

In this particular example, we calculated the Average Age in 'Students'.

Analytical functions:

DAX offers us a set of powerful analytical functions. Here, RANKX is a very useful function that ranks a provided expression against a specific scope.

EVALUATE
ADDColumns (
Students,
"Age Rank", RANKX ( Students, Students[Age] )
)

In this example, it calculated the Age rank for each student. RANKX function is used to rank the values in the column 'Age'.

Further application of DAX

Given the overview of some basic DAX formulas as outlined above, by combining them with variables, conditional statements and a few other DAX features, you can always handle complex business scenarios with highly efficient formulas:

VAR TotalStudents = COUNTROWS ( ALL(Students) )
RETURN
SUMX (
VALUES ( Students ),
VAR StudentsAge = Students[Age]
    RETURN
    CALCULATE (
        COUNTROWS ( Students ),
        ALL ( Students ),
        Students[Age] = StudentsAge
    ) / TotalStudents
)

In this example, we calculate the proportion of students for each age group in the total number of students. These types of calculations where we use both aggregate and table functions can provide a deep insight into the data. This helps us to understand the distribution of students across different age groups.

Conclusion

This document outlines the basics of DAX and demonstrated a few practical implementations. However, the crux of mastering DAX lies in understanding the evaluation context i.e., the circumstances under which DAX computes values. Always keep in mind the nature of the function you are using: is it an aggregate function, is it a filter function or is it a scalar function? This can generally guide you towards distinguishing between intended and unintended behavior in your formulas.

Building and Analyzing DAX Formulas

Section 1: Introduction

DAX (Data Analysis Expressions) is a formula language which allows users to create custom calculations on data models. For our analysis, we will focus on the CALCULATE DAX function, which is one of the most commonly used and fundamental expression in DAX language due to its flexibility and power.

CALCULATE Function

According to Microsoft Docs, the syntax for the CALCULATE function is:

CALCULATE (, , , ...)

• <expression>: Any DAX expression that returns a single scalar value.
• <filter1>, <filter2>, ...: The filter arguments to apply.

Section 2: Building a DAX Formula

Let's take a practical example where we have a table named 'Sales' with columns 'Product', 'Region', 'Quarter', and 'Revenue'. We want to calculate the total revenue for the product 'X' in the 'Q1' quarter.

We can write the following DAX measure using the CALCULATE function:

Total Revenue for Product X in Q1 = CALCULATE (
        SUM ( Sales[Revenue] ),
        Sales[Product] = "X",
        Sales[Quarter] = "Q1"
    )

In the above formula, CALCULATE changes the context under which the data is evaluated. It does this by imposing the filters Sales[Product] = "X" and Sales[Quarter] = "Q1". The SUM function is then evaluated under this new context.

Section 3: Analyzing a DAX Formula

To understand the impact of the CALCULATE function in this formula, let's say without the use of CALCULATE we compute the sum of the 'Revenue' column and we get the total revenue for all products across all quarters.

The CALCULATE function can alter this contextual evaluation. By setting the filters of 'Product' to be 'X' and 'Quarter' to be 'Q1', it modifies the context for the aggregation function SUM, so that SUM only considers rows of the 'Sales' table where 'Product' is 'X' and 'Quarter' is 'Q1'. The sum of 'Revenue' under this new filtered context gives us the total revenue for product 'X' in 'Q1', demonstrating the power of the CALCULATE function.

Conclusion

In conclusion, DAX language provides powerful tools like the CALCULATE function to create meaningful and context aware measures. It enables custom and dynamic calculations which can handle complex business requirements, making it an essential tool for any data analysis involving Power Pivot, Power BI or Analysis Services.

Mastering Data Manipulation Functions in DAX

In this section, we will go further in-depth with the DAX functions that work with datasets, specifically focusing on data manipulation. This knowledge is essential to creating complex, useful queries and measures in DAX.

Understanding Context in DAX

Before we start looking into the functions, we need to introduce the concept of context in DAX. In short, context refers to the subset of data that a DAX formula operates within.

There are two types of context in DAX; row context and filter context:

• Row context refers to the current row. Any DAX formula operates within a row context when computing the values of its arguments.
• Filter context is applied to the data model, limiting the data that is loaded into a visualization, or into any DAX formula.

We have two functions for manipulating context.

CALCULATE and CALCULATETABLE are the most important functions in DAX. They allow you to modify the context in which their parameters are computed.

CALCULATE (, , , …)

CALCULATE evaluates the expression argument in a context that is modified by the specified filters.

CALCULATETABLE works similarly, but is used when you want to return a table.

CALCULATETABLE (, , , …)
Data Manipulation Functions
Table Functions
FILTER Function
The FILTER function allows you to generate a new table based on filtering the rows of the original table.
FILTER ( ,  )
An example of how to use FILTER could be to make a new table with only the consumers whose purchase was above the average:
FILTER (Sales, Sales[Quantity] > AVERAGE(Sales[Quantity]));
RELATEDTABLE Function
The RELATEDTABLE function allows you to generate a new table with the rows that are related to the current row context.
RELATEDTABLE(
)
A use case for the RELATEDTABLE function would be if you have a sales table and a product table. If you want to get all the sales records for a certain product, you can use RELATEDTABLE.
RELATEDTABLE(Sales)
Transformation Functions
RANKX Function
RANKX is a function to provide a ranking number, given a table and expression to base the ranking on.
RANKX(
, )
For example, if you want to rank your consumers based on the quantity of items they bought:
RANKX( ALL(Customers), SUM(Sales[Quantity]),, DESC )
EARLIER Function
The EARLIER function is used to access data from an earlier row context.
EARLIER()
EARLIER is useful for nested calculations where you wish to use a certain value as an input. As an example, you can calculate the cumulative sales:
SUMX( FILTER( Sales, Sales[Date] <= EARLIER( Sales[Date] ) ), Sales[Quantity] * Sales[Net Price])
These are just some of the functions that DAX provides for data manipulation. Remember that learning DAX is a continuous process and that even the most experienced DAX developers regularly learn new tricks and methods. Happy coding!
4navigate_next
Real-world Applications of DAX in Data Analysis
This section will cover a practical example of using DAX (Data Analysis Expressions) to analyze some real-world data. We'll focus on the CALCULATE DAX formula, since it's an incredibly powerful and highly used function in DAX.
Scenario: Sales Analysis using CALCULATE Formula
Suppose we have a sales data file containing a sales fact table and dimension tables for products, customers and salesperson data, etc. We want to analyze this data using the CALCULATE DAX formula to get deeper insights.
Objective
Our detailed analysis objective is to calculate the sales amount for a specific product and for a specific salesperson during a specific time period.
We will implement two examples of CALCULATE:

Calculate Total Sales by a particular Salesperson.
Calculate Total Sales of a particular product in a given time period.

Example 1: Calculating Total Sales by a Specific Salesperson
Suppose we have a Sales table with a column Salesperson, and we want to calculate the total sales made by a specific salesperson say 'John Doe'.
The CALCULATE statement could be written as:
Total_Sales_John = CALCULATE(
SUM(Sales[SalesAmount]),
Sales[SalesPerson] = "John Doe"
)
In the formula above, SUM(Sales[SalesAmount]) is the expression that calculates the total sum of the sales amount column. Sales[SalesPerson] = "John Doe" dertermines a new context where the salesperson is John Doe.
Example 2: Calculating Total Sales of a Specific Product during a Specific Time Period
Let's consider we have a Sales table and a Dates table and we want to calculate the total sales for a product named 'Product X' during the year 2020. 
The CALCULATE statement could be written as:
Total_Sales_ProductX_2020 = CALCULATE (
SUM(Sales[SalesAmount]),
Sales[ProductName] = "Product X",
DATESYEAR(Dates[Date]) = 2020
)
Where SUM(Sales[SalesAmount]) calculates the total sum of sales. The context is changed in the following ways: Sales[ProductName] = "Product X" where product is 'Product X', and DATESYEAR(Dates[Date]) = 2020 where the year is 2020.
Conclusion
As can be seen from the examples above, DAX’s CALCULATE formula can be applied in many real-world scenarios to change the context of the calculation. This makes it one of the most powerful and commonly used DAX formulas. By understanding how to use CALCULATE, you can manipulate the context to produce dynamic and powerful insights from your data.
In practice, be sure to replace "John Doe" and "Product X" with your actual salesperson name and product id/name respectively. Also, replace "2020" with your own year of choice.
5navigate_next
Dissecting the CPT Columns Count DAX Formula
In this section, we're going to thoroughly dissect the Columns Count DAX formula, the fifth unit in our series for mastering DAX Formulas. 
First, let's define what the Count Columns Function is. In DAX (Data Analysis Expressions), the COUNTCOLUMNS function is used to count the number of columns in a table or in table expression syntax. The syntax is simply COUNTCOLUMNS(<table>, [<expr>]). It returns the count of columns that have non-blank values.
Usage of COUNTCOLUMNS Function
Consider we have a simple table named 'Employees' as follows:

Name
Age
Salary


John
25
3000


Kate
27
3800


Roy
31
5000


Linda
29

=COUNTCOLUMNS(Employees)

=COUNTCOLUMNS("Counting columns for 'Employees' record:",
               Employees[Name], Employees[Age], Employees[Salary])