Forum Discussion
Filter with calculated measures
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?:smileyhappy:
Regards
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.