Must I explicitly use "filter( )" when using CALCULATE?

[michellepace]

Hello,

The two dax expressions below yield the same results. If I do not explicitly state "filter" when I am using calculate, will I eventually get myself into trouble? 

[amitchandak]

@michellepace , to overserve the difference; try this

have only one filter

[product name] = "Product 2" and check it this measure with axis/row/group as the product name.  Same filter same axis , try both option in different visual

[michellepace · Accepted solution]

Thank you both for your replies of which I have worked through. Going forward, when using CALCULATE I will always default to explicitly using FILTER( ). 

For anyone else interested in why to do this, have a look at the screenshot below. Shortly after posting here the course which I'm currently working through addressed my question too.

[AntrikshSharma]

@michellepace  Products[Product Name] = "Product 100" internally expand to the below version and for each row of the matrix the same condition is evaluated, as presence of ALL removes any filter coming to Product Name from the report and that's why you get the same value for each cell

=
CALCULATE ( [Total Sales], Products[Product Name] = "Product 100" )

--version above is equivalent to the below

=
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Products[Product Name] ),
        Products[Product Name] = "Product 100"
    )
)

 If you want to get the same result as with using FILTER then use KEEPFILTERS this way there will be a Set intersection operation, i.e. if Product 100 is currently in the filter context then only will it return something otherwise you will get blank

https://en.wikipedia.org/wiki/Intersection_(set_theory)

=
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Products[Product Name] = "Product 100" )
)

-- version above is similar to the below one:

=
CALCULATE (
    [Total Sales],
    KEEPFILTERS (
        FILTER (
            ALL ( Products[Product Name] ),
            Products[Product Name] = "Product 100"
        )
    )
)

The problem with the below one is that you are referring to the expanded products table, and in case of Snow flake schema you would see unexpected result, for star schema it should be fine as Products expands to itself (assuming)  

https://www.sqlbi.com/articles/expanded-tables-in-dax/

Another problem is that you are applying all the columns of the Products table to the filter context to evaluate Total Sales, while you can already do this with a single column, that way you have a more efficient formula

=
CALCULATE (
    [Total Sales],
    FILTER ( Products, Products[Product Name] = "Product 100" )
)

[harshnathani]

Hi @michellepace ,

Please see this video for the difference between FILTER and FILTER (ALL(....) )

https://www.youtube.com/watch?v=bJtRB86n9tk

The first 30 minutes is interesting to understand the differences,

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)