- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

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

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- 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

MP_123

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Accepted Solutions

v-ljerr-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- 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

6 REPLIES 6

Michiel

Regular Visitor

Re: Filter with calculated measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- 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.

v-ljerr-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- 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

MP_123

Member

Re: Filter with calculated measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-27-2016
03:44 AM

@v-ljerr-msftthanks! your awsome!

amalhotr

Frequent Visitor

Re: Filter with calculated measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- 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

Michiel

Regular Visitor

Re: Filter with calculated measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- 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.

DesireeMaya22

Frequent Visitor

Re: Filter with calculated measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- 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?