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.
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.
Your email address will not be published. Required fields are marked.
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.
Do you have urgent questions about Excel, Project, Power BI or Adobe? Our Office experts will gladly help you through online coaching!
Subscribe for our monthly newsletter and receive: