Advanced DAX

The DAX language was designed specifically so that users could quickly learn the basics and immediately improve the analytical capabilities of their data models. But then what? The DAX language can be difficult to understand once you've mastered the basics and move on to more complex calculations. In last week's webinar, our Business Intelligence Consultant Mitchell Pearson discussed the building blocks necessary for building more complex calculations in DAX.

Expanding on his presentation,  Mitchell has addressed these additional questions (that came up multiple times during the webinar) to help provide a clearer understanding of Advanced DAX.

WHY ARE THE TOTALS FOR FORECAST AND DYNAMIC THE SAME?

The grand total is different as well.

are we sure all those totals are right? for the mls data?

It so happens that all three of these questions can be answered with the same solution! The totals that are generated in DAX are usually the cause of confusion, especially if you are still learning about filter context. Totals do NOT simply add up the values that appear in the rows on the report. This is confusing because most of the time, it looks like this is exactly what is occurring in the background.

If the Totals cell in your table does not add the rows then how does it come up with a total?? Great question! The total cell also executes the DAX formula defined in your measure.

Let’s start with a visual, one from my webinar that prompted the questions leading to this post. Notice in this picture you can clearly see that the “TOTAL” is not the sum of the rows. The values represented in the total row look completely wrong, however they are not.

Remember this question? Why are the totals for Forecast and Dynamic the same? Let’s try to answer this question now. We can begin with Forecast YTD Sales.

Forecast YTD Sales is a simple calculation in the model. It is simply Prior YTD Sales * 2. In the screenshot below I have added the calculated measure Prior YTD Sales to the table so we can visualize exactly how the total cell is being calculated.

The total value is $19,582,120 which is $9,791,060 * 2.

Remember that in our original table there was only data up until June of 2008? Therefore, why is the total row taking Prior YTD Sales from December? Why is the total calculation taking the value of $9,791,060 from the Prior YTD Sales measure instead of $3,037,501 (June)?

The table visual only displayed data up until June but this was because we don’t have any sales past June of 2008. The total row is looking at the entire 2008 year and therefore gets the Prior YTD Sales value as of the last date in 2008 ($9.791,060). Although this may not be the desired behavior, it is the correct behavior.

why are the totals for Forecast and Dynamic  the same?

Well all of that is great, but that still doesn’t explain why the Forecast YTD Sales has the same value as our Dynamic Measure at the total row. The Dynamic Measure calculation always takes YTD Sales unless there are no sales on the last day of the current month in which case it will take the Forecast YTD Sales. This was explained in the webinar so please revisit that if you don’t remember the explanation behind this calculation.

IF(
    LastSaleDate <> LastDayOfMonth,
    [Forecast YTD Sales],
    [YTD Sales])

At the total row, the calculation would read like this:

IF(
    “6/20/2008” <> “12/31/2008”,
    [Forecast YTD Sales],
    [YTD Sales])

Based on this interpretation of the measure we can see that the last day a sale took place in our model does not equal the last date in our model for the current year. That means that the total ROW is going to display the measure [Forecast YTD Sales]. This explains why we see the following values in our total row:

Once again, understanding the total row is not easy, especially if you are transitioning to DAX from working with Excel. If the total row seems incorrect, it’s important to validate the calculated measure at the total level.