cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MP_123
Microsoft
Microsoft

Filter with calculated measures

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!

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

@MP_123


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.

= 
VAR m = 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?Smiley Happy

 

Regards

View solution in original post

9 REPLIES 9
v-ljerr-msft
Microsoft
Microsoft

@MP_123


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.

= 
VAR m = 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?Smiley Happy

 

Regards

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.

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!

Can somone tell me why this works

= 
VAR m = 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....

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?

@v-ljerr-msftthanks! your awsome!

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

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.

Michiel
Resolver III
Resolver III

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors