BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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

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.

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

## 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?

- 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