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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper IV
Helper IV

Filtering table by measures



I'm able to create a new table filtering like this:


NewTable = FILTER(Table; Table[Area]<= 500 && Table[Area] >= 10

I'm not able but would it be possible to use measures instead of static values, like here?


NewTable = FILTER(Table; Table[Area]<= measure1 && Table[Area] >= measure2






If your measure 1 and measure 2 can be sliced on row level, your formula should work. See my sample below:




Then you can create a calculated table with similar expression.







Hi @laciodrom_80


Your idea of using interim measures is good !

But using a measure inside a filter function activates context transition because a Calculate is automatically wrapped into a measure.

In your particular case, in the filter function, you are iterating over each row of your 'Table' (row context). In option 2, because you are using a measure as part of the filter condition, this row context is transformed into an equivalent filter context (context transition).  So be careful of what you really want to happen.


Here are my suggestions:

Always use interim measures in the 1st argument of Calculate ( [expression] argument) - it makes the syntax clearer and never has any impact on performance/results.

Only use measures inside Filter arguments when you really want context transition to happen.


Here, keep your first option with a fixed value as a filter condition (or an expression which doesn't trigger context transition... so basically no calculate but simple aggregators such as Min, Max...) if you don't want context transition to happen.


Note that If you are using Excel 2016 or Power BI Desktop, you can leverage DAX variables:


There are many pros using DAX variables. It is not just about measure readibility but also performance.

Variables are evaluated first, "once and forever", in their current filter context. Their value is fixed so there is no need to use Filter function anymore !

The pros I see using variables (DAX 2015 so only in Excel 2016 or Power BI Desktop) :
(i) The formula is smaller, easier to read and to maintain. Users can understand better the role of each component.
(ii) It prevents you from the classic error to forget your "All" function in the Table argument of your filter function.

(iii) The performance can be improved

(iv) It is also very convenient when you want to filter a column based on the value of a measure but you don't want context transition to apply


Hope I've been clear Smiley Happy and it helps you !


Not applicable

Hey there!


This was a fantastic answer which helped me a lot! To add to this, if you would like to use the same interim measure but reference it in multiple places you can simply reference the measure within a var and then call the var into the filter context.

For instance:
Measure = Measure logic

Then within the equation you want to reference this
MeasureTitle = Measure
Calculate(Sum(Table[Column]), Filter(Table, Table[Column] = MeasureTitle))

This allows you to write the measure logic once, but still reference it into multiple different equations without having to update the vode in multiple places.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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