Filter functions

DAX has a class of functions that perform data filtering. For given arguments, as a result, they return filtered table. Filter functions are usually used in combination with another DAX function, and their specificity is to allow creating measures that ignore the limitations of existing filters in a Pivot Table, as well as interactive filters such as Slicers and Timeline. In this recipe you will be briefly acquainted with these functions.

The most common filter function we use is FILTER, and its syntax is:

FILTER(<table name>,<filtering condition>)

It is commonly used in combination with iterator function or function CALCULATE. For example, if we want to sum quantities of all beers sold within a retail store, should write an expression:

SumaProdatihKolicinaPiva:= SUMX(FILTER(Artikli,[KLASIFIKACIJA]=”PIVA”),[SUM of KOL])

Function FILTER is first argument of SUMX. It should return filtered table, with all rows in table Artikli (Items) that have classification „PIVA“ (BEER). Other argument is previously created implicit measure (sum of all quantities). Remember, first argument of iterator is a filter, second has to be a measure.

Function ALL is more complex. Its syntax is:

ALL( {<table name> | <column1>[, <column2>[, <column3>[,…]]]} )

If we only enter first argument, a table name, it should return all values of all columns, i.e. it should „turn off“ all filters, including slicers and timeline filters. For example, to see sum of all quantities we should write a formula:

SveKolicine:=SUMX(ALL(Transakcije),[SUM of KOL])

In addition to the above, function ALL can have one or more columns as arguments. This means that it remove filters only for given columns. For example:

SkoroSveKolicine:=SUMX(ALL(Transakcije[GODINA],Transakcije[MESEC]),[SUM of KOL])

If you pay attention, you’ll see that slicers for measures SveKolicine and SkoroSveKolicine “do not work”. On the other hand, they have effectively filtered implicit measure SUM Of KOL.

For filtering we can also use ALLEXCEPT function. It is used to obtain all the values in the table except for a few, which are allowed to use filters. Its syntax is:

ALLEXCEPT(<table>,<column1>[,<column2>[,…]]) 

At the end let’s mention function ALLSELECTED:

ALLSELECTED([<table> | <column>])  

Aim of this function is to emphasize appliance of filters by giving a name of the table or column which is used for filtering.