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
laciodrom_80
Helper IV
Helper IV

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 static values, like here?

 

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

Thanks!

 

Luca
3 REPLIES 3
v-sihou-msft
Employee
Employee

@laciodrom_80

 

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

 

12.PNG

 

Then you can create a calculated table with similar expression.

 

55.PNG

 

Regards,

 

 

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

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.