Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
selimovd
Super User
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
 

View solution in original post

1 REPLY 1
selimovd
Super User
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
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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