Forum Discussion
Losing context transition by using filter
pankajk83
As I mentioned in my previous reply, there is absolutely no difference, in this case, between using FILTER - ALL or FILTER - VALUES. In fact VALUES, DISTINCT, ALLSECTED will all perform as ALL because the is no outer filter context.
What you are missing here is that the filter argument of CALCULATE replaces the filter context created by CALCULATE. When placed in a calculated column, CALCULATE converts all the columns of the current row of the table into a newly created filter context. When you use FILTER - Table you actually replace that filter context with a new one. Except this time the new filter context includes all the rows, not only the current row. That is the very same effect of FILTER - ALL ( Table ).
When you use FILTER - ALL ( Table[Column] ) you only replace that column in the filter context created by CALCULATE but the rest of that filter remains.
My recommendation, avoid using CALCULATE in creating calculated columns but rather try to rely on a NoCALCULATE approach.
pankajk83
Before reading my answer, I would suggest you to go through these two blogs
https://www.sqlbi.com/articles/expanded-tables-in-dax/
https://xxlbi.com/blog/power-bi-antipatterns-9/
https://xxlbi.com/blog/power-bi-antipatterns-10/
I would like to answer your question in two parts, first lets see the difference between, filter function in calculate and predicate filter in calculate.
-- filter predicate in calculate
single calculate=calculate(sum(Sales[pk_total sales amount]),Sales[Quantity]>1)
when you are filtering on a column within calculate as filter predicate that means you are filtering on the expanded table (which includes columns from sales and product).
The equivalent of above DAX expression is:
single calculate=calculate(sum(Sales[pk_total sales amount]), Filter(all(Sales),Sales[Quantity]>1) )
However, with the below syntax you are filtering on sales table only.
single calculate=calculate(sum(Sales[pk_total sales amount]), Filter(Sales,Sales[Quantity]>1) )
Now coming to the next part, first of all
One of the rule for calculate function is, calculate fitler arguments are evaluated in original row and filter context (i.e row context in your scenario) and the result of these arguments then effect the filters created due to context transition.
with filter predicate (Sales[Quantity] > 1) or (Filter(all(sales), sales[quantity] >1):
- The resulting filter context includes columns from both the Sales and Product tables, and filters produced due to context transition from the Product table will affect the result.
- The CALCULATE function evaluates its expression within this modified filter context, taking into account any filters applied by the FILTER and ALL functions.
with double calculate with filter function:
- Here, a FILTER function is nested inside a CALCULATE function to filter the Sales table based on the quantity column.
- The inner CALCULATE evaluates its expression within the filter context created by the FILTER function, which filters the Sales table.
- The outer CALCULATE evaluates the result of the inner CALCULATE within its own filter context, potentially affecting the result through context transition.
with Filter(Sales, Sales[Quantity] > 1)
- Here also the filter argument is evaluated in original filter context but the result will not contain the columns from product table (not an expanded version of sales)
- So, though the filters due context transition are not observable)
Note: I am not an expert in the core concepts of DAX. I wrote the above answer based on my understanding, so take it with a grain of salt.
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun