When to use KEEPFILTERS over iterators
HTML-код
- Опубликовано: 26 апр 2021
- How to use KEEPFILTERS in DAX iterator functions to preserve arbitrarily shaped filters in context transition.
Article and download: sql.bi/705054?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... 
Наука
"There are no dark forces in action" - LOL
Every time I encounter something strange now I know that I must check filter context and not blame the occult!
Thanks again Maestro for an excellent explanation!
True! for a long time I've avoided complicated situations like this. Now it is time be one with the force!
I think you are best teacher ever seen for DAX
Filter context is the mother fr*ucker of the many issues I've encountered over the years while developing with DAX. - context transition is hard to explain and hard to learn - I wished we had this video +3 years ago , It's excellently explained maestro!
may the daxforce be with you! :)
Any video that talks about 'dax shenanigans' gets a like from me ;)
Finally, I've seen use of keepfilters around tables in some of your article but never understood the reason behind it, thanks so much Alberto for explaining it, amazing video as always!
That's the most important thing for "DAX coders": dominate the filter context and context transition. You do it as a master!
Congrats Alberto and thanks for sharing knowledge!
I love to learn with SQLBI videos.
Once again, the master has spoken and imparted his knowledge on me. THANK YOU!! Great explanation. And I have gotten those wrong calculations and could not figure out why!
THIS IS MY MOST FAVORITE VIDEO YET! Thank you for taking the time to explain!!!!
you are so generous. great teacher!
Thanks Alberto for the clear explanation (also helps to start from the very beginning). I just rewatched this video after going through the example in the module from the video course. Marco also did a great job and watching this video right after really helps reinforce the knowledge. Keep up the good work!
- DAX novice in training
Thank you. I couldn't get a good explanation for KEEPFILTERS for a very long time.
I watch every video ...You are teaching the roots of DAX...thank u so much for all ur efforts and good work
Brilliant explanation - this is pure DAX gold. Thank you, sir!
Well explained!!!! Perfectly. It is also the best explanation what is the filter context transition. It should be be in a tittle as well! It is one of the best videos on SQLBI. I love the way you explained all filtering section with drawings.
So much depth in this topic and explanation. Thanks Alberto!
Phephenomenal video! I was always having a tough time understanding context transition.
What a detailed and clear explanation.
By the way, while I was doing some practice on KEEPFILTERS, I discovered that only if we use YEAR & MONTH hierarchy in the Filter Context, in both Matrix and Slicer, the result will be correct without the need to use KEEPFILTERS. But I don't know why. I hope you explain this scenario in some of details.
THANK YOU for the grate videos
Thanks Alberto! This video looks tailor-made over an issue I had right this morning!
mind blown, the explanation is so smooth thank you alberto!
Great Video! Please let more such videos come in. I firmly believe that we can solve complex problems if we are clear qith basics and theory! Thanks!
That's an amazing explanation! Thanks, Alberto!
This is GOLD! Thank you soooo much from Melbourne down under. ✍
Thanks DAX Guru, can not say more thanks to you and your team :)
Excellent. The best explanation on the planet.
Very helpful! Thank you Alberto and team!
Excellent article this - a light went off in my head !
Very clear and intuitive explanation, Sir! Subscribed
Great video, I absolutely love those theory videos! Thanks!
Thanks Alberto. You're a genius. Very well explained!
Wow.. You and Marco are the god of DAX... :)
Que manera de explicar tan clara¡¡¡ Grazie mille maestro¡¡¡¡
I really liked this type of video. This is one of best of chanel. There some things em DAX tha we need to explain in a deep details and this diagrams step-by-step are a great way to understand. Thanks a lot!
Eres genial Alberto, todos tus videos son grandiosos!!! Saludos dese Perú 🇵🇪
Very useful. Thank you as always 👍
Superb and very useful 👍 Thank you.
this is amazing!
more "boring" theory :) thank you for your knowledge!
Absolute masters!
I loved it!
just amazing =)
Excelente explicação!
Thanks!!!! You save my life.... Again LOL
Thank you very much
"DAX shenanigans" :) at 2:58
Awesome!
Great..👍👍👍
Masterful
Awesome Awesome Awesome 👍👏
SuperVideo. Thanks
Thank you🙏🙏🙏🙏
Sensacional!!!
Great!!
thank you
oh this is fab! dax enjoyed! ciao
Great!
I'm sure that others MVP'S owners of RUclips channels benefits from this channels like us but i don't come across with their comments here, when they ask for their channels , this is not fair.
Thanks Marco and Alberto
So if a [Date Month] Column was used {2007-11-01,2007-12-01,2008-01-01,2008-02-01} instead of a Textual Month (so unique values per month) in the "Values" of the function, it would have iterated discrete values and solved the issue without complexifying the code. But I get the point of the exercice for demonstration purpose :) Well done as usual !
Correct!
This is one solution. The same effect is there on using a Year-Month hierarchy.
Thank you for another great video.
I assume the second filter context which is generated on transition is only month due to the VALUES (Month) in the averageX.
If the AVERAGEX iterated on VALUES ( Year-month) instead of just Month, would that avoid the issue?
Correct!
Thank for the information, amazing as usual , however it's seem to me the link is not working for article .
The link works - can you try again? What error do you get?
Hi Alberto, not sure I got you there. In the example you said that “Novemeber” was used together with the combination of the filters applied on year. Thus what we see is nov 2007 and nov 2008 combined together. But the filter passed to averagex in the first iteration was year = 2007 and month = nov. How did calculate see both the years then? I guess, and correct me if I’m wrong - the filter on year was simply wiped off and only the month was retained since the calculate is built in top if that. I also think your sample DB contains just these 2 yrs, because of which the value corresponds to the aggregation of these 2 years. Cheers.
Alberto - have you ever covered how you use a tablet and stylus to annotate your visuals in one of your videos? This would be really useful to use as a whiteboard in meetings.
See this: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/
How about Sep. New update for Calculate, please teach us more on this.
Again great Alberto. But should this behaviour of keepfilters not be automatically in a hiërarchiek slicer??
At the total row?
Also, I have a little problem with variables!
When i use the external measures, very basic, it works
But when i uses this measures in a variable , it gives the wrong result.
I tried everything, also, extra calculate around the variables, but still, no go. No one can solve this basic problem. Will you give it a trie in a video?!
Last question, do you ride a maserati? 😀
Great video as always :) I have a question: let's say we have Sales Amount measure calculated as SUMX (multiplying net price and quantity). Is it better to have it as this measure in model or precompute in ETL and load to Tabular as ready column? So trade off would be bigger model vs need to calculate it on the fly.
It depends. In general, it's better to save storage and use the two columns, which result in a better compression compared to a single column with the result. In a very large model (billions of rows), precomputing the result of the multiplication could be better for both performance and storage. The balance point depend on the number of unique values and it's hard to estimate, it should be analyzed model by model. However, for tables that have only a few million rows usually it is better to perform the multiplication at query time and save storage.
@@SQLBI thank you very much
You are the best! Help me a lot
But why in the mouth its work perfect?
Thank you Alberto.
Q: Is there an easy way to see the filter context in action? Like a debug for External/Internal context and Context Transition? Or something like an Execution Plan in SQL Server?
Read this article+video: www.sqlbi.com/articles/displaying-filter-context-in-power-bi-tooltips/
I ran into a similar problem sometime back, and started blaming SUMX function as buggy.
Great help, Alberto! Thank you! Is there a reason that I wouldn't use this on every Calculate formula?
It depends on the purpose of the filter. As a rule of thumb, you often want to use KEEPFILTERS for a range of values (e.g. >=) and you don't want to use KEEPFILTERS for a single value (e.g. =) but there are certainly exceptions to this "rule".
Thank you for this clarity! Great help!!
Love it!!! There is not dark forces in action!!!
Thanks for the insight on KEEPFILTERS. one interesting aspect noticed is if a year month hierarchy is used then the problem is not appearing. Someone can confirm this.
Because you have a single column filter, in that case you don't have an arbitrary shaped filter that would be partially broken by the filter overwrite.
@@SQLBI Thanks for the clarification.
How about it
calculate (
divide(sales[sales amount],
countrows(values(date[Month])
Could be it analog to your method ?
Hi Alberto,
First of all, Thank you for all your videos, It's a great pleasure for me to learn DAX with SQLBI.
I have a question about your explanation of the Context Transition in the Timestamp 10:55 of the video : I don't understand why context transition operate only on the Month column. Intuitively (And maybe there is my error), When I read the report in the example , the New filter resulting of the context transition is 'Date'[Month] = November AND 'Date'[Year] = 2017. Of course is false.
Could you help me to understand my error?
Again thank You for your work,
Juilen JACQUEMONT
The year filter is there because of the original filter context. The iterator transforms only the columns in the row context into an equivalent filter context - the Month column, indeed.
Hi Alberto,
What if you replaced month with yearmonth. Would that solve the problem on a total level? you have a unique value for each year month combination.
Yes filtering a single column is always better!
PLOT TWIST
"The problem is not DAX...Is you."
I still don't understand one thing . If we used KEEPFILTERS why does it work properly? as we only have month filter (ie.November) coming from context transition for iteration #1 so it only should KEEPFILTERS (takes mutual part) for month column, from outer filter context not a year column (from outer filter context) ?
Isn't it simpler to do :
Monthly Average =
AVERAGEX (
SUMMARIZE('Date', 'Date'[Month], 'Date'[Calendar Year] ),
[Sales Amount]
) ?
Values (year month) instead of keepfilter?
Correct! A proper data model always saves you - but if you don't have the YearMonth column, KEEPFILTERS becomes necessary.
The lesson always seems to be, if you're code is wrong stop and go study. 😁
Норм!!! 📌
Extra!
How to explain something simpel more difficult.
I am back after a year and still don’t understand one thing where this logic doesn’t work on subtotal if we iterate with years. I read sqlbi and a dax bible but it doesn’t cover this. What is the filter context for ie subtotal 2008? I guess it is 2008 - Jan and 2008 - Feb. So iteration over values (years) according to this logic should iterate over 2008 only but it doesn’t work and gives wrong result. Somehing is worng here at subtotal level. The filter context from a table doeas not work as expected
It's better if you comment below the related article, for complex answers we do not use RUclips comments. It's also better if you clarify the exact formula and point in the article, that's also the reason why we prefer to comment there. Thanks!