Filtering a Column via a Visual AND CALCULATE - Excelerator BI

Filtering a Column via a Visual AND CALCULATE

Level: Intermediate.

Sometimes, just when you think you are getting the hang of the DAX language, something happens that completely baffles you.  Such events can cause you to doubt your own learning and progress. But let me assure you – this happens to EVERYONE at sometime or another.  In this article I am going to show you one such baffling situation, and then explain what is really happening.

The Scenario

I am using a simple Adventure Works data model for this demo.

image

The final objective is to write the following measure

Total Northern Hemisphere Sales in 2003

In other words, I want a measure that will give me sales for all the countries excluding Australia.  (BTW, did you know that there is just 5.7% of the global population that has summer in Dec – Feb each year (ie summer in the southern hemisphere)? – read my article about that here).

I have set up a table that contains the Territory[Country] on Rows and I have added 2 measures.

Total Sales = SUM(Sales[ExtendedAmount])

Total Sales in 2003 = SUM([Total Sales],'Calendar'[Year]=2003)

image

All that is required now is to remove Australia in the measure to deliver the final required measure.  Here is the first attempt at such a measure

Total Northern Hemisphere Sales in 2003 = CALCULATE([Total Sales],
       'Calendar'[Year]=2003,
       Territory[Country]<>"Australia")

When this measure is added to the above pivot table, this is what happens.

image

The measure indeed is returning Total Northern Hemisphere Sales in 2003, however the problem is that it ALWAYS gives the TOTAL sales regardless of any other filter coming from one of the Countries.  Plus, what’s worse, Australia pops up again when the whole purpose of the measure was to exclude Australia from the calculation.  This may not be what you expect and sometimes not what you want.  Arguably it would be more useful if this measure returned $6.8M at the Grand Total line, but still returned the sales by country when sliced by each country (and excluded Australia in the table).

Syntax Sugar

To understand what is happening here, I need to cover the topic of “syntax sugar”.  Syntax sugar is a term that the Microsoft developers use to describe the way they simplify the syntax of DAX.  They do this to make it easier for people to get started with the DAX language without having to be an expert.  This is indeed helpful, but by definition it hides more complex principles from the DAX author, and these can come back to confuse you at some time in the future.

Total Sales from Bikes

Let me take a slight diversion from the DAX formula above.  Say you wanted to write a measure “Total Sales from Bikes”.  Thanks to syntax sugar, this is very easy to do.

Total Sales from Bikes = CALCULATE([Total Sales],Product[Category]="Bikes")

But this simple syntax is just syntax sugar for a more complex formula.  Under the hood, this is the actual formula that gets executed.

Total Sales from Bikes =
CALCULATE (
    [Total Sales],
    FILTER ( ALL ( Product[Category] ), Product[Category] = "Bikes" )
)

The second formula is harder to learn than the first one.  Of course you CAN learn it, but it is just not the first thing the average person wants to learn when the objective is to do something as basic as returning Total Sales from Bikes.  There is a lot going on with this second formula including a FILTER function and also the ALL function – not something you would want to learn in the first couple of hours of your DAX journey – hence the purpose of the syntax sugar concept.

The Full Formula From Above

Back to the original formula Total Northern Hemisphere Sales in 2003.  You now know that the full actual formula is as follows.

image

There are 2 FILTER functions ‘hidden’ from the simple syntax that are visible in the full formula above. Each of these formulas first removes the filters applied to the columns (Calendar[Year] and Territory[Country]) and then each FILTER re-applies a new filter on that same column.  So this explains the strange behaviour shown at the start of this article.  Line 5 in the formula above removes the filters coming from Territory[Country] (which is in the rows section of the table below), and then reapplies a new filter on the Territory[Country] table so that it returns all countries other than Australia, returning $6.8M for every row in the table.

image

As you can see above, this can be confusing because the rows in table appear to give the wrong answer.  In this case, the rows in the table above are “fighting” with the CALCULATE function for the right to apply a filter on the Country[Territory] column.  When a filter is applied to a column from a visual and then another filter is applied on the same column in a CALCULATE, the CALCULATE always wins.  So the visual in the row highlighted above says “put a filter on Australia” but the CALCULATE inside the measure [Total Northern Hemisphere Sales in 2003] says “put a filter on all countries except Australia”.  They are “arguing” over the same column and CALCULATE wins.

Why have the ALL function at all?

The next question that may come to mind is “why have the ALL function at all”?  Why doesn’t the syntax sugar just return the following?

image

If I add the above formula to the table, this is what happens.

image

This time the measure returns something that is more intuitive for the average user ( I think you will agree).  So why didn’t the developers use this formula as the syntax sugar instead of the version including ALL?  Well I can only speculate (others may know more than me), but one thing I know for sure is that where ever possible, it is advisable to operate on a single column in preference to working on an entire table.  This is particularly true in formulas that are iterators (such as FILTER and the X Functions).  The Vertipaq engine is designed to store data in Columns and as a consequence it operates most efficiently when it acts on single columns.   In the case of the Territory table in this example, the number of rows in the Territory table and the number of distinct values in the Country column are not that different (as can be seen below).  So in this case the performance difference would not be significant, however the principle of operating on columns rather than tables is still well advised.

image

An Alternative Formula using VALUES

There is an alternative formula that uses columns and still gives the more intuitive result. In fact there are a few options that could work and this is just one.

image

The formula above uses VALUES instead of ALL inside the FILTER function.  This is more efficient than referring to the entire table and has the added benefit of respecting the filter applied by the rows in the visual.

image

Wrap Up

So why didn’t the developers use VALUES underneath the syntax sugar of a simple CALCULATE instead of ALL?  Once again, I don’t know the answer to that – maybe others know more than me.  Maybe the VALUES function didn’t exist when they first came up with the simple syntax for CALCULATE, maybe there is some other technical reason that ALL is superior.  But it doesn’t really matter once you understand more about how the DAX language works, because you can then use your skills, knowledge and experience to solve almost any problem you come across.

16 thoughts on “Filtering a Column via a Visual AND CALCULATE”

  1. My opinion is that it is this way because CALCULATE is intended to change the filter context. When you use VALUES you are not changing the filter context, only when using ALL inside FILTER in this case. If you changed the natural behaviour to VALUES, it would not make sense anymore when using Time Intelligence Functions inside CALCULATE, such as DATESYTD for example.

    These time intelligence functions change the filter context by applying ALL inside them, and not VALUES.

    So for me it this behaviour is natural. But I admit it takes time.

    Hope I made sense 😀

  2. With syntax sugar and probably best practice:

    Total Northern Hemisphere Sales in 2003 (Keepfilters) =
    CALCULATE (
    [Total Sales],
    ‘Calendar'[Year] = 2003,
    KEEPFILTERS ( Territory[Country] <> “Australia” )
    )

    1. Thanks Frank – I knew someone would know. I see KEEPFILTERS a bit in the query plans and never really looked into it because I didn’t know what it did or why I needed it. I will take a look.

  3. Ariana Newcombe

    I have just bought your book “Learn to Write DAX. A practical guide to learning Power Pivot for Excel and Power BI”. Does your book come with samples files? I am new to this topic and I would greatly appreciate “a learning schedule” from an expert. Learning it looks overwhelming without clear learning path. Thank you. I also bought “Power Pivot . Power BI 2 nd edition” of Rob Collie & Avichal Singh.

  4. What a great question: why does Territory[Country]=”Australia” execute as FILTER(ALL(Territory[Country]), Territory[Country]=”Australia”) and not something else.

    Right off the bat I would say that the answer is that THAT FILTER construction is the simplest filter you can create without introducing subtle side effects in which extra filtering is done or extra semantics is introduced. That is the case with FILTER(VALUES(Territory[Country]), Territory[Country]=”Australia”). FILTER(Territory, Territory[Country] =”Australia”) would involve extra filtering AND extra semantics and both of the latter DAX expressions would necessitate an understanding of an empty filter/set.

    It’s a very good question because it involves the very basic of DAX and leads to a better understanding of more complex filtering. Remember FILTER() is the Swiss army knife of DAX filtering.

    1. That makes sense to me Jess, and I also assumed something like that. I have tried to read Alberto Ferrari’s article about ALLSELECTED, and I have not been able to complete the reading as yet – it does my brain in. But that is actually one of the things I love about DAX – it is a lot like an onion, and there is always another layer to peel back to get a deeper understanding.

      1. That onion/layer analogy is really spot on Matt. I have had the same experience with DAX. Just as you think that now she’ll be apples another layer reveals itself. In our defense I think that even Alberto and Marco experience that every once in a while. Look at my comment to this blog post: https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

        I don’t know about you but I can’t help admiring people who are super experts but still can admit that there are corners to be investigated. It sort of gives hope to us mere mortals and as you mention yourself that is what makes DAX fascinating.

        The Alberto ALLSELECTED article is definitely a tough one, we just have to remember that it deals with a corner of DAX i.e. shadow filter context that is just that – a corner. The majority of scenarios involving ALLSELECTED can be explained in a much simpler way.

        Here is the simple definition I use inside my head:
        ‘The purpose of ALLSELECTED is to remove the internal filter context unless this filter context has been explicitly changed since its creation. This makes it possible to filter using the external filter context. ‘

        The internal filter context is the filter context that comes from the axes of a pivot table or a power BI visual. The axes would typically be rows and columns and legends etc. The external filter context is the combination of slicers and filters.

        When you deal with iterators the internal filter context is the current row being processed. The external filter context is the table being iterated.

        Explicit filter context is the filter context you add in the argument list of CALCULATE and that more often than not will overwrite the internal and external filter context.

        In his article Alberto mainly uses queries to explain his point on shadow filter context this so he can create his own ( A query doesn’t have an external/internal filter context when it starts out). He probably stopped writing the article when he reached 20 odd pages, but had he continued and included scenarios with concatenated filters things would have been even more interesting and tougher. 

        Jes.

  5. Matt, Thanks for another interesting read. For the record, when you framed the question (how to get Northern and Southern Hemisphere totals) my mind went in a different direction. I considered creating a table that grouped countries and then I remembered Power BI has a built in Function called “New Groups” where you can assign the countries to Hemispheres without having to change any calculations.

  6. Matthew Runyon

    Ah! I ran into this problem the other day, and eventually fixed it by explicitly using the FILTER on the different filters, but I didn’t know why that fixed it. Thanks!

Leave a Comment

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

Scroll to Top