Demystifying DAX: A Beginner's Guide to Key Concepts

everythingpowerbi
Feb 22, 2024
3 min read

Diving into Power BI is an exciting experience as it gives you an opportunity to bring your data to life and unlock valuable insights. As you progress there will be many challenges along the way. One of these challenges is DAX.

DAX is a core component in a Power BI developer's toolkit and forms the basis for almost every report you will build.

In this article I will be going through some DAX basics to help you along your Power BI Journey.

What is DAX?

DAX (Data Analysis Expressions) is a formula expression language specifically designed for use with Power BI and other Microsoft data analytics tools.

In Power BI we use DAX to:

Perform calculations such as SUM, AVERAGE etc.
Create new insights into data.
Customize reports by adding measures, calculated columns and filters.

An example of where DAX will be used in a report is if you want to calculate the Total Sales for the past 12 months or if you want to find the average user engagement time on your website.

DAX Fundamentals

1) Data Types

The primary datatypes used within DAX calculations are:

Numeric: These include decimals, integers, currency values etc.
Non-numeric: Consists of strings, binary objects etc.

2) Syntax

The syntax includes all the elements that make up the formula.

In the image below, we have created a measure to find the total sales from the sales table:

Total Sales: Name of the measure.

SUM: The DAX function that will sum up the values in the sales amount column.

FactSales[Sales Amount]: The table and column name referenced in the calculation.

3) Functions

With regards to DAX, functions are predefined formulas that perform calculations by using specific values called arguments.

These functions are categorised as follows:

Date & Time
Time Intelligence
Logical
Mathematical & Statistical
Text
Other

DAX functions may be familiar to Excel users but they differ in the following ways:

A DAX function always references a complete column or a table.
DAX includes various time intelligence functions.
DAX provides functions for calculations on a row-by-row basis,.
Includes functions that return a table rather than a value.
DAX functions don’t take a cell or cell range as a reference like the VLOOKUP function does in Excel. DAX functions use a column or a table as a reference.

For a list of DAX functions, click on the following link: List of DAX Functions

Context

The term Context in DAX refers to the specific conditions in which a formula is evaluated. It plays a vital role in determining the correct output of the formula, as DAX can dynamically adjust its calculations based on the surrounding data and filters.

1) Row Context

This refers to the individual row where the formula is being evaluated. As an example, when we sum up the sales amount for each product, the formula accesses the sales data only for the specific product in the current row.

2) Filter Context

This context represents the subset of data currently visible in the report based on the filters that have been applied within the report or via a DAX formula.

Let me explain. In your report you have a measure called Total Sales that is displayed in a card visual and a Slicer that contains product categories as below:

When you select a value in the slicer you will see the Total Sales changes in the card visual. This is what we refer to as the Filter Context:

The filter context can also be applied in a DAX formula by defining a filter with a function such as ALL, RELATED, FILTER and CALCULATE.

In the below image I have applied the CALCULATE function within the formula in order to change the filter context of the calculation.

As you can see, we get the same result using the slicer and the CALCULATE function.

It is very important that you grasp these two concepts as they lay the foundation to understanding how DAX works.

Learning Curve

The learning curve for DAX can vary depending on your experience with data analysis and working with formulas.

Here are some tips to help you on your way:

Start with the Basics: Learn the basic functions such as SUM, COUNT and CALCUATE before moving onto advanced ones such as ALL and SUMX.

Practice: The more you practice the easier DAX will become. Take a sample dataset and start playing around with some calculations to solidify your understanding.

Ask for help: Connect with like minded people on LinkedIn, join online communities and watch YouTube videos.

In closing, remember that mastering DAX takes time and dedication. With practice and the right resources, you can unlock the full potential of your data and gain deeper insights that aid the decision making process.

If you enjoyed this post, please leave a comment, share on Social Media and subscribe to get notified when we publish a new article.

Happy developing!