cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted

Re: Filtering table by measures

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 !

 

Highlighted
Microsoft
Microsoft

Re: Filtering table by measures

@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,

 

 

Highlighted
Resolver II
Resolver II

Re: Filtering table by measures

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
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors