MSPTDA 19: CALCULATE DAX Function & Filter Context & ALLSELECTED & KEEPFILTERS (50 Examples)

[@mohitpaul1992]

thank you Sir. I have been studying DAX and Power BI from paid sources but in the end it was free classes of yours on KZbin that helped me understand the core of these DAX concepts. I don't know how I can thank you but truly appreciate it :)

[@excelisfun]

You are welcome!! I am happy to provide all this for free : )

[@aseemgoyal101]

Amazing learning, I have subscribed to Marco Russo & Alberto Ferrari's paid course, but this is way simpler, clearer & better. Thank you

[@excelisfun]

You are welcome, Bulls and Bears!!!

[@zahoorsarbandi2982]

I have no words: how to appreciate and praise these videos...I searched and found No words in dictionary which can give a tribute to your efforts and expertise. You are a man of Par Excellence..

[@excelisfun]

I am so glad that the videos that I post can help : )

[@jeffnixon4868]

My hazy understanding of filter context is now crystal clear thanks to your brilliant explanations!! Your knowledge and teaching style are only exceeded by your generosity in time and effort. Imagine the value you must be creating with a video such as this, as we employ the knowledge gained, and pass it on yet again to others. Thank you so much!

[@excelisfun]

You are welcome so much, Jeff Nixon!!! And, please help support the free knowledge by passing the videos on to whoever else needs to know too. And thanks for your support with your comment, Thumbs Up and Sub : )

[@a.achirou6547]

Waou ! This is amazing. Thank you for making freely available such an extraordinary content to the World. This is a thorough content.

[@excelisfun]

You are welcome!! : ) This is what I have been for the last 15 years at KZbin.

[@celia5132]

I come back once in a while to refresh my memories. If there is a way to gives you a million likes, I would. Thank you, Mike!!!

[@excelisfun]

Thanks for the million likes : ) : ) : )

[@__HumanBeing]

I'm not saying you are better than Russo and Ferrari. But I can say that you explain things way better than them! Thank you for your videos, I increment my job skills just by watching your videos, thank you very much!

[@excelisfun]

You said it perfect: I am not smarter than they are with DAX, but I can tell stories to help make complicated topics less complicated at a high level : ) You are welcome for the video, Diego!

[@danjarupath]

Bravo !!!!! Thank you 💕💕💕💕 😊

[@excelisfun]

@@danjarupath You are welcome rad racer!!!!

[@szpyrkowski]

I came here to write this comment. Totally agree with you, Diego.

[@excelisfun]

@@szpyrkowski Thanks : )

[@RidharvJoshi]

Right now I have never seen video like this..its more than amazing❣️❣️❣️

[@excelisfun]

I am glad that the video can help you, #ROCK!!!

[@shoeshines2121]

This is an amazingly thorough movie! The explanation of FILTER, ALLSELECTED and KEEPFILTERS is the clearest out there! I am just floored at the amount of time and effort put in to every tutorial.

[@SV1207]

I went through the last 19 vids in a week. This has without a single doubt been *the* best resource on powerquery/powerpivot/M-code/DAX I've come across. I'm usually a sucker for books, but I can honestly say this series is much better than any book I've ever read. Especially with the reference materials *and* homework. Thanks!

[@excelisfun]

You are welcome! Thank you for your kind words!!! I try to post helpful and fun materials so we can all learn to have fun with excel and Power BI!!!! Tell everyone you know that the PQ and DAX stuff is here at excelisfun. And thanks for your support, Sebastian!!!!

[@Budzilnik]

It is like watching Avengers Infinity War. You are on the edge of your seat, all favorite heroes are on the screen, some fantastic action is happening, and the longer you watch, the less you want it to end. This was amazing. Thank you for this movie.

[@excelisfun]

I LOVE your simile / metaphor / analogy!!! It is a fast pace movie with so many important tips flowing out one after the other, sort of like a good super hero movie. Thanks for watching and supporting, Leonards : )

[@RobMichaels1]

I really like how you use small fact and dimension tables to illustrate how the filtering works. Excellent description of the FILTER function in action. Thanks so much!

[@excelisfun]

Glad it all helps, Rob!!!! Thanks for your consistent support : )

[@joaquimcosta952]

Wow ...... who is this Dax god. What a haunting quality to explain the topics.

[@excelisfun]

Yes, it took over a month to make this video... All the visuals and animation took a while to think up. But it was fun and I am glad that you like it! There are many more DAX videos with similar quality and visuals to help illuminate the invisible-ness of DAX. Hopefully you will Sub and then check out the full MSPTDA playlist of videos : ) Need help finding anything, just let me know.

[@joaquimcosta952]

ExcelIsFun I discovered this channel by accident. it is impossible not to subscribe and begin to analyze the remaining material. I am truly impressed. I can only thank you for the good work.

[@excelisfun]

@@joaquimcosta952 I am so glad that you discovered it. I have been posting for 11 years of fun and efficient Excel. Please help support on each video with those comment and thumbs up. See you in the comments, Joaquim!!!! Do you use DAX a lot? Were you aware of how ALLSELECTED and KEEPFILTERS worked before this video. When you watch #18, I am curious if you were aware of the potential double count issue when Measures iterate over Fact Tables?

[@joaquimcosta952]

@@excelisfun Unfortunately I don't use it as much as I wanted to, and although I work in finance and some management software, my company doesn't value financial reporting and we're in the 21st century. The age of big data, information. I keep doing it and training.

[@excelisfun]

@@joaquimcosta952 Well good for you, in that you keep doing it and trying to train people to get into the 21st century!!!!

[@GeertDelmulle]

Epic video, Mike. the cinematography we've gotten used to by now, but still: it flows super nicely in your action-packed style. Also, you've built up your format with so many powerful tools (DAX Studio) and techniques (dynamic visuals and storyboards) that you are able to efficiently and effectively explain these rather advanced and complicated matters in the easiest way possible, visualizing *everything*. I've watched several videos by Ferrari on the topic of DAX (explaining things in his self-declared "spaghetti-English") and I'm sure his understanding is second to none, but he doesn't have your format, and therefore his learning curve is way steeper. I truly believe that this is your finest work yet -- you're constantly outperforming your previous self, from video to video in this series. You reached master-level a long time ago, and now you are transcending to God-level in Excel Heaven. In one word: EPIC, borderline legendary. We are truly lucky to witness this ultimate form of altruistic knowledge-sharing. We salute you. (no, I haven't finished watching this one, yet, it's a lot to take in).

[@GeertDelmulle]

BTW: good move to add all files in one ZIP-file: makes it easier to download. I suggest you add that as a general rule to your format.

[@GeertDelmulle]

Mike, at 34:00 in your frequency disconnected table you made the same mistake as once before: the comparative operators should all 'point' in the same direction, e.g.: 5

[@joaquimcosta952]

My english is not very good. I'd say one to five should be two, but I'll try to express some thoughts. This tutorial is so rich in information that it looks more like a dax library. Even more important is the way knowledge is presented to us. The depth of my knowledge has undoubtedly increased with the MSPTDA tutorials 16, 18, 19. I still can't see as many as I wish, but I am persistent and I will continue. Wow ... what more can I say !!! Amazing!!!...

[@excelisfun]

Thank you for your kind and thoughtful words, Joaquim!!!! I am very happy that the video help and are good for you : )

[@bernardarhin2628]

Nobody does it better than you. Absolutely nobody!!!

[@excelisfun]

Thank you!!! As a person who is not that smart, in order for me to understand complex things, I must break it down into very simple terms and visuals, so that means the story that I tell can be understood by more people : )

[@benghwachan7641]

Excellent coverage on CALCULATE with very clear explanation on handling complex filters and expanded tables. Thank you very much for sharing.

[@excelisfun]

You are very welcome for sharing! It is fun to be able to do this hard DAX stuff, and power too! Thanks for your support, Beng : )

[@erickknackstedt3131]

Amazing. Preferred explanation over that offered by the definitive guide to DAX. Which is saying a lot.

[@excelisfun]

THAT is saying a lot. Thanks, Erick!!! I know from my own experiences, having read that AMAZING book many times, and chapter 10 of Evaluation Context at minimum 10 times, this topic of CALCULATE and Filter Context is really hard. For me, until I can visualize it in pictures, like I did in this video, I just do NOT get it. So when I made this video and the 20 associated files that accompany it, I had to take a long time (over 200 hours) and get the visuals right so I could understand it, and hopefully others can understand too : )

[@fcoatis]

Absolutely true. Mike has the capacity/skill to translate/visualize all the hard concepts of the DAX Bible.

[@excelisfun]

@@fcoatis Thanks Fabio!!!!! ... if only I could post a picture of a happy face with DAX formulas floating into the happy persons brain, then the message I am trying to communicate would be even better : )

[@lmoraferia]

100% agree, I read that book also but after watching the video I think I finally got the hang of calculate and filter functions.

[@excelisfun]

@@lmoraferia , Yes!!!! I even did not understand it well enough until I made all the pictures to try and understand the "invisible" side of DAX.

[@erwinnreza2559]

My colleagues attended 5 days training and I watch this video. Mine is much better than them, This is the most amazing DAX Calculate & Filter function video. You are the best!!!

[@excelisfun]

Glad it helps. And the whole MSPTDA class is here at KZbin for free!

[@brianxyz]

I'll have to watch this several times just to get a handle on the terminology and then several more times after that to understand fully the application of the various functions. Thanks for putting so much effort into this video and the accompanying files so that we can gain a better understanding of DAX.

[@excelisfun]

Practice makes perfect! Glad all the resources help, brianxyz! Thanks for the support : )

[@LeilaGharani]

I agree with RRR - This is an epic DAX movie :) Thank you for an amazing tutorial!

[@franciscoisaacs881]

Mike and Leila, you two and other high quality content creators have no idea the level of positive influence and impact you have on content consumers. A million thank yous

[@MrBIKERBOYZZ]

OMG... I watch ur videos too..

[@rayhanhossain7999]

Expect a full dax tutorial from you

[@KumarAnalytic]

*This channel really as super and honestly I've no words to express my gratitude towards you Girvin and all others. I'm from Banaras, India and not so good English and I starting I was able to understand your words only 15-20% but I just follow the same your syntax and each click very carefully. And now it's 3 years and now I'm as Data Analyst and now my Salary is 250% increased and this all credits goes to you all and I would love to say big thanks to sister Leila Gharani, and now watching you continuously I can understand your English 80-90%. I'm remembered your first video I found from my friend in Pen Drive and I had to watch it at least 30-40 times to understand Vlookup because that time your English pronunciation was something hard to me to understand, this vlookup **kzbin.info/www/bejne/Y5mtqXyDd8egkLs*

[@KumarAnalytic]

*Thank you sis Leila finding your comments here is really greatfull....*

[@isidrebague3659]

Thanks for the video, but also, the way to explain it is very clear and transparent. Congratulations.

[@excelisfun]

You are welcome, Isidre!!! Thank you for the support with your comment, Thumbs Up and Sub : ) Did you watch just this video, or did you watch the whole class?

[@isidrebague3659]

@@excelisfun Just this video. I am subscribed to your channel and I watch videos according to my interest on some topic. English is not my native language, for this reason I appreciate when the explanation is clear.

[@excelisfun]

@@isidrebague3659 , I am glad that things are clear! Are the visualizations and animations that I use helpful too? Thank you for your support, Isidre!

[@isidrebague3659]

@@excelisfun Effectively. They are not only useful, they are also very complete.

[@excelisfun]

@@isidrebague3659 : )

[@robinking1601]

I want to thank you for taking the time to make these videos, create the files, and the PDF notes. I reference my PDF notes all the time!

[@excelisfun]

You are welcome! That is why I post this definitive CALCULATE video - so that so many of us that had a hard time with the intricacies, can now understand. Thanks for your support on each video, Robin : )

[@kamalsh123]

i'm sure there's no better definitive guide to DAX out there other than this one.

[@excelisfun]

Thanks for your kind words. Marco Russo and Alberto Ferrari Definitive DAX book is pretty good, though : ) Thanks for the support, and glad the video and files help, kamalsh!!!

[@excelbear6860]

many awesome things that makes this video easy to understand; the smaller dimension tables, the showing of PivotTables to reconcile what you did in the measures, DAX studio, and the expanded tables.

[@excelisfun]

Thank you, fellow teacher Excel Bear : ) : ) : )

[@timpanister73]

My understanding of how Calculate works has much improved after watching this video. Thumbs up!

[@excelisfun]

Glad to help, Jenny Tran! Thanks for supporting with your comment, thumbs up and Sub : )

[@nguyeninhty8126]

The video long 2 hours, but I take 02 day to go thru (go along step by step, back and forth alot). Can't image how many effort you put in this video. So awesome and amazing 👍👍👍👍.Now, I'm your big fan 👏👏👏👏👏

[@excelisfun]

Yes, that is right. It took me weeks to make this, and you have the right idea: Since this is packed with so much content, if you study it long and hard, you can become a CALCULATE Master!!!!

[@excelisfun]

Thanks for your support on each video with a thumbs up : )

[@adighose]

Mike you have hit this one out of the park. So glad you took this topic. I was having trouble getting around DAX. Now no more, that you are here. Thanks a ton! This is an epic video. I actually it's good that you have it all in one video. As always thank you very much

[@excelisfun]

You are welcome very much, Aditya!!! Thanks for the homerun metaphor! Now, let's just get this video out to the world so the many people who have a hard time with DAX and with the so-far-in-history resources that teach DAX, can finally have a pictorial method of learning CALCULATE, Filter Context and DAX Formula Evaluation.

[@discrate]

I really appreciate your time and the massive work you did to create this complex but extremely useful tutorial, explained very detailed and crystal clear.

[@excelisfun]

You are welcome for the clearness, discrate!!! This CALCULATE video tries to level out not detail about the invisible nature of DAX, unlike most CALCULATE vidoes, even though it make it complex, difficult and not approachable by most.

[@likz1154]

just WOW, The way concepts are explained ..its fantastic, i have seen many tutorials on DAX , trust me no one is up-to the level which Mike has put up on his videos . Excellent Resource to learn , Thanks Mike

[@excelisfun]

You are welcome for the DAX video, Smruti!!!

[@ConanNYC]

wow your step by by step explanation of what is going on inside the dax engine makes it so easy to understand

[@excelisfun]

Yes, indeed. I was like most people, I could not understand how Filter Context and DAX worked, until I made this video... Glad it helps you, Cono!!!!

[@ConanNYC]

@@excelisfun I could be wrong but I can't replicate the problem #6 from the homework in POwerBI, I don't think power bi i can select 2 months from one year and 2 months from a different year in a slicer. Please let me know if I am correct or not thx

[@excelisfun]

@@ConanNYC If you use a Matrix and turn on +/- icons: 1) Format Tab (Paint Brush), 2) Row headers, 3) +/- icons you should be able to do it...

[@TainuiaKid1973]

Oh wow. 14 mins in and you've just demonstrated so clearly the effect of row context and filter context. Now subscribed to your channel.

[@excelisfun]

The whole MSPTDA class is free here at KZbin with over 50 videos: kzbin.info/aero/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1 Thanks for your support with your comments and Thumbs Ups on each video that you learn from. See ya in the comments, TainuiaKid1973!

[@toirshad]

CALCULATE is the paramount in DAX. Thank you Mike making it so simple and understandable!!

[@excelisfun]

You are welcome, Irshad!!!!

[@wayneedmondson1065]

Hi Mike.. wow.. 1 hour 48 minutes.. get me the coffee.. haha!! Looks like another epic video. Looking forward to carving out the time to watch and learn. Thanks and thumbs up!

[@petermyran4986]

Another awesome video - full of valuable information! What an education you are providing so generously. Deep respect and gratitude!!!

[@excelisfun]

You are welcome, Peter!!! There is no doubt = this MSPTDA #19 is THE most important video in the whole series (#18 important too), and even though these two videos are dense and contain complicated topics, without this knowledge, you have the potential for too many hidden errors in DAX Formulas. Thanks for your consistent support, Peter : )

[@K_dyub]

It's hard to find good stuff on CALCUATE so this is much appreciated! God Bless!!

[@excelisfun]

That is why I made it, because I could not find a source that pointed out all the intricacies in an easy way : ) Thanks for watching and supporting, Kenneth!!

[@lmoraferia]

Whatta video !! You did it one more time. You have the great ability for making difficult concepts easier to understand. Thanks Professor !!

[@excelisfun]

You are welcome, Imoraferia!!!!! I am glad that the difficult has become a bit easier!!!!! Thanks very much for your support with your comment, Thumbs Up and Sub : )

[@malchicken]

Excellent and very thorough explanations. I really appreciate you showing the multiple ways to get the same results, but explaining the value of each method.

[@koteshwarpanuganti3507]

Hi, i started learning Power query and Power BI.. Your videos and explanations are awesome...For the last 10 days i have been watching your videos and i think till date i completed till MSPTDA 17. Without your videos with such a structured course, i would have been lost where to start and end. Thanks a lot to you and to your team.

[@excelisfun]

I am glad the the MSPTDA class helps, Koteshwar!!!!

[@JawadAli-nv9mr]

These 100 minutes are the best 100 min on calculate function. genius 👍

[@NoShadowOfDoubt1]

I had to come back and watch this video to double check my understanding of external filter manipulation, it is a great video... thank you Mike!

[@excelisfun]

Yes, that is why the posted videos, pdf notes and Excel workbooks are always available, No Shadow Of A Doubt!!!! : ) You are welcome!

[@richardhay645]

Great video particularly because of the outstanding graphics!!! Takeaway? The FIRST LAW OF DAX: Understand Filtering: DAX is easy. Don't understand Filtering; NOT SO MUCH.

[@ljubicar1987]

Wow!! I have just watched the video, and have no adequate words to say how amazing it is and to thank you enough for your effort. MSPTDA series of videos are sure the most powerful when it comes to Power Pivot and Power Query. Go Mike!

[@excelisfun]

Thank you, ljubicar1987!!! Go Team!

[@jamesmurdock7958]

This was the best one yet. More clear about context transition that I've seen. The notes with images were a big help. May I please have more!

[@excelisfun]

Yes, the combo of the video with the visuals and animation, and the the pdf with static notes and images, is a two-for one about DAX CALCULATE!!! Glad it helps, James! Thank you for your support on each video so I CAN keep making you and the rest of the Team more MSPTDA videos : ) P.S. I have two video projects i am posting in February and then MSPTDA will be running non-stop between March and June (I posted in the community section of KZbin and at Linked about this schedule).

[@nadermounir8228]

Am Epic Video Mike lots of very important information for someone who is new at learning power pivot. Much appreciated Mike :)

[@excelisfun]

You are so welcome, Nader!!!!!

[@mattschoular8844]

Thanks Mike. This looks awesome!! Now I have something to do tomorrow...(Not a football fan here). I will be watching this for sure. Thank you so much for your time to produce such great content.

[@excelisfun]

Yes, CALCULATE over Super Bowl Football, tomorrow, Sunday!!!! Have fun and Thank you very much for your support : )

[@SyedMuzammilMahasanShahi]

WOW. This is so amazing. 50 Examples and all 20 files altogether. Thanks Mike, you are the man. :P

[@excelisfun]

You are welcome, Syed!!! Thanks for your amazing support : )

[@gregfollett8285]

Thanks Mike, I learned a lot!

[@excelisfun]

Thank you for your kind donation, Greg!!!!!

[@xiaojienan7891]

it is epic video even yrs later, sooo helpful. thanks very much

[@dineshmaurya7884]

Many thanks for creating such videos on DAX formulas, the creativity is so much commendable and I think this video session showed up the easiest way of learning DAX. Great work done!!

[@excelisfun]

You are welcome! I an glad that this fun video helps you, Dinesh!!

[@Pierre61]

That has the highest "Knowledge per minute" value of anything I have seen on YT. I'm knackered, but bless you :)

[@excelisfun]

Thanks for the cool compliment, Peter Ball!! I am glad it helps. Luckily, since you are knackered, you can back to watch it again, and you can rewind it as many times as you would like. I figured that I would put all the knowledge in one place for easy one-stop-shopping : )

[@joelngige5776]

Thanks Mike for this great video on DAX. It has taken me sometime spread over three days (not full days off course) to go over the entire video while practicing as i soak in to the DAX formulas. Jumping in/out of DAX Studio was also very helpful into knowing how the formulas work. From Excel point of view its like highlighting a formula and pressing "F9" scenario. I have really learned a Ton of knowledge which i have to practice more hence forth. God Bless you

[@mangeshmehendale4139]

Mike - I cannot tell you how many of my concepts this video thoroughly cleared up - thanks to a massive massive massive effort on your part to create this video with the amount of care and detail that has so clearly gone into making this. Lots of respect from India

[@excelisfun]

You are welcome!!! Most people that use DAX in the world, do not understand the detail presented in this video and therefore can fall into hidden traps. If only I could somehow get this video to everyone in the world who uses DAX, there would be many more incorrect reports. I am glad that enjoyed it and took the time to study it carefully : ) : )

[@mangeshmehendale4139]

@@excelisfun Count me within the ignoramuses! I have spent the entire afternoon with your files trying this and that and measuring expected outcomes with actual outcomes! Only cuz you had shone a light on the path could I actually try and walk ahead, even if on unsteady feet. Sometimes the journey as much fun as getting there! Cheers!

[@excelisfun]

@@mangeshmehendale4139 I agree : ) : ) : ) The journey is often more fun than getting there!!!!

[@mangeshmehendale4139]

On a different note, this is also a long overdue note of thanks. I have learnt Power Query from scratch only through your videos and Ken Puls book. In fact, even today whenever I get stuck - as a visual learner, I automatically come back to your videos and invariably I go back with a "Aha" moment. Thank you once again for hosting a truly superb channel Mike

[@excelisfun]

@@mangeshmehendale4139 You and I both are visual learners. If I had not made all the pictures for all my videos, I would have never learned DAX. Because DAX works so invisibly, if I don't visualize, I don't understand. I hope you will support my efforts, by always clicking thumbs up and tell all your friends : )

[@Jill_Liu]

Hi Mike great thanks for your video. I have seen several tutorial about filter context undoubtly you explained best. Thanks.

[@excelisfun]

Thank you for your kind words, Jill Liu!!!! Filter Context is a very complicated concept so I wanted to make a story that made it less complicated : ) Glad you like the story!

[@Jill_Liu]

@@excelisfun Thanks for your response. By the way, Mike do you have recommend on VBA book / tutorials? I do not find this topic in your playlist either in your book "The Only App Matters” . Appreciate your response. Thanks.

[@excelisfun]

@@Jill_Liu I am not good with VBA, so I never make videos or write about VBA. The only chapter in the "Only App That Matters" that contains some VBA is chapter 20 that covers the Macro Recorder. For me, I have always been able to get the VBA I want from the Macro Recorder. However, much of what we used to use VBA for can be accomplished with Power Query, DAX, LET and LAMBDA functions and Dynamic Spilled Arrays. There are still jobs that require it, but I would look closely at what they ask you to do and see if other tools like Power Query can accomplish it. Sorry I do not have VBA for you, Jil Liu, but at least I have most everything else : ) : ) : )

[@excelisfun]

I just checked out your channel!! Great Channel. I just subbed : ) : ) : ) : )

[@karimallahwala7022]

Very Very helpful to understand DAX Function thank you so much

[@excelisfun]

You are very, very welcome, Karim!!!!

[@markosiridzanski2181]

Sir, I am very happy to see that there are so good people like you sharing this amazing content. You just inspire me to learn all related topics on your channel :)

[@excelisfun]

Yes, sir! That is my goal: free education for the world! Thanks for your support with comments, thumbs ups and of course your sub : )

[@BolajiBI]

I finally understand Filter and Row Context in DAX, thanks to this awesome video and playlist. Not so many videos out there that graphically explains DAX evaluation contexts. Thank you!

[@excelisfun]

Yes, indeed: DAX is hard and pictures really help to understand the invisible way that DAX works : )

[@brunof.s.8186]

This is MSPTDA series is beyond awesome. It's another whole level. Someone should make an NFT of this video!!!

[@excelisfun]

Glad you like the awesomeness : ) But: what is NFT?

[@excelisfun]

non-fungible token = cool!!!! : )

[@DIGITAL_COOKING]

no five stars this time, you deserve an OSCAR EXCEL VIDEO :) I liked so much your replay for Erick great words

[@excelisfun]

Thank you for the Oscar, DIGITAL COOKING!!!! Glad you liked the comment on Erik's comment. And its true: I could never understand DAX without pictures...

[@pravinshingadia7337]

Amazing video!! Thanks again for another brilliant and informative video. Been put on a new assignment to build a Power BI dashboard so will be spending time catching up on these videos from you.

[@excelisfun]

Glad that the videos help you, Pravin!!!! Thanks for your support with your comment and thumbs up on each video and of course your Sub : )

[@enriquedominguez9709]

Thank you Mike, great way to unveil the deepest mysteries of calculate.

[@excelisfun]

Glad you like it, enrique!!! Thanks for the support!

[@Softwaretrain]

I can't believe how this is possible to gather all these details in one video. No doubt this is the best and also hardest tutorial that I saw. but the hard part is coming from too many concepts behind the sense, not about your explanation. you make it as easy as possible. regarding Calculate function as I heard from Mr. Alberto Ferrari, he said Calculate is a queen, not king, since it's a woman, why? because nobody can understand it completely😂😂😂 and also it's really powerful. Thanks for your all efforts. I appreciate.

[@excelisfun]

I took a few classes from Mr. Alberto Ferrari a number of years back, but I never heard that joke about queen lol You are welcome for all the efforts. BTW, it took me 34 days and 244 hours to make this video. Something like this takes A LOT of time lol

[@xavielmejia2732]

Thanks for sharing your knowledge with us, it is extremely important to manage the filter context when using DAX, and the way you explain it is really clear!

[@excelbelajar]

Love your Tutorial So Much. Have been following you since the beginning I learn Excel.

[@excelisfun]

Glad to help from your beginning until now, Beljar!! Thanks for the support with your comments, thumbs ups and of course your Sub : )

[@excelisfun]

The Frequency Distribution Sales Count Categories are not correct in the video, but in the Download file, the categories are correct. After the video is published, KZbin does not let me edit... : ( Note: In the Video the category shows: 0>=Sales

[@luisantonioacunaasenjo8467]

Best explication of disconnected tables min 40:00. MUCHAS GRACIAS!!!!!!!

[@excelisfun]

You are welcome, Luis! Thank you very much for your support of my efforts to bring quality education to the world for free with your comments and thumbs ups, and of course your Sub : )

[@JoshuaDHarvey]

42:00 still trying to wrap my head around the idea that filter can receive instructions from a disconnected table. Technically the 'disSalesLimits' table is providing filter context into the FILTER argument of CALCULATE but I didn't realize columns on the other end of the conditional operator could receive filter context especially when the measure is based on a disconnected table... Hoping to see more of that later so I can understand better and visualize what's happening. As I mentioned in my other comments, you aren't just amazing at making videos on KZbin but your responses to comments and support for your viewers makes you one of the best KZbin Channel providers on KZbin! If you think about that... that's kind of a big deal 😁

[@excelisfun]

Well, is the disconnected column is in the row area of the Pivot, then it will filter the table, even if it is not in a relationship with any other table.

[@excelisfun]

I think about it every time you click that thumbs up, comment and tell all your friends : )

[@excelisfun]

BTW, I can not always answer comments. I get 100s of correspondences every day and it is impossible for me to get to them all, and work at my normal job, and make videos here at KZbin, and have a family...

[@JoshuaDHarvey]

@@excelisfun Totally understandable! Thanks for letting me know. 😊 PS - That's pretty amazing that you are able to do all three! Thanks again!

[@PVTzzz]

Great video. These guys are moving a hella lot of boomerangs!

[@excelisfun]

Glad you like the video, Blake!!!! Back in the 1980s and 1990s, when I ran a boomerang company, I NEVER sold this many : )

[@DinoAMAntunes]

Most amazing explanation, of Calculate in the internet, Thanks . At last i understand it.

[@MalinaC]

Beautifully explained! Thank you, Mike. Thumbs up!

[@excelisfun]

Glad it is beautiful, Teammate! Thanks for your support : )

[@rrrprogram8667]

Epicccccc videooo..... Ufff soo much to watchhhh... Soooo much efforttt

[@shuyukahn]

Thanks a lot for the clear explanation. Your videos are the best!

[@excelisfun]

You are welcome!!! Thank you for your support and tell everyone who wants to learn about CALCULATE that knowledge is in this vid!!!!!

[@davideferrari9333]

Hi. First of all thank you very much for this epic video. In problem #6 of the homework you suggest to calculate Average Transactional Revenue: I don't understand why I obtain two different results if I use this 2 DAX formulas: 1) Total Revenue:=SUMX(fTransactions,fTransactions[Units]*RELATED(dBoomProducts[RetailPrice])) Avg Transactional Revenue with measure:=AVERAGEX(fTransactions,[Total Revenue]) ---> $2311.63 2) Avg Transactional Revenue :=AVERAGEX(fTransactions,fTransactions[Units]*RELATED(dBoomProducts[RetailPrice])) ---> $2310.95 thank you in advance!!!

[@williamarthur4801]

Finally finished over a couple of days , I think I might have tomorrow off. Learned a heck of a lot, now to remember it all. 😉

[@excelisfun]

My videos are always here for you if you forget : ) pdf notes and Excel files too, william!!!

[@nidhipandya7132]

Great source!!! I can't thank you enough for all the efforts you put into this to make it as lucid as possible.

[@ogwalfrancis]

Thanks alot Mike, Your videos are always very amazing.

[@excelisfun3903]

You are welcome, ogwal!!!! Thank you for your support : )

[@ozansen_ozibaba]

Hello Mr. Mike , Thank you so much for this high class tutorial . It made everything clear to me now . Pdf Notes is also very well prepared and assists in understanding the subject . I especially like your classes about DAX and Power Query . Wish you all the best , Master :)

[@excelisfun]

You are welcome, ozan!!! Thank you for your support : )

[@techproductowner]

Dear sir , this is an epic dax tutorial .

[@ismailismaili0071]

Thank you so much Mr. Mike you are always the best

[@excelisfun]

You are welcome, Ismail!!! Thanks for your support : )

[@olcia2467]

Epic video that really helped me to understand as well as a pdf which i used to undersntand better. I am looking forward to see more videos about DAX.

[@excelisfun]

Glad this helped with the DAX! Here is the full playlist with many DAX videos: kzbin.info/aero/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1

[@777tmack]

Freaking awesome!... Like every great movie, it gets better after each viewing!

[@excelisfun]

Also, the definition of great art : ) Thanks for watching and support, Mack!!!!!

[@chakche]

Great video, great help in everyday job. You are the best. Thank you 🙏🏻

[@excelisfun]

You are welcome, Crac!

[@JoshuaDHarvey]

35:40 What pre-requisite statistics video/series were you referring to?

[@excelisfun]

It was discussed in the first video. It is Busn 210 at Highline College. The class is here at KZbin: kzbin.info/www/bejne/jmrbqKF-qpd7Y6M Also, this 2 minute video shows you how to find what you want at my channel: kzbin.info/www/bejne/omKQYpSMnKunnpY

[@simfinso858]

Superlative Level of Excel knowledge 🤐🤐🤐😷🤒🤕😨 wow

[@excelisfun]

Glad you like the WOW, Finance in 5 minutes!!! Thank you very much for your support : )

[@celia5132]

WOW WOW WOW WOW WOW! EXACTLY WHAT I'M LOOKING FOR! THANK YOU SO SO SO MUCH MIKE!!! I have to capitalize all the letters as I am o grateful for you!

[@excelisfun]

YES!!!!!! I love to hear that this video helped : ) I am not sure there is a more complete one-stop shopping video for CALCULATE than this one ; )

[@celia5132]

@@excelisfun I would say that it is not only a place to learn Calculate comprehensively, it also has so many good examples that are super useful and very easy to be applied to the real world to simplify my life! There are videos that show information but I still have no clue on how to use it. All in all, I would like to thank you again Mike for your precious time to make this majestic tutorial! Have a lovely day!😄

[@rrrprogram8667]

This is not less than a movie... U should have named it.. CALCULATE movie... Mike is the lead character... And dax is the heroine

[@DIGITAL_COOKING]

I am amazed by this series, and I believe if someone understands it well and exercises I guaranty how much good in BI he will be... Five stars

[@excelisfun]

Thank you, DIGITAL COOKING! This video is the single most complete video on CALCUALTE ever invented, at least so far : )

[@DIGITAL_COOKING]

@@excelisfun yes Indeed !

[@chrism9037]

Great video Mike, thanks as always!

[@excelisfun]

You are welcome as always! This one took a long time to make... Thanks for the support, Chris!

[@pablofts1569]

I can't belive this is free

[@excelisfun]

This is what I have been doing for 13 years at KZbin: free excel and power bi education for the world : ) BUT: I do charge a thumbs up for each video that you watch lol

[@malcorub]

Oh snap, first video of the year for this series. Power BI FRIDAY NIGHTS, what has my life become. LOL

[@girishghadge8460]

Thanks!

[@girishghadge8460]

Please don't Mention as this amount is nothing compared to the effort you have put in the content making and the energy you have put in entire course is amazing keep the good work going May god bless you Thanks a ton

[@excelisfun]

Thank you for the kind donation, girishghadge8460!!!! It really helps me to keep making videos : ) : )

[@excelisfun]

I would have replied earlier, but KZbin did not send me notifications... : ( : ( I will mention how awesome you are for the donation ; )

[@oscarkatsinis6817]

Thanks Mike!!! Amazing. I just found these really epic videos. Congratulations, your classes are really very didactic.

[@excelisfun]

Glad they help, Oscar! Thanks for your boomerang back help: with your comments and thumbs ups : )

[@peperazo]

Excellent video!!!

[@excelisfun]

Glad it was EXCELlent for you, Alejandro!!!!

[@JawadAli-nv9mr]

The best of all I found on calculate function.

[@excelisfun]

It is quite comprehensive. Glad you liked it, Jawad!!!

[@phanindragaddam7607]

Amazing tutorial 🙏🏻! Great explanation!

[@excelisfun]

Glad it is amazing for you, PHANINDRA!!!! Thank you for your support with your comment, thumbs up and Sub : )

[@deepakagrawal465]

Just one word... Wowwww.... Some super deep dive into DAX granularities. Would be great if you can cover "Handling ragged hierarchies using ISINSCOPE / ISFILTERED / ISCROSSFILTERED functions in DAX", since Excel and Power BI do not support ragged hierarchies till now (SSAS 2017 does).

[@excelisfun]

I have not used ragged hierarchies. What are they? ISINSCOPE sounds fun, though : )Thank you very much for your support, Deepak!!!

[@deepakagrawal465]

@@excelisfun Ragged hierarchy is pretty common across businesses. e.g. in accounting g/l groupings are at different levels and when browsed through pivot tables the lower members in the hierarchy either get repeated or blanks appear while drilling down. This makes navigation of P&L very poor because pivot table doesn't ignore a blank member or repeating member in hierarchy. This behaviour can be solved using function like ISINSCOPE for checking the granularity of hierarchy members. You can read Kasper's blog on this ... www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[@excelisfun]

@@deepakagrawal465 Thank you for the incredible link. I will have to study up on Ragged hierarchies and consider a video later in this series.

[@deepakagrawal465]

@@excelisfun Just a suggestion. DAX complexity reduces many times using "VARIABLES" and the code becomes fast also because a defined "Variable" gets executed only once after defining. In fact, Marco and Alberto (The great Italians) are rewriting the God of DAX book "Definitive Guide to DAX" to redefine complex DAX codes using "VARIABLES". Following are some of the links highlighting the importance/usage of "Variables" inside DAX: www.sqlbi.com/articles/variables-in-dax/ www.sqlbi.com/articles/optimizing-if-conditions-using-variables/ www.sqlbi.com/articles/dax-coding-style-using-variables/ www.kasperonbi.com/use-more-variables-in-dax-to-simplify-your-life/

[@excelisfun3903]

@@deepakagrawal465 I agree, Variables are vital to good code. I meant to introduce them in this #19 video, but of course there was just too much content and I cut it out. But soon...

[@andreasbeck3336]

Another amazing video by Mike the DAX Magician! Thank you so much !!

[@excelisfun]

CALCULATE is THE DAX Function! You are welcome so much!!!

[@sane7263]

I would really like to see more videos on "Complex Filter", btw as always Great work Buddy👍

[@langthangtinhbun]

Thank you very much for you video.

[@excelisfun]

You are welcome!

[@ElbinaRizakhanova]

Superb lesson!

[@excelisfun]

Glad it is superb for you, Ebina!!! Thanks for the support with your comment, Thumbs Up and Sub : )