how to filter to between 2 values in a measure

[Anonymous]

I have the following measure, in which my goal is to calculate [Total Sales] with the filter 'Dim Table'[Col] between 2 values:

Total Sales Filtered =
CALCULATE(
    [Total Sales]
    ,'Dim Table'[Col] >= 1
    ,'Dim Table'[Col] <= 5
)

As far as I know, this is equivalent to:

Total Sales Filtered =
CALCULATE(
    [Total Sales]
    ,FILTER(
        ALL('Dim Table'[Col]
        ,'Dim Table'[Col] >= 1
        && 'Dim Table'[Col] <= 5
    )
)

Are these two implementations of the measure actually equivalent?  If so, is there a preference for one over the other?  I ask because, for the situation of filtering between 2 values, I have only ever seen the second one, not the first one, even though the first one seems like it would be more common (since it's syntactical sugar).

[selimovd · Accepted solution]

Hey @Anonymous ,

from the way the engine works they should be equivalent. The different filter arguments are combined with a logical "and".

To be honest the first way is very uncommon to write it and if you're not deep into the DAX language it's difficult to understand. So for maintainability I would never write it like the first version.

Since March 2021 you can also combine the conditions. So my preferred way would be:

Total Sales Filtered =
CALCULATE(
    [Total Sales],
    'Dim Table'[Col] >= 1 && 'Dim Table'[Col] <= 5
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic