When to use KEEPFILTERS over iterators 

"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!

I think you are best teacher ever seen for DAX

Any video that talks about 'dax shenanigans' gets a like from me ;)

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! :)

THIS IS MY MOST FAVORITE VIDEO YET! Thank you for taking the time to explain!!!!

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.

Brilliant explanation - this is pure DAX gold. Thank you, sir!

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!

you are so generous. great teacher!

I watch every video ...You are teaching the roots of DAX...thank u so much for all ur efforts and good work

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

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.

Thanks DAX Guru, can not say more thanks to you and your team :)

So much depth in this topic and explanation. Thanks Alberto!

Thank you. I couldn't get a good explanation for KEEPFILTERS for a very long time.

Phephenomenal video! I was always having a tough time understanding context transition.

Very helpful! Thank you Alberto and team!

That's an amazing explanation! Thanks, Alberto!

mind blown, the explanation is so smooth thank you alberto!

Thanks Alberto! This video looks tailor-made over an issue I had right this morning!

Excellent article this - a light went off in my head !

Excellent. The best explanation on the planet.

This is GOLD! Thank you soooo much from Melbourne down under. ✍

Thanks Alberto. You're a genius. Very well explained!

Great video, I absolutely love those theory videos! Thanks!

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!

Wow.. You and Marco are the god of DAX... :)

Very clear and intuitive explanation, Sir! Subscribed

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

Que manera de explicar tan clara¡¡¡ Grazie mille maestro¡¡¡¡

Eres genial Alberto, todos tus videos son grandiosos!!! Saludos dese Perú 🇵🇪

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!

Very useful. Thank you as always 👍

this is amazing! more "boring" theory :) thank you for your knowledge!

Absolute masters!

Superb and very useful 👍 Thank you.

Thanks!!!! You save my life.... Again LOL

I loved it!

just amazing =)

Awesome Awesome Awesome 👍👏

Thank you very much

Excelente explicação!

SuperVideo. Thanks

Awesome!

Sensacional!!!

Thank you🙏🙏🙏🙏

Great..👍👍👍

"DAX shenanigans" :) at 2:58

Great!!

Masterful

oh this is fab! dax enjoyed! ciao

I'm sure that others MVP'S owners of RU-vid 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

Great!

thank you

I ran into a similar problem sometime back, and started blaming SUMX function as buggy.

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?

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? 😀

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.

How about Sep. New update for Calculate, please teach us more on this.

Thank for the information, amazing as usual , however it's seem to me the link is not working for article .

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 !

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?

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.

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.

You are the best! Help me a lot But why in the mouth its work perfect?

How about it calculate ( divide(sales[sales amount], countrows(values(date[Month]) Could be it analog to your method ?

Great help, Alberto! Thank you! Is there a reason that I wouldn't use this on every Calculate formula?

The lesson always seems to be, if you're code is wrong stop and go study. 😁

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) ?

Love it!!! There is not dark forces in action!!!

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.

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.

PLOT TWIST "The problem is not DAX...Is you."

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

Values (year month) instead of keepfilter?

Isn't it simpler to do : Monthly Average = AVERAGEX ( SUMMARIZE('Date', 'Date'[Month], 'Date'[Calendar Year] ), [Sales Amount] ) ?

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