For those used to LODs and filters in Tableau, trying to do the same thing in DAX can sometimes feel awkward and confusing. While in Tableau you can calculate, for instance, the average of something per category simply by wrapping it in a FIXED LOD and not thinking much more about it, in DAX it can feel like there are, conservatively, about 20,000 different functions involved, depending on your use case. Furthermore, the best approach to do the same can be dependent on your data model, the filters active on the page, and the variables you have in the view. Without an understanding of what each function is doing and why Power BI behaves the way it does, this can get very confusing very quickly.
In this blog, I will explain the difference between some filter functions in DAX: how to use them, and why they behave the way they do.
Filter Context and the ubiquitous CALCULATE() function
In Tableau, a filter is something that add or remove parts of your data, e.g. taking the data from only a single year or customers who meet a certain condition. However, in Power BI, the term filter also applies to the equivalent of dimensions in Tableau: a variable in the view that segregates the data somehow. If I create a table in Power BI showing the sum of Sales per Store Location, Power BI will consider Store Location a filter – specifically, a row or column filter. The Tableau kind of filter is referred to as an external filter or a slicer.
This is important to keep in mind when we think about what a FIXED LOD would look like in Power BI. In Tableau, you fix your calculation on a/multiple given dimension(s), e.g.
{FIXED [Dimension]: SUM([Measure]}
Because of Tableau's order of operations, this will ignore any filters on the data and fix the data only to the specified Dimension. But in Power BI, we're not dealing with dimensions: we're dealing with filters. So how do we change the filters that are being applied to our measure?
The answer to that is the CALCULATE() function. The official documentation says that it "evaluates an expression in a modified filter context". In less jargony terms, it allows you, the user, to specify what filters you want to apply to your measure and which you don't. To use Tableay terminology, this will allow us to "fix" our measure on certain "dimensions".
This function can take many arguments, but the first is the "expression to be evaluated", aka the measure or aggregate you want to modify the filter context of. The second argument onwards are were you specify how you want to modify the filter context. And for that, we need to look into some other useful functions...
ALL()
Slightly confusingly, using the ALL() function means that NO filters will be applied to your measure. Or to put it another way (presumably the way the person who came up with this syntax envisioned it), it will remove all filters.
Let's say you're looking at the classic Tableau Superstore dataset and you've made a table containing a field for Product Name and the Sum of Sales for that product. If you added a measure specified as below:
Fixed Sum of Sales = CALCULATE(SUM([Sales]), ALL('Orders'))
that field would show you the total Sum of Sales on every row. In other words, this is basically the equivalent of fixing your LOD on nothing.
Note that in the example given here, the function takes the argument of a table, 'Orders'. This argument tells the function where you would like to remove filters. Specifying just the table means that filters from any field in the table will be ignored. You can also specify a table and column(s) (e.g. in this case, ALL('Orders'[Product Name]), in which case it would only ignore filtering on that column.
ALLSELECTED()
You might be wondering, how is this different to the similarly named ALLSELECTED()? This function works very similarly to ALL(), with one very important caveat: it will not ignore external filters and slicers on your data. This is very useful when you want to have a slicer affecting your visual, e.g. in that case calculating percent of total for what's in the view. ALLSELECTED() accepts as an argument either a table, a column, multiple columns from the same table, again specifying where to remove filters.
Note: technically, both ALL() and ALLSELECTED() output a table. This means you can use an ALL() or ALLSELECTED() function as an argument within other functions that require a table.
ALLEXCEPT()
What about if you want to fix an aggregation on a specific measure, as one might with a Fixed LOD in Tableau? For instance, let's say you have a graph showing Sales per Region and Product over Time. Maybe you want to find the average Sales per Region regardless of the other filters in the view (Product and Time). That would be an excellent use case for ALLEXCEPT(). This function tells Power BI to remove all filters except whatever is specified within the function.
The first argument specifies a table from which to remove context filters, and further arguments specify the column(s) where you want to keep the filter active.
(Note: the table you specify should be related to the filters you want to remove AND keep, keeping in mind that most dreaded of Power BI words cross-filter direction. To put it in layman's terms, dimension tables can’t “see” fact tables even then they are connected within your model, because Power BI only lets a table "look" upstream. As such, when dealing with a complex data model, make sure you specify a fact table that can “see” all the other tables you want to remove or preserve filters from.)
FILTER()
To add even more confusion to the mix of filters, you also have a FILTER() function in DAX. But what kind of filter does this refer to?
FILTER() works more like an external filter or slicer than a row/column filter. This function will return a subset of a given table (first argument) based on a filter condition, some kind of boolean expression (second argument).
Technically you don't need to use FILTER() to filter your data: a boolean expression alone will work just the same. While ‘Sales’[Region] = “West” will work just fine, this is a shortcut for:
FILTER(ALL('Sales’[Region]),‘Sales’[Region] = “West”)
Especially when learning DAX, it can be a good idea to write things out in full rather than using shortcuts, so you know exactly what's happening.
KEEPFILTERS() and REMOVEFILTERS()
There is a very similar function called KEEPFILTERS(). The arguments for this function are specifically the fields in which you would like to keep the filters. Pretty self-explanatory!
In almost every context, FILTER() and KEEPFILTERS() will do exactly the same thing, but KEEPFILTERS() is computationally more efficient. Additionally, if you have a more complex use case where you need to nest multiple filters, for instance with REMOVEFILTERS(), you would need to use KEEPFILTERS() instead.
So what is REMOVEFILTERS()? As the name implies, this removes filters. Straightforward, right? You can input a table and/or column(s) from which to remove filters, or you can leave it empty of arguments to remove all filters, similar to ALL() and ALLSELECTED(). With no arguments, REMOVEFILTERS() is essentially equivalent to ALL(), but it is computationally more efficient because it doesn't create a table.
Conclusion
As you can see, there are many filter-related functions in DAX. Some of them are, in my opinion, a little confusingly named, and sometimes it can be hard to decide which function suits your use case, since in many cases there are multiple ways of achieving the same end goal. But if one keeps in mind the particulars of each function, it might be a little easier to choose. Hopefully this blog has given you some of the tools to do that.
Happy filtering!