Introduction to DAX in Power BI — DAX in Power BI — Chapter 1

This is a step by step tutorial starting with an introduction to DAX operators, DAX function categories, DAX data model components and first hands-on experience with DAX.


For any POWER BI job description, you will find one competency is mandatory. Any idea what is that? Yes, you are right. It is DAX.

DAX is playing an important key role in any Power BI project. In this blog series, I will capture my understanding of DAX with some practical code.

What is DAX?

DAX (Data Analysis Expressions) is known as a functional language. It means, calculations mostly use functions to generate the results.

DAX is designed for enhancing data modelling, reporting and analytics capability.

DAX Data Type

DAX Data Type Power BI
Image by Author

In DAX, it is possible to define the data type for each column based on your requirement.

The selection of the best data type helps to reduce the size of a data model and improve the performance when to refresh data and use of any report.

DAX Operator

POWER BI DAX Operator
Image by Author

DAX operators are divided into four categories: comparison operators, arithmetic operators, text operator, and logic operators.

DAX Function Category

You can write a nested function using DAX but you cannot create your own.

There are more than 200 DAX functions. There are 9 categories in the DAX function.

  1. Date and Time
  2. Time Intelligence
  3. Information
  4. Logical
  5. Mathematical
  6. Statistical
  7. Text
  8. Parent/Child
  9. Other

Components of DAX Data Model

DAX component in Power BI
Image by Author

A data model consists of data, calculations and formatting rules and it combines to create an object. This object helps to explore and understand the dataset.

Your First Practical Experience with DAX

Till now you have some idea about DAX and its importance. Before moving to discuss each DAX function category, let us get some hands-on experience with DAX.

Get Data

For this case study, I consider the US Superstore dataset from Kaggle ( https://www.kaggle.com/juhi1994/superstore)

  • Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
  • Select the file named US Superstore data.csv
  • After selecting the file, data will be displayed in the below format
DAX in POWER BI
Image by Author
  • Click on Load and save data.

There are 3 types of calculations in DAX.

  • Calculated Columns
  • Calculated Measures
  • Calculated Tables

Example 1 (Calculated Measure)

  1. Create calculated measure Total Sales

Total Sales = SUM(‘US Superstore data’[Sales])

Calculated measure in DAX Power BI
Image by Author

2. Similarly, create a calculated measure of Total Profit.

Total Profit = SUM(‘US Superstore data’[Profit])

Example 2 (Calculated Column)

  1. Create calculated column Total Cost.
Calculated Column — DAX in Power BI
Image by Author

Example 3 (Calculated Table)

  1. In Report View, go to Modeling Tab and click on New Table under Calculations group.
  2. Create a calculated table (e.g. Date Table).
Date Table in Power BI using DAX
Image by Author

Download

Please find the code in the below location

DAX — Chapter 1 (https://github.com/arpitag1/Power-BI)

Video

Conclusion

In this blog, the following things have been identified

  1. What is DAX
  2. What are all DAX Operators
  3. How many types of DAX categories
  4. How many components are there in DAX Data Model
  5. First Hands-on Experience with DAX.

In my next blog, we will learn in details about DAX functions.

5 comments

  1. When I am trying to create a date table, its showing CALENDARAUTO function can not find a base column of DateTime type in the model.

    Like

Leave a Reply to Arpita Ghosh Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: