Dax Me Anything

This guide assumes you’re familiar with DAX concepts like Row Context and the CALCULATE function. If not, don’t worry—I’ve got you covered!

• Filter Context
• Calculate Function

As discussed before in the blog about filter context, the filters listed in the visual affects the calculation of the measure but is it possible to alter how the external filters affect the calculate function from inside the calculate function itself? well, this is where functions like Remove Filters, All, keep filters come into play.

Let us take a real-world example to understand this better.

---

Explanation Through Story Telling:

Meet Timmy, an entrepreneurial elementary school student running a marble-selling business.

Timmy wanted to calculate the number of marbles sold, categorized by each color.

Timmy knows he can achieve this result by simply creating a Table Visual:

• Setting the first column to color
• Setting the second column to the Sum of count

Timmy achieved the result he wanted, but recently he’s been learning about functions that can modify how external filters impact the CALCULATE function. Curious, he decided to test them out.

Timmy ended up with 3 different situations:

Situation A

Count Of Marbles Set Filter To Yellow =
CALCULATE(
    SUM('Order Items'[count]),
    'Order Items'[color] = "yellow"
)

//Which is Equivalent to:

Count Of Marbles Set Filter To Yellow =
CALCULATE(
    SUM('Order Items'[count]),
    FILTER(
        ALL('Order Items'[color]),
        'Order Items'[color] = "yellow"
    )
)

Note: When you create a calculate function and create a simple condition such as ‘Order Items’[color] = “yellow”, what happens in the background is actually that a filter function is created which is represented in the second section, the filter function takes a table and return the values from the table that matches the condition set by the filter.

The function All will ignore any external filter set by the visualization or drop-down filter to the column ‘Order Items’[color] and then we set our own filter which is ‘Order Items’[color] = “yellow”.

Timmy finished the first measure and added it alongside the sum of count and checked the difference.

Timmy discovered that in this scenario, the measure overrides any filters applied by the visual to the ‘Order Items’[Color] column. Instead, it applies its own filter, setting ‘Order Items’[Color] = “Yellow.” As a result, it calculates the number of yellow marbles for each entry, regardless of the color filter specified in the visual.

Situation B

Removing external filters without setting any of your own inside the calculate function.

Count Of Marbles Remove All Color Filters = CALCULATE(
    SUM('Order Items'[count]),
    REMOVEFILTERS('Order Items'[color])
)

//Which is Equivalent to:

Count Of Marbles Remove All Color Filters = CALCULATE(
    SUM('Order Items'[count]),
    ALL('Order Items'[color])
)

Note: REMOVEFILTER AND ALL are similar except that ALL return a table of the values while REMOVEFILTERS affects the filter but doesn’t return a table.

Timmy finished the second measure and added it alongside the others.

In this scenario, Timmy discovered that this measure ignores any filters applied by the visual to the ‘Order Items’[Color] column. As a result, it calculates the total number of marbles at each entry in the visual/slicer, regardless of their color.

Situation C

Keeping the external filters, while using your own filter inside of calculate.

Count Of Marbles Keep Color Filters =
CALCULATE(
    SUM('Order Items'[count]),
    KEEPFILTERS('Order Items'[color] = "yellow")
)

//Which is Equivalent to:

Count Of Marbles Keep Color Filters =
CALCULATE(
    SUM('Order Items'[count]),
    KEEPFILTERS(FILTER(VALUES('Order Items'[color]), 'Order Items'[color] = "yellow"))
)

Note: The “Values” function here is used to convert the Order Items[color] Column into a single column table as the “Filter” Function only accepts tables.

Timmy finished the third measure and added it alongside the others.

In this case:

• The filter from the visual remains active.
• The filter applied within the Calculate function also remains active.

This leads to two possible scenarios:

• When the external filter (from the visual) matches the internal filter (from the Calculate function):
  – For example, if the external filter is “yellow” and the internal filter is also “yellow” the measure calculates the number of marbles with the color yellow.
• When the external filter (from the visual) and the internal filter (from the Calculate function) do not match:
  – For instance, if the external filter is “white” and the internal filter is “yellow,” the measure will return nothing. This is because no marble can simultaneously be both “white” and “yellow.”
  – Conceptually, it is like filtering the marbles to only those that are “white,” and then further filtering that subset to only those that are “yellow,” resulting in an empty set.

You might wonder when such a function is useful. Functions like this are essential for calculations such as year-to-date metrics and other advanced scenarios, which we will explore later.