Blog

You are currently viewing Introduction to DAX in PowerBI

Introduction to DAX in PowerBI

Loading

In this blog we will be learning about DAX in PowerBI also known as Data analytics expression. It helps user to create calculated columns, measures, and calculated tables for getting detailed insights.

What is DAX in PowerBI?

DAX is stands for Data Analysis Expressions, is a specialized formula language developed by Microsoft. It is primarily use to create custom calculations and perform advanced data analysis in Power BI, Excel Power Pivot, and SQL Server Analysis Services (SSAS). DAX is design to be familiar to users who have experience with Excel formulas, yet it provides a lot of power and flexibility to handle complex calculations and data manipulation. It allows users to create calculated columns, measures, and calculated tables, enabling deeper insights and powerful data models.

History of DAX in PowerBI

DAX was introduced in 2010 with the release of Power Pivot for Excel. Microsoft developed DAX to extend the capabilities of Excel, providing more powerful tools for data modeling and analytics. Over time, DAX has been integrated with other Microsoft tools such as Power BI and Tabular Models, making it a key component of the Microsoft Business Intelligence Suite. Its development is driven by the need for more robust analytical tools in a fast-paced data-driven world.

Where DAX is used

  • Power BI:DAX is widely used to create measures, time columns, and time tables. These are important for continuous calculations and interventions as well as for enhancing visualization and reporting.
  • Excel Power Pivot: DAX is used in Power Pivot to create more powerful data models within Excel, allowing the user to handle large datasets and perform complex calculations beyond standard Excel funnels.
  • SQL Server Analysis Services (SSAS):DAX is used to define business logic, create upgrade solutions, and create solutions that support advanced surface testing.

Basic Concepts of DAX in PowerBI

Calculated Column

Calculated columns are columns added to a table using a DAX formula. When the data model is edited or refreshed, it is calculated row by row. Calculated columns store the results of your calculations in a table that can be used in any data format or visualization.

Example: Suppose, you have a table named Sales and have following column and data:

ItemUnitPriceQuantity
Item 112005
Item 275015
Item 38510

Now you have to calculate total sales and store in TotalSales Column in Power BI Desktop.

Steps need to create a calculated column

Step 1. Go to Table view

1.PNG

Step 2. Go to Home tab and Select New column

2.PNG

Step 3. Fill the following:

Custom column formula:

3.PNG

Step 4. Click on ✔

4.PNG

Here, we can see that calculated column create a TotalSales column storing UnitPrice*Quantity for each stored Items. Now we can use this field for visualization also.

Let’s try one more example, now we want to add 18% GST on TotalSales

Now we will follow the same steps:

Step 1. We’are already in Table view

1.PNG

Step 2. Go to Home tab and select New column

2.PNG

Step 3. In formula field, type the following:

5.PNG

Step 4. Click on ✔

6.PNG

Measures

Measures are calculations that are used to dynamically aggregate data based on the filter context of a report or visualization. Unlike calculated columns, measures are not stored in a table, but rather they are calculated on the fly.

Example: To calculate the TotalSales across all rows in the Sales table, you can create a measure:

TotalSales = SUM(Sales[TotalSales])

Create a measure to calculate total revenue from the Sales table. We have already created TotalSales column.

Steps need to create Measures

Step 1. Go to Table view

1.PNG

Step 2. Go to Home tab and Select New measure

7.PNG

Step 3. Fill the following:

Measure formula:

8.PNG

Step 5. Click on ✔

It will not show in table, but present in Data and can be used in Visulaization:

9.PNG

Let’s try to use in visualization:

Follow the following steps:

Step 1. Go to Report view

10.PNG

Step 2. Select Card from Visualizations Section

11.PNG

Step 3. Select Total Revenue from Data Section:

13.PNG

Step 4. Fields will be filled with Total Revenue

12.PNG

Step 5. Now we can see visual:

14.PNG

Here we can see Total Revenue that we calculated

Tables and Columns

DAX has fundamental structures in the form of tables and columns. Tables contain data, while columns represent different fields within those tables. DAX allows creating a new counting table and columns to improve the model.

Example: You can create a new table aggregating sales by region using the SUMMARIZE function:

SalesByRegion = SUMMARIZE(Sales, Sales[Region], “TotalSales”, SUM(Sales[TotalSales]))

Steps need to create calculated tables

There is Calculated table also that we can create with it, with the help of DAX function can be created a new table:

Step 1. Go to Table view

1.PNG

Step 2. Go to Home tab and Select New measure

15.PNG

Step 3. Fill the following in field:

17.PNG

Step 5. Click on ✔

This all process will create a new table named HighSalesItems based on old table.

Old Table: (sales)

18.PNG

New Table: (HighSalesItems)

19.PNG

DAX Syntax and Elements:

Functions

DAX provides a variety of pre-built functions that perform various calculations and manipulations. These functions can be grouped into several categories, including aggregation, date and time, logic, text, and many others. Here are some examples:

  • SUM: It will return sum of all rows from a column

Column = SUM(TableName[ColumnName])

  • TODAY: It will return current date

Column = TODAY()

  • IF: A condition function for performing task based on condition

Column = IF(Condition) then “Statement” else “Statement”

  • CONCATENATE: For concatenating two string or two column

Column = CONCATENATE(TableName[ColumnName], “ ”, TableName[ColumnName])

Operators

Operators in DAX specify the type of calculation to be performed between elements in a formula.Here are some examples:

  • – (Subtraction): A operator used for subtraction from one or more columns

Column = [ColumnName] – [ColumnName]

  • & (Concatenation): It concatenate two column or string using & (sign

Column = [ColumnName]&“ ”&[ColumnName])

References

DAX uses references to indicate the source of data for calculations. Column references are enclosed in square brackets, while table references include the name of both the table and the column. Example:

Column = SUM(Tablename[Columnname])

Key Features of DAX in PowerBI

Data Types

There are various data types in DAX:

  • Integer: Whole Numbers (1, 2, 3, 4, …)
  • Decimal: Floating Number (1.2, 3.21, …)
  • Date/Time: Date and Time (2024-06-15, 02:05:56)
  • Text: String Values (“DataSpoof”, “Training”)

Row Context vs Filter Context

Row Context: Refers to the current row in the table being processed. It is used when the formula is applied to each row separately. In calculated columns, the formula works in the row context of that table. Here is an Example:

TotalSales = [quantity] * [unitPrice]

Filter Context: Refers to a set of filters to be applied to data when calculating a measure. It determines which data should be included in the calculation based on the current report or visualization filter. Here is an Example:

TotalSales = SUM(Sales[TotalSales])

Context Transition

Reference transition occurs when a row reference is converted to an equivalent filter reference. This typically occurs when a row reference is passed to a measure, allowing the measure to use the row reference as part of its calculation.

Example: Suppose you have a table with different transactions and you want to calculate the total sales for a specific product. Using measures, you can create a dynamic calculation:

TotalSales = CALCULATE(SUM(Sales[TotalSales]))

CALCULATE function converts the row reference (each transaction) into a filter reference so that the total sales for the specified product can be correctly aggregated.

Conclusion:

DAX in PowerBI or Data Analysis Expression plays an important role while processing or cleaning data or finding insights. With its help, we can perform many calculation and aggregation operations which are not easily possible with Excel functions.

With the help of this document we got many things like various calculations, aggregations, basic concepts and features.

If you like the article and would like to support me, make sure to:

Leave a Reply