Power BI And Excel: Difference In DAX Formulas

In this blog, I will quickly discuss the main difference between Power BI DAX and Excel formulas.

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.

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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

Power Automate Expressions – An Introduction

In this tutorial, weâ€™ll explore Power Automate expressions and how to use them in workflow automation....

List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...