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

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

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Filter with calculated measures

@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

6 REPLIES 6
Michiel Regular Visitor
Regular Visitor

Re: Filter with calculated measures

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
Super Contributor

Re: Filter with calculated measures

@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

MP_123 Member
Member

Re: Filter with calculated measures

@v-ljerr-msftthanks! your awsome!

amalhotr Frequent Visitor
Frequent Visitor

Re: Filter with calculated measures

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

Michiel Regular Visitor
Regular Visitor

Re: Filter with calculated measures

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
Frequent Visitor

Re: Filter with calculated measures

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?