Forum Discussion

laciodrom_80's avatar
laciodrom_80
Helper IV
9 years ago

Filtering table by measures

Hi,   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 stati...
Tutorial Requests
Datatouille's avatar
Datatouille
Solution Sage
9 years ago

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 !

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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