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




Emergent updaterenewal cheats -- Cyberpunk 2077 | Doom Eternal | Watch Dogs:Legion | Resident Evil 3 | The Last of Us Part II | Assassin's Creed Valhalla

Now at any moment 328 games.

Our web-site >

It's just today.

Leave a reply

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

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