Power BI DAX

What is DAX in Power BI?

DAX stands for “Data Analysis & Expression”. DAX is a functional language which represents all the function in Excel & Power BI. In simple, we can call it “Function Representor”. Using DAX language we can create formulas in Power BI, 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 then understanding DAX formulas won’t be a difficult task.

Data Visualization is the need of the hour, and 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 even for visualization as well. But now we have Power BI for visualization. With a simple drag and drop technique, we can create our visualization of the data. In this article, we will show you what DAX function is 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 along with us from the below link.

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

Below is the workbook data I am using.

Power Bi Dax (data)

This is a huge data, and 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)
  • As soon you choose the file, you can see worksheet names and tables available in the selected Excel workbook.
Power Bi Dax (Navigator)
  • In the workbook, I have named the table “sales,” so choose the same. On the right-hand side, you will see the preview and click on the “Upload” option.
Power Bi Dax (Data Load)
  • Now 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, and this 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.

DAX Calculation in Power BI

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

First, let’s 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 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 “Sales” is the table name of the inserted table. Now, this new column can be viewed in the field list under “Sales Table.”
Power Bi Dax (Total Sales Column)
  • Now take “Card” visualization and insert a newly created column i.e., 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)
  • For Total Cost, the formula is “Total Units * Cost Price.”
Power Bi Dax (Total Cost Formula)
  • Now the newly created column appears, inserted one more card for this DAX column as well.
Total Cost Card
  • Now find the Gross Profit amount by using the formula Total Sales – Total Cost.
Gross Profit)
Power Bi Dax (Gross Profit %)

Ok, 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 for this. Click on the “Enter Data” option under the “Home” tab.

Enter data
  • Now create a table that comes up, for this 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
  • First, we will use the SUM function to SUM some of the columns. As usual as right-click on the new table name and choose “New Measure.”
New Measure

It pop-ups in 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 as “SUM Total Sales” and then open the SUM function.
Sum Function
  • As you can see, the argument of the SUM function is “Column Name,” i.e., which column you need to SUM. Now select the “Total Sales” column.
Power Bi Dax (Sum Total Sales)

This 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 sales amount, this SUM function simply adds all the values of the “Total Sales” column. Like this, we can use DAX functions in Power BI.

Note: Power BI DAX Function file can also be downloaded from the link below, and the final output can be viewed.

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

Things to Remember Here

  • This is the beginner’s guide for DAX; in upcoming articles, we will more of these DAX functions in Power BI.
  • Power BI is a visualization tool to visualize data.

Guide to Power BI DAX. Here we discuss how to use Data Analysis & Expression function to calculate DAX in Power BI with example. You may learn more about Power BI from the following articles –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>