Wauw! 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 ith 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. The biggest difference is the following:
- 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
