Power BI is a data visualization tool that was launched by Microsoft in 2015. Even though Tableau was already there in the market since 2004, Power BI is catching up in the market very well and has taken a good amount of market share when it comes to Big Data Analytics tools.

What is DAX?

Before we go into the advanced-level details of DAX, let’s understand what DAX in Power BI is. DAX (Data Analysis Expressions) is a formula language that is used in various tools like Analysis Services, Power BI, and Power Pivot in Excel. DAX comes under the advanced level of Power BI where we can perform calculations or manipulate the existing data in the data model using formulas. DAX has almost 15 categories of functions and every category has almost 5 to 10+ functions leading to 250+ total DAX functions.

When we talk about Power BI then we are only talking about creating visuals with the data already present in the model. For Example, if we have two columns, Sales & Profit in the data model then we can calculate Total Sales, Average Sales, Minimum Sales, and Maximum Sales for any dimension but what if we need to see Profit Margin for any dimension then how to do? For this calculation, we need DAX.

Importance of DAX In Power BI

Without DAX, Power BI is just a visualization tool, and no analysis is possible. For a normal report creation, the basic knowledge of Power BI is enough but to be able to create a sophisticated and meaningful report, DAX is required. With customized commands and measures that can be used under the DAX syntax, the reports generated are highly meaningful & appealing.

Components of DAX in Power BI

DAX is made up of three fundamental concepts:

1. Syntax

Syntax refers to the parameters of a formula you are writing like parenthesis, the name of the table, the name of the column, the name of the function etc.

2. Context

Context refers to the target row that has been referred to in the formula for data retrieval or calculation. There are three types of contexts in DAX, Row Context, Filter Context and Query Context.

3. Functions

Functions refer to the predefined logic already existing in the system. For example, Sum, Countrows, Average, Filter, etc.

List of DAX Functions’ Categories

Following are the 15 categories of DAX functions:

New Functions
Aggregate Functions
Date & Time Functions
Filter Functions
Financial Functions
Information Functions
Logical Functions
Math & Trig Functions
Other Functions
Parent & Child Functions
Relationship Functions
Statistical Functions
Table Manipulation Functions
Text Functions
Time Intelligence Functions

Benefits of Learning DAX

You get the maximum information out of your data.
It helps to solve real-time business problems.
It’s a very common requirement in BI job positions.
It’s a commonly used language across all Microsoft Platforms.
Using DAX, you can do custom calculations that are used in advanced analysis.

How DAX helps you as a professional

It makes you a better Data Analyst.
It teaches you the best Dashboarding skills.
It helps to learn optimized codes.
It improves your logical reasoning & ultimately helps you to think differently.

DAX Learning Curve

Learning DAX is a bit hard because people compare it with Excel. Excel is all about Rows & Columns, but DAX is all about Tables & Columns. If you want to learn DAX fast, then keep this difference in mind.

In the blog image, refer to the topics you need to learn in the defined sequence to master DAX. As the functionality will increase, the difficulties will increase.

Pre-requisites before you learn DAX

Basic to intermediate Power BI.
Correct Visual Selection Capability.
Report Generation.
Logical thinking should be very strong.

How DAX Works Technically

The whole DAX is based on two engines, Formula Engine (DirectQuery) & Storage Engine (VertiPaq). The formula engine evaluates a DAX query, figures out the columns it needs to generate the result, sends the data request to the storage engine, the storage engine sends the data cache to the formula engine and then finally the DAX query generates the results.

How to start learning DAX?

When we click on any table in Power BI Environment, there is one section available that is called calculations. It has four options available New Measure, Quick Measure, New Column, and New Table. When you start with DAX then these are the first four concepts you are supposed to learn. Let’s understand them one by one.

New Measure: When you write down your own formula using DAX functions then it is called a New Measure. When we click on New Measure, it gives us a formula bar to write down the formula as same as excel.
Quick Measure: When we click on Quick Measure, it gives a list of pre-defined functions (27 functions divided into 6 categories). Using this section, we don’t need to write out our own customized formula, but we can straight away use the in-built functions in the list. This list of quick measures is very useful and helps us to do quite a good, advanced analysis.
New Column: When we click on New Column, it gives us a formula bar to define the formula using which we want to calculate the value for the new column. As compared to New Measure, New Column created a new column in the existing table & calculated the value for each row of the table. The column will only be accessible in the report view.
New Table: When we click on New Table, it again gives us a formula bar to define the logic using which a new table will be created. The table will only be accessible in the report view.

Important Concepts for Better Clarity

To start with DAX, you must understand the following concepts first. Then only you can proceed towards advanced calculations & logic building.

1) Difference between Measure & Calculated Column

These two concepts are the backbone of DAX in Power BI. The measure is a formula which can only show the result when we plot it in any visual. While Calculated Column is visible in the table itself in the report view. To understand the complete difference, you must experience it practically and we might help you with this with another blog.

2) Difference between Quick Measure & New Measure

Both are dynamic, the only difference is that New Measure is a customized formula and Quick Measure is pre-defined.

3) Role of Calendar Table

At the Time Intelligence level, we need a calendar table to have linear data.

Important Functions to Start With

As DAX has 250+ functions, it is not possible to learn the whole DAX in one go. Please find below a list of functions that one should learn in the starting phase.

1) Scalar Functions like Sum, Average, Count, Min, Max

2) Tabular Functions: Filter, Groupby, Distinct

3) Filter Functions: Calculate, All

4) Relationship Functions: Related

5) Ranking Functions: Rankx

6) Time Intelligence Functions: Sameperiodlastyear, Dateadd

Let’s understand the important Power BI DAX Functions with Examples here

1) Sum

It gives you the sum of all the values in a column.

Syntax: Sum(Column Name)

Example: Figure out Total Sales

2) Average

It gives you the Average of all the values in a column.

Syntax: Average(Column Name)

Example: Figure out Average Sales

3) Count

It gives you the Count of all the values in a column.

Syntax: Count(Column Name)

Example: Figure out the Count of Sales

4) Min

It gives you the Minimum of all the values in a column.

Syntax: Min(Column Name)

Example: Figure out Minimal Sales

5) Max

It gives you the Maximum of all the values in a column.

Syntax: Max(Column Name)

Example: Figure out the Maximum Sales

6) Filter

It gives you a small table out of a big table depending upon the filter being supplied in the second parameter.

Syntax: Filter(Table, Filter)

Example: Figure out a subset table only with the sales details of the West Region

7) Groupby

It creates a pivot table. Multiple aggregations can be done.

Syntax: Groupby(Table, Groupby Column1, Groupby Column2,…)

Example: Figure out the total sales generated by each Customer ID

8) Distinct

It returns the unique values of the column supplied.

Syntax: Distinct(Column Name)

Example: Figure out the distinct names of Sub-Categories

9) Calculate

It evaluates an expression considering the filters supplied in other parameters.

Syntax: Calculate(Expression, Filter1, Filter2,…)

Example: Figure out the Total Sales only for West Region

10) All

It clears the filters from the table on the columns being supplied as parameters. If you don’t supply any column, then it will remove all the filters.

Syntax: All(Table, Column, Column,…)

Example: While using the rank function, filters need to be removed as rankx is an iterator which leaves the filters on the table.

11) Related

It returns a related value from another table.

Syntax: Related(Column Name)

Example: While creating a sub-table out of table A, we need to give criteria from Table B column then a related function will be used.

12) Rankx

It generates the rank for the Column being mentioned on the basis of the expression specified.

Syntax: Rankx(Column Name, Expression)

Example: Generating ranks for sub-categories on the basis of Total Sales.

13) Sameperiodlastyear

It returns one year back data from the date mentioned in the parameter.

Syntax: Sameperiodlastyear(Date Column)

Example: Compare the Sales of July 2022 with July 2021

14) Dateadd

Sameperiodlastyear goes only backwards and that too by one year. If you want to go backwards & forward both and that too by more than a year, use Dateadd.

Syntax: Dateadd(Date Column, Number of Intervals, interval)

Example: Compare the Sales of July 2022 with July 2020

How to Analyse DAX Query?

It is not possible to analyse DAX Query in Power BI Desktop. To know the in-between results of a DAX Query, there is another external tool available that can be integrated into Power BI Desktop. The tool is DAX Studio.

What is DAX Studio?

DAX Studio is an external tool (a free tool that was launched in December 2016) which can be easily integrated into Power BI Desktop. It is used to write, execute, and analyse the DAX query in Power BI. The most important use case is that if you want to check the in-between result of a DAX query then it helps. Otherwise, it is not possible in Power BI Desktop, and it becomes very difficult to figure out the mistake in a DAX query if the query is long.

How to Optimize a DAX Query?

1) Finding the DAX query’s bottlenecks is one of the most important tasks in its optimization.

2) You must first confirm that your data model is configured properly. DAX is a tool that is designed to operate in a particular certain way so that you can follow the process seamlessly.

3) Find the measures you think are causing most of the issues.

4) You can run them in the server timings in the DAX studio and check for any unused rows or call-back data IDs that might affect how long it takes to calculate all the measurements in total.

5) They can also be in the physical query plan’s records column. Simply check that the number of records is equal to or nearly equal to the number of rows in the output. You may ensure that your query fully materialises enough for the tables in the result in this way.

Tips to learn DAX Easily

Before we wrap up, let’s have a look at some of the tips to learn DAX better.

Someone can be a DAX novice, whereas someone else might have gained more knowledge in the past. All that is required is mindful concentration. Regardless of technology, the idea of never giving up on learning is the only one that truly works. Learn something practical, then keep applying what you’ve learned until you see results.

The following are some tried-and-true methods for learning DAX and Power BI:

1) Books or tutorials

This comprehensive guide or other introductory books can be useful to you if you don’t want to delve further into the specifics and want to develop a very basic approach to learning DAX. This guide covers a lot of fundamentals that can assist you in becoming familiar with DAX as a new set of tools. Simply learn the fundamentals, review them, and move on.

2) Implementation

Create a tangible product or put the knowledge you learned in the session into practice. Anything from the toy to the project or anything in between can be considered here. It should be designed in such a way that you can shift your attention from understanding the fundamentals to putting them into practice. This should be something you can use or keep in your house or place of business.

3) Levelling up

You can always move up with the advanced sessions, which we would discuss in more detail if you feel like you have learned the fundamentals through this tutorial. Gaining experience can help you understand how to use DAX and Power BI principles more thoroughly. This guide might have provided you with an exhaustive explanation of how DAX functions, from the filler to the row context. This manual is an extremely important understanding that is required to work in every learning and its implementation.

4) Additional personal research

Studies and self-improvement should never be put on hold. You must use the maximum effort to continue learning about DAX by reading this tutorial often and putting the fundamentals you have learned here into practice. You can quickly become successful in a technical field like DAX by self-study and developing your focus. Prior to truly planning to level up, you must make every effort to concentrate on this guide and conduct some independent research.

5) Solidifying your understanding

Cementing your knowledge and helping others learn what you have discovered is always a smart idea. Filling in the gaps is another crucial activity where you must solidify your information by imparting it to others. You can get a deeper understanding of DAX as a technical language by blogging or by simply sharing what you have learned at random. Make every attempt to master DAX, then. Additionally, you can turn this guide into a video, which will put your work to the test.

Final Words

If you have read this blog properly then you can say that you know basic to intermediate DAX well. To learn more, you can start picking up different business problems & KPIs and try to figure out the formula for the same. The steps are simple. Pick up a problem, understand the problem, try to build the logic for the calculation, figure out the function category, figure out the function that will help you, & finally write down the formula. It will work. If you think that you are curious enough to see the in-between results of a DAX Formula, then proceed and configure DAX Studio in Power BI Desktop and pick up another ladder in your career.

Happy Learning!