In this tutorial, I will cover the two places where you can write your DAX formulas. These two places are the calculated columns and measures. You may watch the full video of this tutorial at the bottom of this blog.
I will go over each one at a time and I’ll start with calculated columns.
Using Calculated Columns
A calculated column is an additional column that doesn’t exist in your raw data source.
This means that we need to add it physically to your data table.
To do this, you put some DAX formula logic into a column to create that additional column. This is very similar to working in Excel and you want to add another column with a formula.
In this example, we will use this fact table that contains all the sales that we’re making in our retail stores and we will add the price of the product.
The price actually already exists in the Products Table here, where we have the Original Sales Price and Current Price.
But to show you how to create a calculated column, I’m also going to add this to the Sales Table.
In a lot of these examples, especially with calculated columns, you don’t actually need to create these columns.
If you’re coming from an Excel background, then you might think you have to, but you don’t have to in Power BI. I’m only doing this to show you what a calculated column is.
But later on, I’m going to show you how you can actually use measures to run these calculations versus adding a physical column inside the data table.
So let’s add the Price here just as our first example.
To create a calculated column, open the Modeling ribbon and select New Column.
I’m going to write some pretty simple logic here to get the Price into this column. I’ll call it Sales Price and then use the RELATED function to reference a column name.
In this case, I’m going to reference the Current Price column. That’s going to give me a sales price for every single item that was sold.
The RELATED function is bringing in the price of each individual product.
And then we could write a new column here and call it as Total Revenue. We use the formula:
Total Revenue = Sales[Quantity] * Sales[Sales Price]
These are just some examples of how you can create a calculated column.
You can also create calculated columns in any table in your data model. It doesn’t have to be just the fact table or the sales table. It can be inside of your LOOKUP Tables as well.
For example, we jump to this detailed Dates Table. Think of these columns as the columns that are going to be filters of your DAX measures.
As I look at this table, I see that there is a dimension here that does not exist currently, which I might want to put into some of my visualisations.
To showcase another way of creating a calculated column, I will use the MonthName column.
The MonthName here is the full month, but I only want the first three letters of each month.
So I’m gonna go to New Column in the Modeling ribbon and call this column as Short Month.
I’m gonna use some logic that you might be familiar with from Excel. I’m going to use LEFT, then find my month name, and I’m going to only use the first three letters of that month name.
Now if we go across to the side, we will see the Short Month column, where we only have the first three letters of each month.
I like to call this adding additional dimensions to analysis because we essentially created another filter that we can use throughout any of our analysis that we do from here on out.
If we go back to the data model, you will see that the Short Month column now exists in our Dates Table and it can filter anything that we do down inside this Sales table.
So if we will run a calculation and count up the quantity, we can now filter it by the Short Month.
I would like to reiterate that it is not recommended that you create these columns in here because we can actually create all of these calculations in memory.
Through creating measures, we can do these internal calculations without having to put them physically inside the table.
That’s a key thing to remember as you learn how to write DAX formula on top of your data tables.
Using Measures
Now let’s talk about measures.
Think about measure as a virtual calculation. It doesn’t actually sit inside your model, but it sits on top of your model.
When you use a measure, it only goes and does a calculation at the time that you use it.
In Excel, every time you run a calculation in the column or in any cell, it recalculates all the time. But in Power BI, a measure only calculates itself when it needs to.
A measure is like a stored calculation procedure that only gets enabled if you use it in a visualization.
So let’s create a simple measure to highlight that point. First, I’m going to select the Sales Table and then select any column in there.
To create a new measure, go to the Modeling ribbon and click on New Measure.
Clicking on the icon opens up this formula bar where you can type in your formula:
I’ll put in Total Quantity Sold to get the sum of the Quantity column in the Sales table.
Now we have this really simple measure, and it is virtually completing its calculation.
It is also calculating everything in memory. In other words, this is calculating the total items that we have sold throughout the time.
The key thing to remember here is that this measure is just stored inside our model, but it doesn’t actually go and run any calculation, unless we drag it on our report page. Then it will go and run the calculation virtually.
So in this case, this measure is virtually going to the Sales table, going to the Quantity column in that table, and then doing a sum over that entire column.
This is actually called an aggregation measure, which we’ll be going over shortly.
Now I’m going to create a new measure and I’m going to call this as Total Sales. Then I’ll use the iterating function called SUMX, which I’ll explain in another model shortly.
I referenced the Sales table, and come up with this formula:
Total Sales = SUMX( Sales, Sales[Quantity] * RELATED( Products[Current Price] )
If you remember, we didn’t physically put this RELATED current price inside the data table. But in this case, I’m virtually putting it inside the data table by incorporating it in this measure.
Then the iterating function SUMX goes to the Sales table and picks every single row in the table multiplied by the quantity by the related current price.
This Total Sales will now give me a result.
You can also do the formatting in the Modeling tab, where your Data type is at the top.
***** Related Links *****
Learn How To Harvest Power BI Slicer Selections To Use Within Other Measures Placing Workday And Weekend Day Numbers Into The Date Table In Power BI Show Results Up To Current Date Or A Specific Date In Power BI
Conclusion
We went over calculated columns and measures, where you can write your DAX formula.
The key thing with calculated columns is that you are physically putting a column of data into your model. If you do that sometimes on some of your larger tables, those can be very large columns.
It is important to recognize that these calculated columns can take up a lot of memory in your model.
They can make your file size larger, and they can sometimes impact performance depending on how big the table is.
But you can counteract this by using measures effectively to run a lot of these calculations virtually. You will still get the same results that you would get by writing these calculated columns.
I hope that this tutorial makes it a lot clearer for you the two places where you can write your DAX formula in and the considerations when writing DAX formulas.
This will also help you understand how to incorporate DAX into your analysis within Power BI.
Enjoy reviewing this one.
Sam
