This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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

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:

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:

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:

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:

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 ...

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

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

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:

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 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:

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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR