MSPTDA 19: CALCULATE DAX Function & Filter Context & ALLSELECTED & KEEPFILTERS (50 Examples)
Вставка
- Опубліковано 30 вер 2024
- All 20 files used in video are available in this zipped folder: people.highlin...
Download file individually at class web site: people.highlin...
pfd notes for Video #19: people.highlin...
Comprehensive video about the CALCULATE DAX Function and how Filter Context works to calculate DAX Formulas in Power BI and in Excel Power Pivot.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction
2. (01:27) Filter Context, First Look
3. (04:40) CALCULATE to change Filter Context with a Boolean Filter
4. (05:10) CALCULATE and CALCULATETABLE DAX Functions
5. (06:28) What is a Boolean Filter?
6. (08:00) First look at the Overwrite Operator in CALCULATE. Merge the Internal Filter Context and External Filter Context
7. (09:45) CALCULATETABLE to change Filter Context with a Boolean Filter
8. (11:25) CALCULATE to perform Context Transition
9. (13:17) All Measures have a Hidden CALCULATE Function
10. (14:20) Boolean Filter is always converted to a FILTER & ALL DAX Function construction
11. (17:22) DAX Studio to look at FILTER and ALL Equivalent for a Boolean Filter
12. (20:00) VALUES rather than ALL in first argument of FILTER
13. (22:05) VALUES Function to bring a Variable into a Formula and use it as a condition for a Boolean Filter
14. (24:22) AND Logical Test - 4 Examples
15. (26:18) Can NOT use two different columns in a Boolean Filters
16. (28:30) OR Logical Test Boolean Formulas
17. (30:33) Can NOT Directly Compare Two Columns as a Boolean Filter. Keystone Pricing Examples.
18. (33:20) DAX Studio to see how FILTER and ALL delivers a table where we directly compare two columns for a DAX Measure
19. (34:00) Can NOT use MIN or MAX or other aggregate functions as a condition for a Boolean Filter
20. (34:00) Frequency Distribution DAX Formula. Learn about the COUNTROWS Function
21. (43:07) ALL DAX Function & Grand Totals. Learn about the Remove Operator in ALL DAX Function when you use it in the Filter argument of CALCULATE
22. (47:42) Look at Data Model for Power BI File for looking more closely at the Overwrite Operator
23. (49:00) Overwrite Operation. Second look at the Overwrite Operator in CALCULATE. Merge the Internal Filter Context and External Filter Context into the Final Filter Context
24. (50:50) Reminder about ALL before we learn about ALLSELECTED. What is the problem that ALLSELECTED can solve?
25. (52:35) ALLSELECTED to create Grand Totals that respect the filtering in the PivotTable
26. (53:33) Context Transition is how ALLSELECTED gets to the Grand Total Cell Filter Context
27. (53:45) Hidden Context Transition for Measures in an Excel PivotTable or Power BI Visual
28. (54:45) ALLSELECTED and Context Transition, the Full Story
29. (56:17) Visual Example of when ALLSELECTED will not get back to the Filter Context of the Grand Total Cell in the Excel PivotTable or Power BI Visual
30. (57:18) Use ALLSELECTED DAX Function in CALCULATE Measures
31. (59:05) ALLSELECTED Functions works on Cross Tab Reports too !
32. (59:44) Example of Measure in Power BI Visual that uses ALLSELECTED and will not get back to the Filter Context of the Grand Total Cell Power BI Visual
33. (01:01:20) Look at new Data Model for next example
34. (01:02:20)First example of the KEEPFILTERS DAX Function to force an AND Logical Test rather than an Overwrite Operation
35. (01:06:55) What is a Complex Filter? What is a Complex Filter Reduction Error?
36. (01:08:56) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column in AVERAGEX.
37. (01:14:47) Why KEEPFILTERS is used in the New Quick Measure in Power BI
38. (01:16:33) How KEEPFILTERS can help solve a Complex Filter Reduction Error for Year Column and Month Column in CROSSJOIN in AVERAGEX
39. (01:18:30) Data Model solution to solve Complex Filter Reduction Error
40. (01:20:05) Determine if a filter is a complex filter with CROSSJOIN
41. (01:21:17) Expanded Table Concept & Relationships.
42. (01:26:50) Table Filters & Expanded Table as filters in the Filter argument of CALCULATE
43. (01:27:15) Expanded Table Filter with ALL Function. 2 Examples
44. (01:29:15) Table Filter to send a filter backwards across a Many-To-One Relationship. Great Visual for understanding this filter
45. (01:33:10) ALLEXCEPT DAX Function with an Expanded Column
46. (01:37:00) Examples of Time Intelligence Functions
47. (01:37:20) Measure for Revenue from Last Year. SAMPERIODLASTYEAR
48. (01:38:40) Measure for Revenue for Last Month. DATEADD and ISFILTERED DAX Functions
49. (01:41:32) Compare Excel and DAX Formulas
50. (01:43:10) Measure for % Change using IF, BLANK, DIVIDE and HASONEVALUE DAX Functions
51. (01:46:15) DAX Formula Evaluation Context Summary
Another awesome video - full of valuable information! What an education you are providing so generously. Deep respect and gratitude!!!
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 : )
Love your Tutorial So Much. Have been following you since the beginning I learn Excel.
Glad to help from your beginning until now, Beljar!! Thanks for the support with your comments, thumbs ups and of course your Sub : )
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!
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!
Bravo !!!!! Thank you 💕💕💕💕 😊
@@danjarupath You are welcome rad racer!!!!
I came here to write this comment. Totally agree with you, Diego.
@@szpyrkowski Thanks : )
Really good videos here. I am enjoying it.
I still have some confusion towards the keeptilters section. In the crossjoin example(average month revenue), only the grand total field has the wrong value, whereas, in previous example(average year revenue), both the subtotal and grand total fields go wrong and the reason is clearly explained.
However, based on my understanding, in the crossjoin example(average month revenue), wouldn't subtotal also calculate the average value for 2017 nov, 2017 dec, 2017 jan and 2017 feb? I must have some misunderstanding here. Hope can have some suggestions.
Thanks.
I agree with RRR - This is an epic DAX movie :) Thank you for an amazing tutorial!
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
OMG... I watch ur videos too..
Expect a full dax tutorial from you
*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 **ua-cam.com/video/-hJxIMBbmZY/v-deo.html*
*Thank you sis Leila finding your comments here is really greatfull....*
This is not less than a movie... U should have named it.. CALCULATE movie... Mike is the lead character... And dax is the heroine
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.
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!
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, UA-cam does not let me edit... : ( Note: In the Video the category shows: 0>=Sales
Oh snap, first video of the year for this series. Power BI FRIDAY NIGHTS, what has my life become. LOL
no five stars this time, you deserve an OSCAR EXCEL VIDEO :)
I liked so much your replay for Erick great words
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...
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.
Yes, CALCULATE over Super Bowl Football, tomorrow, Sunday!!!! Have fun and Thank you very much for your support : )
Epicccccc videooo..... Ufff soo much to watchhhh... Soooo much efforttt
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!
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!!!!
it is epic video even yrs later, sooo helpful. thanks very much
Amazing. Preferred explanation over that offered by the definitive guide to DAX. Which is saying a lot.
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 : )
Absolutely true. Mike has the capacity/skill to translate/visualize all the hard concepts of the DAX Bible.
@@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 : )
100% agree, I read that book also but after watching the video I think I finally got the hang of calculate and filter functions.
@@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.
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).
I have not used ragged hierarchies. What are they? ISINSCOPE sounds fun, though : )Thank you very much for your support, Deepak!!!
@@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/
@@deepakagrawal465 Thank you for the incredible link. I will have to study up on Ragged hierarchies and consider a video later in this series.
@@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/
@@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...
Amazing learning, I have subscribed to Marco Russo & Alberto Ferrari's paid course, but this is way simpler, clearer & better. Thank you
You are welcome, Bulls and Bears!!!
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!
Glad it all helps, Rob!!!! Thanks for your consistent support : )
Waou ! This is amazing. Thank you for making freely available such an extraordinary content to the World. This is a thorough content.
You are welcome!! : ) This is what I have been for the last 15 years at UA-cam.
Am Epic Video Mike lots of very important information for someone who is new at learning power pivot. Much appreciated Mike :)
You are so welcome, Nader!!!!!
Excellent coverage on CALCULATE with very clear explanation on handling complex filters and expanded tables. Thank you very much for sharing.
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 : )
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.
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 : )
Very Very helpful to understand DAX Function thank you so much
You are very, very welcome, Karim!!!!
Superb lesson!
Glad it is superb for you, Ebina!!! Thanks for the support with your comment, Thumbs Up and Sub : )
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..
I am so glad that the videos that I post can help : )
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
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.
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!
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 : )
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.
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 : )
Thanks for the video, but also, the way to explain it is very clear and transparent. Congratulations.
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?
@@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.
@@isidrebague3659 , I am glad that things are clear! Are the visualizations and animations that I use helpful too? Thank you for your support, Isidre!
@@excelisfun Effectively. They are not only useful, they are also very complete.
@@isidrebague3659 : )
Right now I have never seen video like this..its more than amazing❣️❣️❣️
I am glad that the video can help you, #ROCK!!!
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 👏👏👏👏👏
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!!!!
Thanks for your support on each video with a thumbs up : )
Great video Mike, thanks as always!
You are welcome as always! This one took a long time to make... Thanks for the support, Chris!
It's hard to find good stuff on CALCUATE so this is much appreciated! God Bless!!
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!!
Wow ...... who is this Dax god. What a haunting quality to explain the topics.
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.
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.
@@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?
@@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.
@@joaquimcosta952 Well good for you, in that you keep doing it and trying to train people to get into the 21st century!!!!
Superlative Level of Excel knowledge 🤐🤐🤐😷🤒🤕😨 wow
Wow, Wow, Wow : )
Superlative Level of Excel knowledge 🤐🤐🤐😷🤒🤕😨 wow
Glad you like the WOW, Finance in 5 minutes!!! Thank you very much for your support : )
Great video, great help in everyday job. You are the best. Thank you 🙏🏻
You are welcome, Crac!
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).
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.
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
Hi sir, in your video at (32 minute 8 second), as you told that 2 different columns in the same filter argument is not possible so we use filter function, but in below mentioned 3 cases answer coming same.
1) Total Keystone Sales:=CALCULATE([Total Sales],FILTER(SUMMARIZE(dProduct,dProduct[Price],dProduct[Cost]),dProduct[Price]>=dProduct[Cost]*2))
2) Total Keystone Sales:=CALCULATE([Total Sales],FILTER(all(dProduct[Price],dProduct[Cost]),dProduct[Price]>=dProduct[Cost]*2))
3) Total Keystone Sales:=CALCULATE([Total Sales],FILTER(dProduct,dProduct[Price]>=dProduct[Cost]*2))
my question is which one is correct from above 3 cases.
This is MSPTDA series is beyond awesome. It's another whole level. Someone should make an NFT of this video!!!
Glad you like the awesomeness : ) But: what is NFT?
non-fungible token = cool!!!! : )
Great video. These guys are moving a hella lot of boomerangs!
Glad you like the video, Blake!!!! Back in the 1980s and 1990s, when I ran a boomerang company, I NEVER sold this many : )
Great effort
Thank you soo much
You are welcome so much, abo!!!! Thank you very much for your support with your comment, Thumbs Up and Sub : )
Whatta video !! You did it one more time. You have the great ability for making difficult concepts easier to understand.
Thanks Professor !!
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 : )
35:40 What pre-requisite statistics video/series were you referring to?
It was discussed in the first video. It is Busn 210 at Highline College. The class is here at UA-cam: ua-cam.com/video/X9xwnHu2H-A/v-deo.html
Also, this 2 minute video shows you how to find what you want at my channel: ua-cam.com/video/l1-1aVgFth4/v-deo.html
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!!!
Thanks for the million likes : ) : ) : )
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.
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.
WOW. This is so amazing. 50 Examples and all 20 files altogether. Thanks Mike, you are the man. :P
You are welcome, Syed!!! Thanks for your amazing support : )
Hi Bill Jellen,
You can use NATURALINNERJOIN/NATURALLEFTOUTERJOIN to see the Expanded Table.
For example in DAX Studio, you can try:
EVALUATE
NATURALINNERJOIN(
dDate,
NATURALINNERJOIN( dPRODUCT, fTransactions)
)
Nobody does it better than you. Absolutely nobody!!!
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 : )
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!!!
Amazing tutorial 🙏🏻! Great explanation!
Glad it is amazing for you, PHANINDRA!!!! Thank you for your support with your comment, thumbs up and Sub : )
I think Richard Hay is right: Understanding Filtering is the gateway to work successfully with DAX. The video MSPTDA 19 is a great help to learn how filtering works.
I always stopped the video after it has shown a filter and I tried to find out how the filter works. For this, I took up the idea of Chapter 3 of this video MSPTDA 19 to check whether I was right. I wrote measures with an internal filter, that should do the same as the external filter does. But the filter in Chapter 20 confuses me. I found out how it works. The video showed that I was right. But I failed to write a measure with an internal filter, that does the same filtering as the rows of the pivot table do externally. I checked the DAX Function
FILTER(
ALL(disSalesLimits),
disSalesLimits[Category]="10
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.
I have a question. I calculated the Keystone figure using the dProduct table as the table in the ALL filter. If this calculation is put into Pivot Table 4, both the Carlota & Quad rows show $45. But if dProduct[Price], dProduct[Cost] is used in the ALL filter, only the Carlota row has a number. I thought the ALL filter cancels out the external filter & both rows would have the $45.
Hi Mike, is there any way we can filter year and month in Power BI. For example select Year 2018 and month Nov & Dec and Year 2019 and month Jan & Feb?
Non-technical question... but just because I know Mike would reply :)
As it relates to learning and becoming more fluent in DAX, the most advice people give is to work on lots of datasets (projects) but in my opinion, getting a solid foundational understanding of how DAX evaluates your formula and then trying different analysis on a few small sample datasets is better.
If I was one of your BI 348 students who really wants to learn and get better with DAX, how would you suggest I go about it?
P.S This is my favorite video, I've watched it over and over and over again.
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!
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 : )
Hi, on the frequency pivot I notice the 5> sales < 10 is at the bottom, not the same order as the data model, I couldn't get this to sort correctly so added "" ; so ""&B27&"
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!!!...
Thank you for your kind and thoughtful words, Joaquim!!!! I am very happy that the video help and are good for you : )
For calculating Just quad Rev KF, Keepfilters did not worked in Excel 2010.
Is it possible to count rows based on dates exceeding end of quarter i.e. 9th September, 2019 is counted in Q1 2019, Q2 2019 Whereas its not counted in Q3 2019. This is because an activity started in Q1 remained open in Q1 & Q2 and closed in Q3?
I have one query,
how to calculate the date of a specific sale which happened last year,
for example this year I reached to my target sales(3000 sales) In the month of aug, I want to know when the same amount of sales(3000) happened last year?
is it possible to have such DAX?
Thanks a lot for the clear explanation. Your videos are the best!
You are welcome!!! Thank you for your support and tell everyone who wants to learn about CALCULATE that knowledge is in this vid!!!!!
Thank you, but how can you use the small/large function to extract data? Tnank you!
In Excel or DAX?
@@excelisfun In excel, using small/large functions to extract the Nth occurrence of a variable (error: #N/A, #DIV0!, etc; Sales Rep, Location, Price), compound variables (i.e. Sales Rep + Sales Location/Product, error + Sales Location, multiple criteria). By the way, your tutorials/tricks are so enligthening!
Hello
Mike Girvin,
I am rafiqul .I am from Bangladesh . I wants to buy your book . it is available in market . how can i get it . if possible please inform me .
Great video, watching from Mexico City amigo. Thanks!!!
May I ask you one question on AllSelected as Table function vs calculate modifier . I am clear on the calculate modifier now . but would you like to share something about allselected as table function .
Thanks alot Mike, Your videos are always very amazing.
You are welcome, ogwal!!!! Thank you for your support : )
Hi Mike great thanks for your video. I have seen several tutorial about filter context undoubtly you explained best. Thanks.
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!
@@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.
@@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 : ) : ) : )
I just checked out your channel!! Great Channel. I just subbed : ) : ) : ) : )
Thank you so much Mr. Mike you are always the best
You are welcome, Ismail!!! Thanks for your support : )
Hi , i need to build a report need help ...example sales report from few country for 2 year ...data from local country is in local currency. ..say if australia in aud ..new zealsnd in nzd ..etc...how do i change to usd .however each month has different rate and need to add slicer to mtd and ytd per year ...plz help
Thank you very much for you video.
You are welcome!
Great video Mike! Thank you!
You are welcome, Cristian! Thank you for clicking that Thumbs Up, commenting and your Sub : )
Beautifully explained! Thank you, Mike. Thumbs up!
Glad it is beautiful, Teammate! Thanks for your support : )
Most amazing explanation, of Calculate in the internet, Thanks . At last i understand it.
i'm sure there's no better definitive guide to DAX out there other than this one.
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!!!
oh yes! a MSPTDA episode.
Yes!!!! MSPTDA: the UA-cam Class Video that can take up LOTS of your time with fun learning and studying : ) Thanks for the support, Excel Bear!
DAX Blockbuster movie ❤️..thanks a lot 🙏🤟
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.
Practice makes perfect! Glad all the resources help, brianxyz! Thanks for the support : )
Freaking awesome!... Like every great movie, it gets better after each viewing!
Also, the definition of great art : ) Thanks for watching and support, Mack!!!!!
Best explication of disconnected tables
min 40:00.
MUCHAS GRACIAS!!!!!!!
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 : )
Thanks Mike, I learned a lot!
Thank you for your kind donation, Greg!!!!!
this is great.thank you!
Glad you like it!!!!
god bless you!
Glad you like the video, Arman!!!!
Wow, unbelievably helpful! 👌👌👍👍👏👏
I can't belive this is free
This is what I have been doing for 13 years at UA-cam: free excel and power bi education for the world : ) BUT: I do charge a thumbs up for each video that you watch lol
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.
Thank you, fellow teacher Excel Bear : ) : ) : )
hi sir ,
i was trying "values" for distinct count but it didnt work ?
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.
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
18:06 I think you meant to say that ALL() provides the entire table not a unique list. If I'm not mistaken VALUES() or CROSSJOIN on VALUES() provides a unique list or combination list (respectively)
Yes, ALL does innately return a Unique List. ALL and UNIQUE are comprehensively covered in MSPTDA #18. But here is the summary:
ALL
• Unique List
• Removes Filters
• Same Unique List Everywhere
• Return 1 Blank Cell when there are Unmatched Items in Relationship
• If there is no Filter Context = They Both Return Same Items
• ALL(Column or Columns from Same Table or Table)
VALUES
• Unique List
• "Sees" Filter Context
• Unique List in the "Current Filter Context"
• Return 1 Blank Cell when there are Unmatched Items in Relationship
• If there is no Filter Context = They Both Return Same Items
• VALUES(Column or Table)
Hi all,
I'm confused about the external filter context part when Mike explains how Keepfilters function works. Specifically the 2017(2018) Total cell. For example at 1:13:40 the external filter context (Original filter context created by the pivot table itself) is 2017 Nov || 2017 Dec (Am I correct?) but at 1:11:14 it was Nov||Dec||Jan||Feb? (That's weird for me). From what I learn these following steps would have been supposed to happen at 2017 Total cell:
Original Filter Context: [Monthname]="Nov"&&[Year] = "2017"
||[Monthname]="Dec"&&[Year] = "2017"
And there is also another filter context, induced by the context transition (since [Total Revenue] is a calculated field so it is automatically wrapped around by Calculate function and Values function computes its result of only 2017 in this cell) and filtering only 2017, which we will call the “new filter context”:[Year] = "2017"
To merge the two contexts, DAX removes the part of the filter on [Year] from the original filter context and replaces that part with the new filter context:
Original Filter Context Without the Conditions on [Year]:
[Monthname]="Nov"
||[Monthname]="Dec"
The final step is to combine the two filters into an AND condition. The result is:
[Year]=2017
&&
([Monthname]="Nov"
|| [Monthname]="Dec").
By using Keepfilters, we also retain the original filter context:
[Monthname]="Nov"&&[Year] = "2017"
||[Monthname]="Dec"&&[Year] = "2017"
The complete expression will be the intersection between the combined filter context and the original filter context (Keepfilters guides the measure to do so):
Original filter context && combined filter context
[Monthname]="Nov"&&[Year] = "2017"
||[Monthname]="Dec"&&[Year] = "2017"
&&
[Year]=2017
&&
([Monthname]="Nov"
|| [Monthname]="Dec").
Simpy put [Year]=2017
&& ([Monthname]="Nov"|| [Monthname]="Dec").
So basically our final filter context is not different from the original filter context, thus the result of $6,774,886,9 at 1:13:40 is correctly computed but not of that at 1:11:14??? What really undergoes at 1:11:14?
Try watching it again and concentrate on the pictures and diagrams that i displayed, maybe that can help illuminate : )
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!
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 UA-cam and at Linked about this schedule).
Thank you Mike, great way to unveil the deepest mysteries of calculate.
Glad you like it, enrique!!! Thanks for the support!
Not quite come to the end but thank, you thank you, for ages I've been wondering why in DAX when using IF and wanting blank I've been using " " and getting zero's not a huge problem but annoying, my problem is solved and SO EASILY.
You are welcome!!! Just so you know, " " is a space and is text with length of one, whereas, "" is a zero length text item. They are very different. I am glad that this video helps : )
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!!!
Glad it helps. And the whole MSPTDA class is here at UA-cam for free!
In fact, we can get % of grand overall total by simply having another total sales column next to and then right click and selecting the % of grand total option in "show values as" feature instead of striving to build a new DAX formula. Can't we?
You could, but implicit Measures tend to cause trouble.
Excellent video!!!
Glad it was EXCELlent for you, Alejandro!!!!
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.
Glad this helped with the DAX! Here is the full playlist with many DAX videos:
ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Thanks Mike. Lots to study. Amazing as always.
Lots, John, Lots of studying, Lots of FUN studying : ) Thanks for your consistent support!!!!