Power BI DAX

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is DAX in Power BI?

DAX stands for “Data Analysis & Expression.” DAX is a functional language representing all Excel and Power BI functions. In simple terms, we can call it “Function Representor.” Using DAX language, we can create formulas in Power BI. Understanding DAX formulas would not be difficult if you are familiar with Excel formulasExcel FormulasThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions.read more.

Data visualization is the need of the hour. Here we go. We have a “Microsoft Power BI” as the data visualization tool.

We have used MS Excel for numbers crunching, data analysis, and visualization. But now we have Power BI for visualization. With a simple drag and drop technique, we can create our visualization of the data. This article will show you the DAX function and how to start with it in Power BI.

Power-BI-DAX

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI DAX (wallstreetmojo.com)

Upload Data to Power Bi

You can download the workbook used in this example to practice with us from the link below.

You can download this Power BI DAX Excel Template here – Power BI DAX Excel Template

Below is the workbook data we are using.

Power Bi Dax (data)

It is huge data. You can download the workbook here.

  • Now, open Power Bi software and click on “Get Data”>>> “Excel.”
Power Bi Dax (Get data)
  • Now, the file picker window will come. Choose the downloaded file from the same location.
Power Bi Dax (Data open)
  • You can see worksheet names and tables in the selected Excel workbook as soon as you choose the file.
Power Bi Dax (Navigator)
  • In the workbook, we have named the table “Sales,” so choose the same. Then, you will see the preview on the right-hand side and click on the “Upload” option.
Power Bi Dax (Data Load)
  • It will take some time to upload since the data is large. After the upload, you will see the below window.
Power Bi Dax (Desktop)
  • On the right-hand side of the window, you will see the “Fields” list, which shows all the data headings in the selected table.
Power Bi Dax (Fields)

Now with this data, we need to play around by using DAX functions in Power BI.

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

DAX Calculation in Power BI

If you see the data, we have “Total Units,” “Sale Price,” and “Cost Price” only. So, we need to find “Total Sales,” “Total Cost,” “Gross Profit,” etc.

First, let us calculate “Total Sales.”

  • Right-click on the table name “Sales” and choose “New column.”
Power Bi Dax (New Column)
  • Now, the “New column” will open like the one below.
Power Bi Dax (Column)
  • Now, give a name to this as “Total Sales” and put an equal sign.
Power Bi Dax (Total Sales)
  • Now, apply the formula as “Total Units * Sales Price.”
Power Bi Dax (Total Sales Formula)

As you can see above formula says “Sales [Total Units] * Sales [Sale Price].”

  • Here, “Total Sales” is the table name of the inserted table. So now, we can view this new column in the “Fields” list under the “Total Sales” table.
Power Bi Dax (Total Sales Column)
  • Take “Card” visualization and insert a newly created column, Total Sales.
Power Bi Dax (Card)
  • Then, the card looks like as shown below:
Power Bi Dax (Total Sales Card)
  • So, our first DAX calculation is working fine. Now again, right-click the “Sales” table name and choose “New Column” for “Total Cost.”
Power Bi Dax (Total Cost)
  • The “Total Cost” formula is “Total Units * Cost Price.”
Power Bi Dax (Total Cost Formula)
  • Now, the newly created column appears. We inserted one more card for this DAX column as well.
Total Cost Card
  • Find the “Gross Profit” amount using the formula: Total Sales – Total Cost.
Gross Profit)
Power Bi Dax (Gross Profit %)

Now, we will see how to use some basic functions in Power BI DAX.

Functions in DAX Power BI

To store all the calculated fields, let us create a new measure. First, click on the “Enter Data” option under the “Home” tab.

Enter data
  • Now, create a table that comes up, which gives a name as “Calculated Columns,” and click on “Load.”
Create Table
  • Now, we can see a new table name in the fields.
Calculated Columns
New Measure

It pop-ups in the “Formula bar.”

Measure
  • Before we open the SUM function, we need to give a name for this column. Since we are adding “Total Sales,” give the name “SUM Total Sales” and then open the SUM function.
Sum Function
  • As you can see, the argument of the SUM function is “ColumnName,” i.e., which column you need to SUM. Now, select the “Total Sales” column.
Power Bi Dax (Sum Total Sales)

It also works the same way previous “Total Sales” works. But, previous sales calculate the sales as Units * Sales Price. So, since “Total Sales” already has the sales amount, this SUM function adds all the values of the “Total Sales” column. Like this, we can use DAX functions in Power BI.

Note: We can also download the Power BI DAX function file from the link below. We can view the final output.

You can download this Power BI DAX Template here – Power BI DAX Template

Things to Remember Here

This article is a guide to Power BI DAX. Here, we discuss using the DAX function to calculate DAX in Power BI. You may learn more about Power BI from the following articles: –