In this blog, I will quickly discuss the main difference between Power BI DAX and Excel formulas.
I’ll show you some examples to help you see this better.
I’m going to use the data that I used in the previous blog that tackled the differences between Measures and Calculated Columns. You may watch the full video of this tutorial at the bottom of this blog.
Let’s get straight to it.
Excel Formulas
I have a small sample size of my sales data containing details about transactions that I’ve made.
In this section I’m going to use Excel to show you how calculating things here works.
In Excel, if you need to make some calculations, you have to think in regards of specific cells.
Let’s say I want to go and grab the Price. Since the price data doesn’t exist in my small sample size of data, I’ll get it from the Products table.
First, I’ll create a price column by clicking on the topmost cell of Column H and then type in Price.
And then in the same column, I’ll go to the second cell from the top and then type in the following formula:
VLOOKUP Function
Here, I’ve used the VLOOKUP function. This formula scans the Products table and then returns the price depending on the product that I’ve looked up.
Since I looked up the price of the product in cell B2, the formula will then return the price of that product.
To copy the same formula and get the prices of the rest of the products, simply hover your mouse over the lower right corner of the cell and double-click.
Since I have the same products in my small sample size you’ll see that the prices are all the same.
Now, I’ll make a new column for the sales.
Click the first cell of Column I and then type in Sales.
To get the Sales number, I need to multiply the values in the Quantity and Price columns.
To do this I had to set up the following formula:
This formula simply multiplies the value from cell G2 in the Quantity column to the value from cell H2 in the Price column.
Again, to copy the same formula for the rest of the Sales number hover your mouse over the lower right corner of the cell and then double-click.
This is what it should look like after doing the above:
When making formulas with Excel, I’m only referencing cells, and then I would just copy the formula to the remaining rows.
The formula then automatically adjusts by changing the cell that is referenced depending on the row that you’re in.
If you take a look at cell I7, you can see that the cells that are being referenced changed to G7 and H7.
There is nothing like this in Power BI.
Power BI Formulas
Here is a sample formula that was used in Power BI.
In Power BI, everything that you reference is either a column or a table. And if you want to start transitioning from using Excel to Power BI, then you need to start thinking that way.
In the previous blog, I made a Calculated Column named Short Month.
As you can see in the Formula Bar, I’m always referencing columns. Here, I used the MonthName column from the Dates table.
This is exactly the same with DAX measures.
In the sales measure that I made, I only referenced tables and columns. In this formula, I utilized the Sales table, the Quantity column, and the Current Price column.
Also, the calculations are created by the filtering that I have in my model rather than what is in a specific cell.
You can see that the calculations are filtered by the Dates.
The filter in the measure is made possible by the relationship of the Dates table to the Fact table in this model.
So what happens is that based on the filter that we have, the formula will then evaluate through all of the results in the columns that we used in our formula rather than just in specific cells.
***** Related Links*****
Want A Data Driven Culture? Why Power BI Should Be Your Tool Of Choice
DAX Formula Reference Guide
Four Pillars Of All High-Quality Power BI Solutions
Conclusion
In this tutorial I went over some differences between Excel and DAX formulas.
The main thing to take note of is that in Excel formulas you always have to think in terms of cells. On the other hand, you should think about using columns and tables when you’re making DAX formulas in Power BI.
I hope this helps shift your mindset when you want to transition from Excel to Power BI.
All the best,
Sam
