cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## how to filter to between 2 values in a measure

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).

1 ACCEPTED SOLUTION
Super User

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors