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 as “Function Representor”. Using DAX language we can create formulas in Power BI, if you are familiar with Excel formulas then understanding DAX formulas in Power BI 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.
Upload Data to Power Bi
Now we will see how we can use DAX Formulas in Power BI to play with the existing data set. You can download the workbook used in this example to practice along with us from the below link.
Below is the workbook data I am using.
This is a huge data and you can download the workbook here.
- Now open Power Bi software and click on Get Data >>> Excel.
- Now file picker window will come, choose the downloaded file from the same location.
- As soon you choose the file you can see worksheet name and tables available in the selected Excel workbook.
- 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 “Upload” option.
- Now it will take some time to upload since the data is large. After the upload, you will see below window.
- On the right-hand side of the window, you will see “Fields” list and this shows all the data headings in the selected table.
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 table name “Sales” and choose “New Column”.
- Now new column will open like the below.
- Now give a name to this as “Total Sales” and put an equal sign.
- Now apply the formula as “Total Units * Sales Price”.
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”.
- Now take “Card” visualization and insert newly created column i.e. Total Sales.
- Then the Card looks like as shown below:
- So, our first DAX calculation working fine. Now again right-click “Sales” table name and choose “New Column” for “Total Cost”.
- For Total Cost formula is “Total Units * Cost Price”.
- Now newly created column appears, inserted one more card for this DAX column as well.
- Now find Gross Profit amount by using the formula Total Sales – Total Cost.
- Now arrive Gross Profit % by using this formula Gross Profit / Total Sales *100.
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 lets crate new measure for this. Click on the “Enter Data” option under the “Home” tab.
- Now create table comes up, for this gives a name as “Calculated Columns” and click on “Load”.
- Now we can see a new table name in the fields.
- 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.”
It pop-ups in Formula bar.
- Before we open 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 SUM function.
- As you can see the argument of SUM function is “Column Name” i.e. which column you need to SUM. Now select the “Total Sales” column.
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 “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.
Things to Remember Here
- This is the beginners 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 –