This page has 0 threads | Add post

Blogs

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

How to use the CALCULATE function in DAX measure formulae Part two of a four-part series of blogs
The CALCULATE function is the most important one in DAX. This blog shows you how to use it to replace, remove and amend the query context for a measure (and also explains what this sentence means!). The CALCULATE function in DAX measures Removing filters in an expression using CALCULATE (this blog) Using the CALCULATE function to replace filters Using the VALUES function to amend the query context This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

How to use the CALCULATE function in DAX measure formulae

Part two of a four-part series of blogs

The CALCULATE function is the most important one in DAX. This blog shows you how to use it to replace, remove and amend the query context for a measure (and also explains what this sentence means!).

The CALCULATE function in DAX measures
Removing filters in an expression using CALCULATE (this blog)
Using the CALCULATE function to replace filters
Using the VALUES function to amend the query context

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

Removing filters in an expression using CALCULATE

For this page, I'll show how to:

remove a single filter from a formula; and
removing two or more filters from a formula.

Let's start with removing a single filter.

DAX takes a lot of getting used to! If you want to meet a real owl, why not book a place on our dedicated two-day DAX course?

Removing a single filter - our example

Suppose that in a pivot table you want to display total sales as a percentage of total sales for all species:

The figure for the cell shown shaded should equal 39 (total sales for amphibians for the East quadrant for Retail Park centres) divided by 430 (the same figure, but for all species). This equals 9.07%.

Any ratio such as the one above can be expressed as A / B, where:

A is the numerator; and
B the denominator

Memories of school maths trickle back? In this case the expression to calculate is:

Take the total sales for the current species, quadrant and centre type, and divide this by the total sales for all species for the current quadrant and centre type.

We're relaxing the species constraint which would otherwise be imposed by the pivot table cell's position within the pivot table.

Here's what our formula should look like:

Calculate the total quantity for the current query context, and divide it by the same figure but without any species constraint (ie for all species).

If you want to try copying it, you could use this:

Fraction of all species: =SUM([Quantity] ) /

CALCULATE(

SUM( 'Transaction'[Quantity] ),

ALL(Species)

)

Here's what this should give:

As a quick sense check, it's vital that the grand totals at the bottom of each column are 100%.

Removing two or more filters

You can have as many additional filters as you like in the CALCULATE function. For example, you might want to show total sales for a particular pivot table cell as a percentage of total sales for that pivot table cell's query context, but for all quadrants and all species.

Here's what this looks like as a fraction, using tuples:

Divide the figure for the current query context by the figure for the current query context without any quadrant or species constraint.

Here's the formula we could use:

Fraction of all species: =SUM([Quantity]) /

CALCULATE(

SUM('Transaction'[Quantity]),

ALL(Species),

ALL(Quadrant)

)

Here's what this would give for our pivot table:

The figure for amphibians in the East quadrant is 39 (total sales for amphibians for the East quadrant for Retail Park centres) divided by 4806 (total sales for all species for all quadrants for Retail Park centres). This equals 0.811%.

You can use the ALLEXCEPT function to remove every filter but one from a formula (although to get it to work you first seem to need to combine the fields you're filtering by into a single table, using the RELATED function).

So much for removing filters; what happens if you want to replace them?

Parts of this blog
The CALCULATE function in DAX measures Removing filters in an expression using CALCULATE (this blog) Using the CALCULATE function to replace filters Using the VALUES function to amend the query context

Some other pages relevant to the above blogs 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

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

Removing filters in an expression using CALCULATE

Removing a single filter - our example

Removing two or more filters

Head office

London

Manchester