I was hoping someone could help me with this rather basic, but somehow troublesome DAX questions on Calculate.
It relates to the filter argument, and specifically the difference between writing Filter() and not writing filter. In the first screen shot, you will see Green sales is replicated across all sales when using the inbuilt calculate filter. However, when you write the Filter() argument, green sales is only populated for Green (second screen shot. I was wondering if someone could explain why this is so?
Please always try to post your reference working or demo PBIX file so that members of the Forum can provide assistance in a better and efficient manner. For poster of the query, it may be a general question but the members who are providing the assistance, for them it’s not. They might have to build a file from scratch in order to create the environment which unnecessarily consumes more of their time who are trying to provide assistance.
Now, here’s answer to your question in a two-fold steps (Note:- In my PBIX file, I don’t have a field for “Color” so I’ve used a “Brand Name” to explain the scenario) -
1). Without FILTER -
When you write a expression like this i.e., Without explicitly using the FILTER function -
It means that, filter context over “Contoso” over-writes all the other filters which are there inside the visual as well as outside the visual. Because the expression states that, out of all the “Brand Names” evaluate the results for “Contoso” and then “ALL()” function over-writes all the other filters thereby, repeating the same results for “ALL” the other brands as well. So, even if you make a slicer selection over any other “Brand Name”, it’ll still showcase the results for “Brand Name = Contoso”. Below is the screenshot provided for the reference -
At the back-end of the engine, Power BI scans the entire “Product” table and and keeps the rows only where “Brand Name = Contoso” and rest of the rows are eliminated. So it means that, if “Brand Name = Contoso” then evaluate the results or else it’ll showcase it as blanks.
It’s not a recommended best practice as per DAX to scan the entire “Product” table. Because when an entire table is scanned, it puts more pressure on the engine and slows down the evaluation. In order to achieve that same result, the expression can be written like this -
In this case, we are stating that, only retain the filter over “Brand Name = Contoso” and don’t over-write the other filter contexts, thereby respecting the other filter contexts as well for each Brand Names. But by writing an expression in the form of “KEEPFILTERS()”, we optimize the measure as well whereas in the case of original expression which you’ve written, it has to scan the entire “Products” table and then evaluate the results.
I’m also attaching the working of the PBIX file alongwith the link of a post for your reference purposes where almost similar query was addressed few days back onto the Forum.
EDIT:- Below are additional links of the articles and blog posts provided from SQLBI website with regards to your query about how filter context works.
Hi @AussieDragon,
As the colleague @Harsh has well commented, in the first way of writing the measure, the ALL function is being used internally, so the existing filter context in each row on the COLOR field is overwritten by COLOR=Green.
In the second form of the measure using the FILTER function, the filter context is kept and intersected with the new filter:
For the first row: COLOR=Azure AND COLOR=Green and for the row with color Green, COLOR=Green AND Color=Green is applied, so it is the only row where the measure gets a value.
The following link by Miguel Caballero helped me a lot to understand the filter context displayed as a set of tables and how it is modified when using the CALCULATE function.
Thank you all for excellent explanations - in fact, they are much easier to follow than the ones on SQLBI. No doubt they know their stuff, but they seem to complicate the matter and you end up more confused with their explanations.
One little thing, so, Filter is the same as Keepfilter, If as you say, the filter context is kept? I am thinking that they are, except KEEPFILTERS is best practice and better for performance. Would my understanding be correct?
Also, would it be correct to say that there are only two definitions of filter modifiers
Filter Removal - indicative of using ALL
Filter Adding - indicative of using KEEPFILTERS
I do sometimes hear videos say replacing the filter context, but that to me would be a combination of either 1 or 2
So here’s the additional explanation based on the above queries -
1). No “FILTER()” is not the same as “KEEPFILTERS()”. I’m providing a link below which will answer all your questions about why “KEEPFILTERS()” is more efficient but most importantly, how it’s diffrent from “FILTER()”. In this case, the results were same but that may or may not be the case when we have a chain of relationships between the tables which we call it as “Expanded Tables” (This is another separate topic for discussion, you can refer videos on this over SQLBI website or create a separate thread) and when we use entire table inside the “FILTER()”, it has to iterate/scan over that table or set of tables which are linked via relationships which is not efficient.
2). There’re many DAX functions which works as filter/context modifiers especially, the ones which contains the word “ALL”. Yes, they’re tables functions but when they’re combined with “CALCULATE()”, they also work as a “Context Modifier”. And that was one of the reason why I provided the link above on the topic - “Managing “all” functions in DAX”. Apart from that, “USERELATIONSHIP()”, “TREATAS()”, “CROSSFILTER()”, etc are some of the special functions which also works as a context modifier.
I do sometimes hear videos say replacing the filter context, but that to me would be a combination of either 1 or 2.
It depends upon the scenario that you’re facing or trying to tackle. Sometimes, it may be either-or and sometimes, it can be a combination of 1&2. This functions can only be more understood in-depth by more practicing it since they’re not easily understandable by watching a video once. And when used in a wrong context then might lead to wrong or sometimes even inflated results.
Sorry to ask, but something doesn’t seem to make sense with the following scenario. I have modified the With and Without FILTER function.pbix and if All overwrites, why wouldn’t the additional visual not repeat the value for A Datum Corporation all the way down?
In the table visual, you’re bringing a context in the form of “Manufacturer” and not “Brand Name”. It’ll yield same results throughout when “Brand Name” is brought into the visual. See this -
The reason being is, inside a measure we’ve stated that, “Brand Name = A. Datum” should over-write the other Brand Names which are available in the filter context inside a table visual. And therefore, it starts returning the same numbers against the other brand names as well.
But when you replace a “Brand Name” field with any other field, in this case, “Manufacturer”. At the back-end it still does the same but now the filter context inside a visual is “Manufacturer”. So first, it’ll start evaluating the results where “Brand Name = A. Datum” and then it’ll start searching for “Manufacturer = A. Datum”.
It means that, right now, this logic is in the form of “Intersection”. That is, when there’s a Brand Name inside a table visual then it should over-write them but when it’s replaced with Manufacturer field, it should “ONLY” provide the results where sales for that particular brand name has been made by other manufacturers as well. So since in this case, “Brand Name = A. Datum” is only manufactured by “Manufacturer = A. Datum Corporation” and not by other manufacturers, it showcases the results only against that particular manufacturer due to “Intersection” logic. See this -
But since you wanted to or were expecting to see the recurring results against the Manufacturer where results of “Manufacturer = A.Datum” over-writes over the other manufacturers. Your measure should’ve been like this -
Or since respective brands are being sold by their respective manufacturers (in this case/dataset). The original measure could have been alternatively written as -
But mind you, as I said before writing that last measure, “since respective brands are being sold by their respective manufacturers (in this case/dataset) - Results are same”. But if the same manufacturer is dealing in mutiple brands then “Total Sales” figures for that manufacturer will not be equal with the evaluated sales figures i.e., “Without Filter” section. Because in that case, “Brand Name = A. Datum” for that particular manufacturer will be a small part from the set of brands in which they’re dealing in and not the entire set itself. In this case, since they’re dealing in only one/respective brand, it’s considered as an entire set in itself and therefore, evaluating same results as Total Sales for that manufacturer, otherwise figures will be smaller than that of Total Sales.
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this helpful.
Important Note:- For future reference, please create a new/separate thread once the thread is re-solved based on the original scenario created/mentioned in the initial post. Since only one question/scenario can be posted per thread. Please go through the Forum guidelines for reference purposes.
I have to say your answer has finally made it click for me. I’ve done the SQLBI course on DAX but didnt seem to assimilate what they were saying fully. Largely because of this example which I now grasp.
Thank you so very much for explaining in such a clear way!!
I’m glad that I was able to assist you and you found the explanation helpful. It’s great to see that you’re learning and practicing every small detail of the concept.
