Power BI: What is the difference between DAX and Excel formulas?

Power BI doesn’t just look great, it also yields great results in just a few hours. Watching a demo will already leave you impressed, but when you try it with more complicated calculations and datasets that are more complex than the average demo dataset, you’ll see the true power of this product and the possibilities DAX offers.

When you start using DAX, you might think that it looks strangely much like Excel formulas – but while they do look similar, they work in completely different ways. In order to execute calculations correctly, it’s important that you understand these differences.

  • In Excel, you refer to cells when creating formulas.
  • In Power BI using DAX, you refer to rows and columns – which basically means you refer to tables.
  • Another significant difference is the usage of a Filter and Row Context when using DAX.

I’ll explain this briefly: when using Excel formulas, you calculate e.g. SUM(B2:B9) (see Figure 1). Using DAX, you’ll create a formula that looks like TotalSalesAmount = SUM(Sales[Sales Amount]). This formula will calculate the total sales by adding up the values in the entire column of the table. If you were to use this formula (or measure) in a Product Category table, you’d end up with a result like in Figure 2. For each of the Product Categories, the formula has been filtered to return the expected result.

DAX also has special features, such as ‘iterator functions’ like SUMX. These iterator functions return the result of an expression evaluated for every row in a table: SUMX(<table>; <expression>). In other words, the above-mentioned formula could also be calculated like this: TotalSales = SUMX ( Sales; Sales[Quantity] * Sales[Net Price] )

If you combine these DAX formulas with a slicer to select e.g. the year, the source (being the table) will be filtered and the formula will be executed on this filtered dataset. This is a filter context and one of the reasons why DAX is great for data analysis.

In my next blog posts, I’ll elaborate on Row and Filter contexts.

Our popular ‘Power BI – Core Features 1’ training teaches you how to efficiently use DAX in your reports. During ‘Power BI – Core Features 2’, we go into more detail and explain how to design advanced, powerful measures with which you can run a top-notch analysis. 

Figure 1

Figure 2

Share this blogpost
Categories: Power BI
Tags: Power BI

Also interesting for you

Leave a reply

Your email address will not be published. Required fields are marked.

Virtual workshops Office 365

Are you exploring Office 365 and do you have any questions about the software that need answering? Book one of our virtual workshops today. We’ll help you and your co-workers get up and running with Teams, Power BI, Power Automate, Project Online Professional, and other tools.

Book an Office 365 workshop

Online Coaching: you’ve got questions, our expert has the answers!

Do you have urgent questions about Excel, Project, Power BI or Adobe?  Our Office experts will gladly help you through online coaching!

I want to know more

Bring your knowledge and skills to a higher level

Subscribe for our monthly newsletter and receive:

  • Exclusive tips & tricks
  • Information about our courses
  • Trends on courses
  • Invitations to seminars and events

Most popular courses