FILTER Function in CALCULATION Function Argument

DAX DAX Calculations
1

Hi every Body
I Have a Question
I Want to know When we use FILTER Function in CALCULATE Argument

For Example:

VAR X = MAX(Date[Date])
CALCULATE([Sales],
FILTER (ALL(Date) , Date[Date]<= X))

OR in this video : Simple and advanced filters with CALCULATE using FILTER function

CALCULATE([TotalSale],
FILTER(Product, Product[ProductGroup] = “Top”))

I Know for example for Filtering Measures We can’t Use CALCULATE, But for These Two example I don’t Know Why we should use FILTER Function Inside CALCULATE.

2

Hi @HosseinVossoughi,

Welcome to the Forum!

There’s a lot of content within the Portal on CALCULATE and FILTER.
Please review these first and get back if you have additional questions.

https://forum.enterprisedna.co/t/calculate-function/3265

https://forum.enterprisedna.co/t/why-do-you-even-have-to-use-calculate-its-all-about-context/4920

https://forum.enterprisedna.co/t/calculate-function-the-most-important-function-in-dax/4391

https://forum.enterprisedna.co/t/simple-and-advanced-filters-with-calculate-using-filter-function/4924

https://forum.enterprisedna.co/t/why-calculate-is-important/4919

https://forum.enterprisedna.co/t/how-are-formula-calculated-when-you-involve-calculate/4922

I hope this is helpful

1 Like
3

thanks @Melissa for your response and thanks @sam.mckay for these great videos. I Saw all of these video but I Couldn’t find any reason to use FILTER function in CALCULATE Argument.

4

Hi @HosseinVossoughi,

So here’s what calculate does: it evaluates an expression in a context modified by filters.
For the second argument (filter(s)) a boolean (True/False) expression or a table expression that defines a filter can be passed.

When a filter argument has the form of a predicate with a single column reference, the expression is internally converted into a FILTER( ALL (table[column] ), Condition - so this:

CALCULATE( 
    <expression>,
    table[column] > 1
)

is is internally translated into this

CALCULATE( 
    <expression>,
    FILTER( 
        ALL( table[column] ),
        table[column] > 1
    )
)

However this doesn’t mean the second argument has to be the FILTER function, it can be any table expression returning a list of values for one or more columns or for an entire expanded table.

I hope this is helpful.

2 Likes
5

@HosseinVossoughi,

To augment the excellent information that @Melissa has provided, I’ve always found this video helpful in addressing this topic with a number of very common and clear-cut examples.

  • Brian
1 Like
6

Thanks @Melissa
can you give me a example that necessary to use FILTER Inside CALCULATE (Except filtering measure)

for example:
VAR X = MAX(Date[Date])
CALCULATE([Sales],
FILTER (ALL(Date) , Date[Date]<= X))

I Can write this like below:
VAR X = MAX(Date[Date])
CALCULATE([Sales], ALL(Date) , Date[Date]<= X)

7

thanks @BrianJ

8

Hi @HosseinVossoughi,

I think you are getting there… if I stick with your example, this:

myMeasure = 
VAR X = MAX( Dates[Date] )
RETURN

CALCULATE([Total Sales],
    Dates[Date]<= X
)

will internally be translated into

myMeasure (full) = 
VAR X = MAX( Dates[Date] )
RETURN

CALCULATE([Total Sales],
    FILTER( ALL( Dates[Date] ),
        Dates[Date]<= X
    )
)

So although you don’t have to write FILTER( ALL( Dates[Date] ) as shown in the first measure that is still what the DAX engine is doing behind the scenes…

Therefor I would recommend to always write it out fully (like the second measure) because as you get more comfortable with writing DAX and the logic get’s more complex it is vital that you understand exactly how the context of an expression has been modified inside CALCULATE.

I hope this is helpful.

2 Likes
9

Thanks @Melissa for your clear explanations