Mastering DAX
Multiple Filters
ith
w
CALCULATE ( )
Function
Azman Rana
Expert Data Analytics
Understanding DAX's New
Feature: Multiple Column Filters
This feature allows you to apply filters across multiple
columns in a single DAX function, making calculations
more efficient, precise, and easier to write.
---
Code Highlight:
Before this update, filtering on multiple columns
required more complex approaches like nested `FILTER`
or manually combining conditions. Now, it’s as simple as
listing your conditions!
dax
CALCULATE(SUM(Sales[Amount]), Sales[Color] = "Red",
Sales[Brand] = "Contoso")
Pro Tip:
Focus on small, precise filters instead of filtering entire
tables for better query performance.
Azman Rana
Expert Data Analytics
01
What’s New?
DAX now supports multiple column filters in
CALCULATE! 🎉
This is a huge improvement for anyone working
with large datasets where filtering across multiple
columns was once cumbersome.
What’s the benefit?
Before, complex filtering conditions across
columns made your code harder to read and
troubleshoot. With this update, DAX can handle:
- Filters on multiple columns in the same table
with ease.
- Streamlined code for faster calculations.
Azman Rana
Expert Data Analytics
Code Example:
Here’s a simple example:
```dax
CALCULATE(SUM(Sales[Amount]),
Sales[Color] = "Red", Sales[Brand] = "Contoso")
```
Before the update: This would throw an error or
need workarounds, especially if you wanted
multiple column filters from different tables.
Azman Rana
Expert Data Analytics
02
Why is it Important?
Previously, filtering on multiple columns was a
challenge. You had to use `FILTER` functions or
complicated logic.
For example, to calculate sales of red products
from the brand Contoso, you would write:
```dax
FILTER(Sales, Sales[Color] = "Red" && Sales[Brand]
= "Contoso")
```
Azman Rana
Expert Data Analytics
Using Multiple Filters
Why is this bad?
- It’s less efficient.
- It’s harder to read.
- It’s prone to mistakes.
With the new update, DAX does this natively using
the `CALCULATE` function.
---
Code Example:
dax
CALCULATE(SUM(Sales[Amount]),
Sales[Color] = "Red", Sales[Brand] = "Contoso")
Helpful Tip:
Always prioritize using `CALCULATE` with simple column filters over `FILTER`
functions where possible. It’s more efficient!
Azman Rana
Expert Data Analytics
03
How CALCULATE() Works
The `CALCULATE` function modifies the filter
context of a measure by applying filters to the
dataset. This allows you to control how calculations
are performed, depending on the filtered data.
Let’s start with a simple example:
I created a measure `Red Sales` that filters sales
where the color is red.
dax
Red Sales = CALCULATE(SUM(Sales[Amount]),
Sales[Color] = "Red")
Helpful Tip:
`CALCULATE` works by adjusting the filter context, meaning you can control
how specific calculations should behave based on different filters.
04
Multiple Filters Example
We now want to calculate sales for:
- Products with the color red
- Products from the brand "Contoso"
The old way of handling this was inefficient, as
we had to either manually manage conditions
or use `FILTER` functions.
dax
CALCULATE(SUM(Sales[Amount]),
Sales[Color] = "Red",
Sales[Brand] = "Contoso")
Azman Rana
Expert Data Analytics
05
Writing the Query
Azman Rana
Expert Data Analytics
06
New Way
Azman Rana
Expert Data Analytics
07
CALCULATE () with
SELECTED VALUE
Azman Rana
Expert Data Analytics
08
Old Approach vs New Approach
Azman Rana
Expert Data Analytics
09
Optimization with ALL
function
Azman Rana
Expert Data Analytics
10
Avoiding Over-Filtration
11
Best Practices in Action
Azman Rana
Expert Data Analytics
12
Filter Multiple Columns Effectively
Azman Rana
Expert Data Analytics
13
Handling Complex Conditions
Azman Rana
Expert Data Analytics
14
Nested CALCULATE ()
Azman Rana
Expert Data Analytics
15
Participate in relevant groups and
discussions
Azman Rana
Expert Data Analytics
16
Keep Filters Modifier
Azman Rana
Expert Data Analytics
17
Practical Example: Keep Filters
in Action
Azman Rana
Expert Data Analytics
18
Boost Efficiency
Azman Rana
Expert Data Analytics
Azman Rana
Expert Data Analytics
20
Nested CALCULATE ()
In complex scenarios, you might need to nest
CALCULATE functions to apply multiple layers
of context modifications.
Azman Rana
Expert Data Analytics
DM me “Data” I will
Send it to you
Azman Rana
Expert Data Analytics
