How to use the FILTER function in DAX to filter tables
Although the CALCULATE function is by more useful (and often easier to understand), DAX programmers should also learn about the FILTER function. This provides another way to change the query context for any aggregation.

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 12 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using the DAX FILTER function to filter data

This blog shows how you can filter tables to aggregate only over subsets of data.

Using filtered data as a replacement for a full table

Many DAX expressions ask for a table name:

Typical DAX function

A typical DAX function, asking for a table as one of its arguments.

 

Wherever this happens, you can supply a filtered subset of the full table's data instead, using the FILTER function.

In many cases you can use the CALCULATE function instead of the FILTER function to produce the same results (the resulting formula is usually easier to understand, too).

Our worked example: dividing transactions into cheap and expensive

Suppose we want to divide sales up as follows:

Expensive and cheap sales

We'll create 3 measures, showing cheap, expensive and total sales. A cheap sale is one where the price was £10 or less; expensive implies a price of more than £10.

 

Note that you could solve this problem by creating a new aggregator column:

Calculated column solution

One way of solving the problem - but it wouldn't teach you anything about the FILTER function!

 

Syntax of the FILTER function

Here's how to write a FILTER function in DAX:

=FILTER ( Name of a table, Conditional expression )

Our three measures could therefore be:

Measure Formula
Cheap =SUMX( FILTER('Transaction',[Price]<=10),[Quantity])
Expensive =SUMX( FILTER('Transaction',[Price]>10),[Quantity])
Total =SUM('Transaction'[Quantity])

So for example the Cheap measure will sum the quantity, but only where the price for a transaction is less than or equal to 10 pounds.

Explanation of how this works

To see how the formula above works, consider this shaded cell, giving the total quantity of birds sold for cheap items in the West quadrant for Factory Outlet stores:

Cheap items only

The number 6 in the shaded cell is calculated as below.

 

If you double-click on the total sales for Bird items for the given centre type and quadrant ...

Drill-down on bird sales

If you double-click on the selected cell containing the number 10 ...

 

... you get this set of rows underlying it:

Set of cheap items

The first six cells (shown selected above) give sales of cheap items where the price is less than or equal to £10.  The sum of the quantities is 6, which explains where our figure in the pivot table comes from (the equivalent total quantity for expensive items is 4).

You could have achieved exactly the same effect with the formula =CALCULATE( SUM( [Quantity]),'Transaction'[Price]<=10 ).  This is IMHO much easier to understand!

Removing filters altogether

Instead of using the FILTER function to reduce the underlying set of rows for any query context, you can also use the ALL function to remove any constraints.  Here's an example:

Percent of total sales

We'll calculate the percentage of total sales for each query context.

You could do this using CALCULATE by removing each dimension constraint individually, but the FILTER function allows you to remove all constraints in one go.

Here's the formula that you could use for this statistic:

Percent of total:=SUM([Quantity]) /

SUMX(

ALL( 'Transaction'),

[Quantity]

)

This will divide the total quantity of sales for the current pivot table cell by the total quantity of sales for all transactions.  For tuple aficionados:

The ALL function

The ALL function removes all constraints from the denominator in our ratio.

 

Removing and then reinstating filters

Suppose that you want to show total sales as a percentage of the total sales for mammals:

Percent of total mammal sales

We want to show total sales for each cell as a percentage of total sales for mammals. The only cell which should yield 100% is the one shown shaded.

Here's a formula which will produce these results:

Percent of total:=SUM([Quantity]) /

SUMX(

FILTER(

ALL( 'Transaction'),

[Species] = "Mammal"

),

[Quantity]

)

So the denominator of the ratio calculates the total quantity for all transactions (jettisoning any query context constraints), but with an additional requirement that the species should be Mammal.

This blog has 0 threads Add post