Introduction

DAX is the programming language for Microsoft Power Pivot for Excel and Microsoft SQL Server Analysis Services (SSAS). It is specifically designed to compute business formulas over a data model. Such formulas can include; Sales Year-to-Date, Revenue Month to Date, etc. These formulae allow for the extraction of more insights from data.

Note: A data model is simply a collection of tables linked by relationships. Please refer to this tutorial explaining relationships in a data model.

In this short article, we’ll try to bring you up to speed with some commonly used functions.

Dataset

For this tutorial, we’ll be working with the Northwind Trade Dataset.

To access the dataset, use the OData Feed connector in Power BI and enter this URL as the data source:

https://services.odata.org/Northwind/Northwind.svc/

The “Sales_by_Category” table below is a sample of what we will manipulate with DAX.

Aggregation Functions

First, we’ll practice aggregation functions like; SUM, AVERAGE, MAX, MIN, COUNT

We’ll create these aggregations as measures on Power BI. To create measures, right-click on the desired table and select “New Measure”.

SUM

SUM calculates the total or sum of numeric records in a column. Here, we’ll calculate the sum of the “ProductSales” column. The Syntax is;

Total of ProductSales = SUM(Sales_by_Categories[ProductSales])

We will rename the measure to be “Total of ProductSales” so we can easily recognize it later. We can then use this measure in a card visual to see the total of the “ProductSales” column.

Now, we can see the total of “ProductSales” from the DAX calculation we have written.

We have learned how to create a new measure, rename it, write our formula and visualize the output. For subsequent aggregations, the steps will be similar so only the DAX code and its output will be shown.

AVERAGE

AVERAGE calculates the mean of numeric records in a column. We’ll also calculate and visualize the mean of the “ProductSales” column. Right-click on the Sales_by_category table and select “New measure”. Rename the measure to be “Average of ProductSales” and type in the DAX. The syntax is;

Average of ProductSales = AVERAGE(Sales_by_Categories[ProductSales])

MAX

MAX returns the highest amount of numeric values from selected records, or in our case, from a selected column. To get the highest “ProductSales” amount, we will create a new measure and rename it “Max of ProductSales”. The DAX syntax is;

Max of ProductSales = MAX(Sales_by_Categories[ProductSales])

MIN

MIN returns the lowest amount of numeric values or the smallest string from selected records and also the smaller value between two scalar expressions. To test this, let’s create a new measure and rename it as “Min of ProductSales”. The DAX syntax is;

Min of ProductSales = MIN(Sales_by_Categories[ProductSales])

COUNT

Count returns the number of values in a column, in this context, Count can give us an idea of the number of transactions in our table since each row represents one record.

Create a new measure and rename it “Count of Product Sales”. The syntax is;

Count of ProductSales = COUNT(Sales_by_Categories[ProductSales])

COUNTROWS

COUNTROWS is a variant of COUNT and it returns the number of rows in a table. Using COUNTROWS with a combination of DISTINCT or VALUES, we can count the number of unique items in a column. For example, we can combine COUNTROWS and DISTINCT to get the number of unique items in the “CategoryName” Column of the Sales_by_Categories Table.

Let’s create a measure to test the COUNTROWS + DISTINCT function. Rename the measure to be “Count_distinct_category”. The syntax is;

Count_distinct_category = COUNTROWS(DISTINCT(Sales_by_Categories[CategoryName]))

Using COUNTROWS + VALUES returns the same output in this context. If we had any blank cells in the CategoryName column, then VALUES will act differently from DISTINCT. This is because VALUES recognizes the blank cells as one more unique value while DISTINCT ignores any blank value. Let’s test this using this syntax;

Count_value_category = COUNTROWS(VALUES(Sales_by_Categories[CategoryName]))

Conditionals, Common Operators and Functions

Conditional statements give added flexibility to our analysis. We can check if a condition evaluates to true, and set a return value for when our condition evaluates to true or false.

IF Function

The IF function is responsible for evaluating a condition and it can be used along with the LOGIC statements AND and OR. To see how the IF conditional works, we’ll create a new calculated column. Follow this step to learn how to create a calculated column.

Navigate to the “column tools” ribbon

Click and Select on “New Column” on calculations

On the formula bar that pops up, rename the New Column and type in your formula

We’ll create a new column “ Sales Category” and our condition will be to check the “ProductSales” column to see if it’s greater than or equal to (>=) 5000. If it is, we want to return “large”, and if it isn’t, we will return “small”.

The syntax is;

Sales Category= IF(Sales_by_Categories[ProductSales] >= 5000, "Large", "Small")

Note: the outputs, Large and Small were enclosed in a “ “. All text value types must be enclosed in a “ “ for Power BI to recognize it as text. However,numbers can be written without enclosing them.

CONCATENATION

Concatenation allows us to join values from multiple columns together to create a new column using the ampersand (&) symbol. For example, in our “Sales_by_Categories” table, we can combine the “CategoryName” and “ProductName” columns to create a new column that contains both the category name and also the product name. To test this, let’s create a new column and rename it as ProductCategoryName and use the following syntax;

ProductCategoryName = Sales_by_Categories[CategoryName] & " " & Sales_by_Categories[ProductName]

CALCULATE

Next, we learn how to use the CALCULATE function. CALCULATE accepts at least two (2) parameters; an expression, and a filter. An expression could be an aggregation, like calculating the sum of records in a given column. The filter determines what condition must be met before the expression can be performed in that column.

First, a look at the table we’ll be manipulating for the next few lessons. This is the “Sales_Totals_by_Amounts” table.

Next, we will calculate the SUM of the “SaleAmount” column when the “CompanyName” is "Ernst Handel". i.e, we want to get the total sum of sales for the company “Ernst Handel”. To do this, create a new measure and name it; “Sales_by_filter”

The syntax is;

Sales_by_filter = CALCULATE(SUM(Sales_Totals_by_Amounts[SaleAmount]), Sales_Totals_by_Amounts[CompanyName] = "Ernst Handel")

It’s important to note that you can modify the name of your measure as you see fit. In this case, we can decide to name our measure sales by Ernst Hanel.

Common Time Intelligence Functions

Next, we look at time intelligence functions and how they are written in DAX. We will utilize our knowledge of the CALCULATE function for this. Some common time intelligent functions include Year-to-Date, Quarter-to-Date, Month-to-Date etc. The syntax is similar to the “Sales_by_filter” measure. The difference is, this time, our filters will be time intelligent.

Now, we’ll look at each one.

YEAR-TO-DATE

Year-to-date lets you compute any value for a period of 1 year. i.e from the start of the year to the current day of the year. To test this, we’ll calculate the sum of the “SaleAmount” column for a period of one (1) year. The syntax for this is;

Sales YTD = CALCULATE(SUM(Sales_Totals_by_Amounts[SaleAmount]), DATESYTD(Sales_Totals_by_Amounts[ShippedDate]))

Note that we still use the CALCULATE function, but our filter, in this case, is “DATESYTD(Sales_Totals_by_Amounts[ShippedDate])”. DATESYTD is a special in-built function that allows us to filter a date column and return values up to a year. Other in-built time intelligent functions include; DATESQTD (quarter-to-date), DATESMTD(Month-to-date). We will learn about them in subsequent steps.

QUARTER-TO-DATE

Quarter-to-date filters a date column and only returns values for the last three (3) months up till the current month. Put simply,it only returns values for the current quarter. To calculate the “SaleAmount” for the current quarter, we will create a new measure and rename it as “Sales QTD”, and the syntax is;

Sales QTD = CALCULATE(SUM(Sales_Totals_by_Amounts[SaleAmount]), DATESQTD(Sales_Totals_by_Amounts[ShippedDate]))

MONTH-TO-DATE

Month-to-date (MTD) filters our date column and returns only values that are between the first day of the current month till the current day of the current month. E.g if today’s date is January 26, MTD will return values from January 1 - January 26 and ignore values for all other months. To calculate the “SaleAmount” for the current month, we will create a new measure and rename it as “Sales MTD”. The syntax is;

Sales MTD = CALCULATE(SUM(Sales_Totals_by_Amounts[SaleAmount]), DATESMTD(Sales_Totals_by_Amounts[ShippedDate]))

We do hope that you found this blog exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thank you for learning with Us!