- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Filter with calculated measures

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Filter with calculated measures

09-26-2016
12:33 AM

hi,

i know that calculated measures is changing regard it's context.

i'm using a 'Text' type calculated measure to filter another calculated measure.

since it's value is not stable, filter with calculated measure is not gining me the right result

for exmample; **Measures**='ab'

calculate (sum(x),filter(y,column='ab') is not the same as calculate (sum(x),filter(y,column=Measures)

do you know why?

how can i filter with dynamically filter?

thanks!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-27-2016
03:21 AM

how can i filter with dynamically filter?

Have you tried the VAR function that I mentioned in your previous thread? In this scenario, the formula should like below.

=

VARm= Measures

RETURN

CALCULATE ( SUM ( 'x' ), FILTER ( 'y', column =m) )

If the result is still not right, could you post your table structure and the measures you use, and some sample data in your case?

Regards

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-13-2023
03:31 AM

Hi,

maybe somebody will help you this little thing that helped me out.

This dont work for me, until i didnot change the Measure

=

VARm= Measures

RETURN

CALCULATE ( SUM ( 'x' ), FILTER ( 'y', column =m)

My Measure was using = Values() to take actual filtered option from slicer.

But its single take, so when i change it, and use MAX(), which always return only one string, everything start works.

So take a look on your VAR m = Measures

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-07-2022
04:34 AM

I've just had this same problem and worked out the WHY, rather thank just the 'how to fix'.

When you use a measure in DAX, the DAX will wrap the measure in a CALCULATE. CALCULATE transforms the current row context into a filter context.

The FILTER function introduces a row context. This is important. The FILTER function iterates through the table used as the first parameter row by row and tests the expression used as the 2nd argument. If you use a measure in the expression, the measure will be wrapped in a CALCULATE and the current row context introduced by FILTER will be turned into a filter context and passed to the measure. This means that the measure will always evaluate in a filter context defined by the current row context of the FILTER function.

The reason why setting the measure to a variable bypasses this is that the value associated with the measure outside of any row context is saved to the variable and so it will stay constant and be uninfluenced by row context (if using the FILTER function). It also often allows you to bypass the need for the FILTER function in a CALCULATE to begin with as the expression in a CALCULATE has some drawbacks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-08-2023
08:07 AM

This is actually the best answer for understanding this question. Thank you @tbennett93 . I actually struggled on understanding why assign the same measure to a VAR won't work as the same as directly use the measure in a formula. The reason is that Filter() formula ONLY creates Row Context not the Filter Context (sorry for the confusion, Row Context and Filter Context are the 2 key concepts in DAX and please refer to other articles on this topic). The Filter Context is fulfilled by Calculate() formula. So when use Filter() and Calculate() together, you can have both Row Context and Filter Context work for your math. This is very critical. First, Filter() establish a Row Context, then the Calculate() does the Context Transition to turn the current Row Context to a Filter Context in the Many side of the relationship's table to evaluate the expression you put into the Calculate(). So when work on Products have Total Sales Less than 10000 problem, you can use the [Total Sales] measure in the formula like below:

[Products Sales < 10K use measure] = Calculate(SUM(Sales[SalesAmount]), [Total Sales]<10000)

But NOT like this:

[Products Sales < 10K use VAR] =

VAR varSalesSum = [Total Sales] -- ONLY put a constant in VAR and stops Context Transition in later formula

RETURN

Calculate(SUM(Sales[SalesAmount]), varSalesSum <10000)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-27-2016
03:21 AM

how can i filter with dynamically filter?

Have you tried the VAR function that I mentioned in your previous thread? In this scenario, the formula should like below.

=

VARm= Measures

RETURN

CALCULATE ( SUM ( 'x' ), FILTER ( 'y', column =m) )

If the result is still not right, could you post your table structure and the measures you use, and some sample data in your case?

Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-03-2022
03:28 AM

Hi @v-ljerr-msft , I was also stuck with this problem for more than 2 days till I came across this answer. Can you please briefly tell why it works. I am totally clueless.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-20-2021
06:42 AM

Hi,

I have a table with 3 Columns

Report Name | Manager | Status

Status contains Values: Pass, Fail, NA

I want to calculate % Pass for each manager. NA should be calculated in %of calculation.

I created one measure to calculate Total of Pass and Fail. and tried creating another measure for only "Pass". But its same % value for each manager.

I am showing this data in Matrix. Please guide. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-10-2020
06:45 AM

Can somone tell me why this works

=

VARm= Measures

RETURN

CALCULATE ( SUM ( 'x' ), FILTER ( 'y', column =m) )

and this does not:

CALCULATE ( SUM ( table1[x] ), FILTER ( table1, table1[y] = measure ) )

I had a sample excel where it worked fine in but i had to implement your VAR method for my SSAS and powerpivot model....

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-20-2018
03:18 PM

Hi

I tried the same thing with a filtren that you can choise in the report (a visual filter)

Viajes de Cir - Dest 2 = IF([Base Ajustada Origen]= "HID",CALCULATE(COUNTROWS(Circuitos),'Circuitos'[MAPA DESTINO]="HID"),4)

Measure = Base Ajustada Origen, the value that it generate is HID

But it not recognize the mearsure like a text. The messeage said that i can´t used a mesure with a dinamic visual.

could you help me?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-10-2017
09:25 PM

VAR m = [Measure]

RETURN

CALCULATETABLE('Table 2','Table 2'[QUARTER]=m) Not working with calculatedtable DAX function

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-11-2017
08:39 AM

What is the error that you get? You cannot use this directly in a measure, but that is because a measure must return a scalar value, not a table (which is wat CALCULATEDTABLE returns). So at least it should be wrapped in a table aggregation function.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-26-2016
01:19 AM

With the simple definition of your Measures measure, the two formulas actually do give the same result.

With CALCULATE, you can use simple filters directly, like

CALCULATE(SUM(y[x]), y[column]="ab")

which is equivalent to

CALCULATE(SUM(y[x]), FILTER(ALL(y[column]), y[column]="ab"))

The difference between FILTER(y, y[column]="ab") and FILTER(y, y[column]=[Measures]) comes down to context. In the former, "ab" is evaluated in a row context created by iterating over the table y. In the latter, referencing a measure implicitly introduces a CALCULATE. One of the things CALCULATE does is to create a filter context. In other words, the row context within FILTER is replaced by a filter context. Many DAX functions have different behaviour in row context and filter context, like SUM: in row context, SUM sums all rows in the table, not only the current row; but in filter context, SUM sums only the rows in the filter context (and within FILTER this is only one row).

Creating a dynamic filter is done through having a measure that gives a result based on the context established through the current row within FILTER.

Announcements

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

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

107 | |

98 | |

78 | |

65 | |

53 |

Top Kudoed Authors

User | Count |
---|---|

139 | |

99 | |

94 | |

85 | |

63 |