That's a fantastic video. It took me over six hours to watch it all. I kept stopping the video to practice the tasks and test alternatives. I learned so much. Thank you very much. A hint for others who aren't from the USA either. When loading the .csv files you have to enter the country code at the command Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table. Otherwise I got wrong data. How this works is explained in video number 12.
That is my intent: Make a comprehensive video on this topic that has everything in one video and is for those learners who wants to know the full story, including all the whys. Your approach is perfect: 1) Download all files, 2) Watch Video, 3) Pause, 4) Practice what you see, including alternatives (like you said), 5) Un Pause, 6) Watch More, 7) Practice More, 80 Take a long time to learn because this is such valuable and life/career changing info. Thank you for the tip for non-USA viewers. Yes, Video #12 shows how to deal with international dates: th-cam.com/video/JGppsnepJFo/w-d-xo.html
Glad it helps you, Frank! You can help support my efforts in delivering free education like this with comments and thumbs ups on each video that you learn from : )
Frank I go over a few times and always find something new to learn. Mike is best. Works really hard to prepare detailed examples and notes.. May this to him
WOW! This really is an EPIC GOLDEN Video! So much valuable information packed in one place. I love the overview and comparisons. Thank you so much Teammate Mike.
We pay more for less in schools, but you give MORE for NOTHING, what a wonderful blessing you are to the excel world. I wonder if you know how many people call you #MENTOR. If you can imagine the number, I am one of them. I draw so much inspiration from you that even my students wish to see you, talk less of me.Thanks for this epic video and great lesson.
It is TRUE that I have been trying to give away fun education for 10 years here at TH-cam! I am so glad that it helps you and your students. You can help, though, and really there is a fee for you to use these resources: leave a comment and Click the Thumbs Up on each video that you watch and Sub : ) Thanks, THE REFORM I.T. CONSULT !!!!
@@excelisfun Thanks for permitting me to use your resources. They are or greater value to me than you can ever imagine. If any one asked me the reason why I teach Excel today, I would simply point to your channel. Its has built me to a point where you have become the center in my TRIBE OF MENTORS You are a true genius and hero I will always support your channel the best way I can even though I always feel it's never enough for such great value I reap from your channel
गौरवं प्राप्यते दानात् न तु वित्तस्य सञ्चयात् । स्थितिरुच्चैः पयोदानां पयोधीनामधः स्थितिः ॥ - सुभाषितरत्नसमुच्चय Transliteration: gauravaṃ prāpyate dānāt na tu vittasya sañcayāt । sthitiruccaiḥ payodānāṃ payodhīnāmadhaḥ sthitiḥ ॥ - subhāṣitaratnasamuccaya Meaning of the subhAShita: Respectability comes from giving and not collecting. Clouds (attain) a higher position and the oceans, a lower one. Commentary: What kinds of teachings has God not given us? He has left us hints to all kinds of essentials in nature itself. Even the code of conduct is given to us in nature! Naturally, clouds exist high up in the sky and the oceans are down below. According to the poet, anyone who 'gives', attains higher respect than the one who 'collects' all for himself. The clouds give away all the water they contain and they disintegrate themselves in due course. Once the rain falls they, (clouds) cease to exist. Whereas, the oceans collect all the water that comes their way (even turning the freshwater salty). That is why they haven't reached heights! The essence here is: giving has more value than accumulating or pooling. It could be money, resources, knowledge, wealth or anything of the like. Sharing our knowledge like exce, power query, power pivot, MSBI, etc., by respected Mike is resources and wealth endowment for future generations with our fellow beings brings a higher permanence to the giver, Mr.Mike, as well as, what he is giving to mankind. For example, if one shares knowledge with others, that knowledge gets passed down for generations to come and will live forever. If one shares his wealth, he not only benefits the receiver, but he instils the spirit of giving in himself too. Greed and stinginess have never been approved in any culture. Giving is always an honorable act. pada vigrahaH: गौरवं प्राप्यते दानात् न तु वित्तस्य सञ्चयात् । gauravaṃ prāpyate dānāt na tu vittasya sañcayāt । स्थितिः उच्चैः पयोदानां पयोधीनां अधः स्थितिः ॥ sthitiḥ uccaiḥ payodānāṃ payodhīnāṃ adhaḥ sthitiḥ ॥ For more such precious pearls of great grand mega knowledge warehouse in India's in fact, world's oldest mother of all languages, Sanskrit, visit the blog :sanskritpearls.blogspot.com/search/label/%E0%A4%97%20%E0%A4%98
This is the Best of the Best among Pivot Table and DAX training. I spent whole week to watch all videos twice to get the most out of it. Thanks a lot. Sandra
You are just amazing!!! Thank you from Italy!!!!!!!!I stumbled across your channel 1 week ago....I have been watching your content all day long since then!!! Thank you for the priceless knowledge you share!!!!!!🙏
Refreshing Series, can't believe i had watched this and i am back again, after this series, DAME Series. Thanks for this free class as always. Go Team!!!
Welcome to this this EPIC Video Event!! This is not just a video, but more: there are Excel Files so you can follow along, there are pdf notes with notes about each one of the 40 topics, and there are practice problems (homework problems) so you can practice after studying the video. All these files are available in the links below the video. This Video Event is intended to be a free complete lesson in Power Pivot and DAX. Free education for the world!!!!!!
I really liked your explanation of Context Transition. I have heard about it and read about it in many books and other videos but after watching yours I now finally UNDERSTAND it! Thanks!!!
@@RobMichaels1 , You are welcome! As I mentioned in another comment, I am the same! i read about and took classes that taught Context Transition and I did not understand. But it is really easy when you visualize it and relive that the row condition just needed to floe back across the Relationship to Filter the table on the other side of the relationship. The visualizations are what helped me. So, Rob, glad it helped!!
@@br31woodlands69 , thanks for the words you wrote! And thanks for your support with comments and thumbs up on the videos that you watch : ) Sub and tell all your friends too!
Yes, as always: a complete coverage on the topic, with lots of practice. Thank you. After your full Power Pivot (DAX) session (from a previous release) this was really about practice and deepening. Thanks again. Oh, and happy Thanksgiving!! (I hope I am not too late.)
I'm from Nigeria and I have to say, what you have done is to bring WORLDCLASS education even to the most remote parts of the world. Thank you so so much Mike. The best part is having to work on the Practice Problems :) You are my hero!!!
Judging by the comments here, this will be highly redundant but I just want to thank you from the bottom of my heart for doing this! Absolutely amazing resource for anyone trying to learn this stuff.
There is an epic practice problem (fun homework) below the videos in the links!! But it is for after you study the video : ) P.S. I initially did not have the source data files posted as a link, but now they are posted so you can try the homework problem after studying the video : )
@@cd8490 Gross Profit is being compared to Total Revenue, Not total Gross Profit. If each Gross Profit Amount was being compared to Total Gross Profit, then you would get a column of percentages that added up to one. 100%.
Great Video !!! Is it possible to add columns and rows after the pivot table has been created? I want to be able to manual comments to each row/column. thank you.
Hi Mike, I am from India (Chennai), this MSPTDA videos are nothing but awesome, thanks a lot for democratizing your knowledge to others across the globe. your workings files / pdf notes all are amazing, once again thanks for your selflessness knowledge enlightenment to all. Kudos to your efforts...
Just leave the comment here to prove my strength to go through the whole story :) 2-hour videos but it must take at least 2 days to watch, re-watch, do, re-do homework! I am working in the biggest fast retail company in an Asian country, and I can say this video must be top 1 recommendation for our fresher to first build a new report :)
It is hard to belive that someone can give so much valuable material for free. I know our way to correspond is liking, commenting and subscribing. I have done all of it apart from recommending your channe. It just can't be better. THANK YOU SO MUCH!!!
This is hands-down the best comprehensive guide Power Pivot that I have seen! There are so many valuable terms, concepts, and information packed into one video. What an epic video!
What a truly excellent course! Oftentimes when following along with something like this, you get lost, or something doesn't match what you are seeing, or one of the data files is off. That was not the case with this course. From beginning to end I was able to follow along exactly. A very informative and well-presented tutorial. I learned an enormous amount. Thank you very much indeed. I will for sure be watching more of these.
I am very glad that the class is smooth and you can learn a lot! Yes, the MSPTDA class is quite epic in all of the topics for Data Analysis a& BI using the Mircosoft tools, Power Query, Power Pivot and Power BI ! Thanks for your support on each video with your cthumbs up and comment , and of course your Sub, Franklin : )
I would have to say I really liked the way he presented and tought through out the video.he was quick and quick to the point and skiped the other stuff (going from one point to another).There are soo many boring lecturers (including Udemy), who are very slow to make their point and the way they move their cursor is like snail taking a walk in the park and they are very slow in speach that half way though i lose my concentration.But, this guy is very good and what i like about him is that he is fast and doesn't waste time. For that you have my like and new subscriber.
I am glad that you like what I post for free here at TH-cam for past 11 years, none of that for pay Udemy junk! It is all about editing and telling a story. I depend of viewers like you who will pay for this, but not with cash, but instead with your comment and thumbs ups on each video!!! Thanks for the sub, Nouer!!!
You are the best. Thank you for your teaching us for free. I also follow Leila Gharani and she acknowledges you. Mike Girvin, you are great. God bless you
Yes, Munyaradzi, I am glad the the videos, files and notes that I post for free help you!!!! Thank you for your support with your commnet, thumb up and Sub : )
Before even going through this massive learning journey, I would not imagine we would be having such introductory else where. 2hrs of hands-on , man, that’s a lot of fun indeed. The fact you are even providing homework, make learning curve boosts in a great deal as we would be able to apply and grasp the tips at our own pace. Thanks Mike for sharing your expertise.
You are welcome, Ahmed! Yes, with homework and detailed video and pdf notes that summarize videos - you can learn and have fun! Thanks you for giving back with your comment, Thumbs Up, Sub and telling all your friends : )
I watched your videos back in 2012 when i first tried to understand how excel works. I still remember your intonations and emphasis on certain words, and the way you segue to explain nuances of the operation. Theses small things that made all the difference. Learning Excel through your tutorials allowed me to drive our organization by learning to extract, clean and work with data. The organization 10X in size since then. Now im back trying to learn power query-pivot-and eventually power bi and just so i know how to ask the right questions, and this video gives me the same aha moments i used to get before. thanks so much!
I am happy to hear that the nuances of how I create the stories about Excel to help complicated things seem less complicated works for you Easy PC!!! Thanks for your support : )
Epic is the proper adjective to apply to this video. I learn so much from this TH-cam channel, I am a better professional and an Excel and Power BI ace at my company thanks to Mike Girvin.
Every time I watch your video then I become a changed person. Thanks for your excellent teaching, I learned a lot from you. You are my mentor for Excel!
Happiness in your day is great! And you get to have fun learning with Power Query, Power Pivot, DAX and Data Modeling. Thank you for your support, ZAHID !
You expressed it just fine! For each video you learn from, just click thumbs up, leave a comment if you want, and tell all your friends about excelisfun : )
This is the best overview I have seen yet. Thanks for putting so much into one video with practice data and with links to make reviewing easy. Your videos are the greatest. Thanks.
Professor Mike, this is an EPIC VIDEO! I stop watching on laptop and started watching it on my television. Thank you so much for taking your time to explain everything! YOU SIR ARE A GOD SEND! I am so thankful YOU know that EXCELISFUN and LOVE sharing your knowledge with us! I can't wait to do the homework tomorrow morning after church!
You are my favourite youtuber..... I u literally my teacher in excel..... have learnt everything from your videos..... best past is your free excess on useful sheets...... Thanks Love from Kashmir........
Yes, that is what I have been doing for over 12 years at TH-cam: free videos, notes and files. Thanks for helping me to continue to bring free Excel Education to the world with your comments and thumbs ups on each video that you learn from, Mehboob!
It might be an introduction as well as summary for those who already tryied to work with PP or PBI. Your work is highly appreciate. More youtubers like you please. Thanks!
Yes, that is a really good point, Aron!!! It really is an Intro AND a Summary. Even for me, where I have used Power Pivot and DAX for over 5 years, making this video required that I remember and organize all the tips in a sort of helpful summary. Thanks for your support, Aron!!!!
If this world is fair, TH-cam should create a prise to appreciate TH-cam channel owner Like you, FREE amazing and useful learning, Thank you again and again
The TH-cam world is not fair. I hate to say it, but TH-cam does not value channels like this, that have long videos that tell the complete story. But I post these video anyway, because I know that there are some viewers like you that find value!!! So thanks, Basel : ) : ) : )
Thank you ExcellsFun!! I learned excel from you and I want to learn Power BI and DAX. I have gone through so many channels and finally I came back to you guys. I really appreciate the effort you put in. Thank you again!!
Oh my god what an Event!! Massive knowledge beautifully gathered together in a video that could easily be a semester course! I had to watch the video in "episodes" in order to be able to understand each topic and its usage. I still need many more views and lots of homework to fully integrate all the mentioned concepts. Indeed, not only most introductory videos don't teach such valuable knowledge but also NO-where in TH-cam you will find this kind of teaching! Highly qualified teaching!! Thank you so so much Mike! You give enormous value to the world not only as an instructor but also as an influential person! Your selfless "free education to the world" attitude is awe-inspiring!! Thank you :)
Thank you so much, Evgenia M, for your kind words : ) : ) I do try to put most all the information into one video so you and the other Teammates can have one-stop-shopping!!!!
And you have succeded it to the maximum on every video of yours! Compact and yet detailed teaching worthy of a million thanks that is never enough! I will never stop being grateful to you! :D
Been working through your videos the past few weeks and it's been a great experience. Please accept my gratitude for this awesome education you have provided and for free...you are an amazing individual. I honestly feel that I should contribute to your cause in some way and happy to do so.
Mike You are Really a Genius Man!! Have no word to express my gratitude about what a detailed information you are delivering for NOTHING.Would request few more videos on M code. God bless you!!
I have more videos about M Code. Did you watch this class from the beginning? Here is the full playlist: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html and here is a 2 minute video about how to find just what you want at the excelisfun channel: th-cam.com/video/l1-1aVgFth4/w-d-xo.html
@@excelisfun3903 Mike I always give a thums UP for each video I watch. I will follow your guidence on M code playlist that you have shared. Thanks for replying on my queries👍
Great great video like always. Everytime I have to learn something new from Excel I come to your channel and follow your step by step guides. I have been subscribed for about 5 years now. Thank you very much
This video resolved many issues I encountered in my reports. I view it again and again every time I face difficulty sure that I will find the resolution. Really super
OMG, This video is Mammoth task as compared to other videos. All 39 topic were important in learning DAX. "Thanks Mike" for putting your valuable two hours time in creating this introduction video. This wonderfull video will surely help all the learners out their. In addition to above you have also included supporting data for practices tats wonderful. I am Learning MSPTDA simultaneously with your E-DAB video. Learning never ends with you.😊👍
What? It took 128 hours to make! No human anywhere could create this in 2 hours... I am glad that the E-DAB and MSPTDA help you. That is why i post. Please help with your support on each video with Thumbs Up and Comment : ) Thanks, Santash.
This is an awesome video！It is so clearly instructed and easy to follow with the right pace. As someone who is not that familiar with power pivot, I almost get the points you are making without having to pause or look back. It answered so many question I have and saved me tons of time from googling around. Thank you and good job on it！
Thank you...a lot of information to take in and absorb, I will come back to watch this video again many times to remind myself how to do these... Mike - you are the greatest!
Yes, the intention of this video is to have many of the important tips for DAX and Data Model PivotTables in one place. The Video Event of course has the Excel files, pdf notes and the practice problem so help us all learn the power and fun of Power Pivot and DAX! Thanks for the support, rf05mjy : )
While watching this video, every time I had a question in mind, the very next moment you would clarify that! Perfect compilation and totally relatable to the business problems in analytics! You are awesome.
I am glad to hear that the resources that I have posted help. It gets even better in video #19 in this MSPTDA class, that video shows all about Filter Context. Check it out and let me know if that helps : )
As always Mike invaluable videos on any topic from Excel, Power Query, Power Pivot & Power BI are always packed with great knowledge and you can always decipher the underlying passion from him when teaching. Have really learned so much from your videos since I when I started the Excel learning journey few years back. Inspirational as always Thanks
Yes, I never understood Context Transition with the normal description of "Convert Row Context To Filter Context...", it was not until I actually visualized that we needed the criteria in each row to become a filter that needs to flow through the Relationship (just like normal PivotTable Filter Context) and filter the Fact Table. Pictures helped me to understand, so I am glad the words I chose and the pictures I showed in the video helped you too : ) Glad you enjoyed it, Sanjeev!!!
Yes, I have to make "concepts so clear" because I am not that smart and in order for me to understand difficult concepts like DAX, I have to break it down into simplistic terms and then try and organize the terms, concepts and ideas into an easy to understand story. If I did not spend the 100s of hours doing this, I would never understand it. So really, what makes me an adequate teacher steams from the fact that i am not that smart : )
I know that you have watched 100s of my videos. I REALLY appreciate that you leave a comment on each video and Thumbs Up each video that you watch. You and a few others are the exception to the rule. Most humans just watch, and take the free knowledge without giving back. Further, I do not understand why this is so, given that the videos are often among the most clear and concise videos on a given topic anywhere in the world. Still further, I do not understand why a resource like this, which is accurate and easy to follow AND Free, does not get more views... I have been giving away free education for over 10 years here at TH-cam. And in fact, what I post can really be thought of as "Video Events" that have a video, Excel Files, pdf notes and practice files. My hope for over a decade is that what I post here at TH-cam would reach more people who need free education. But very few of the over 3,000 videos that I have posted have ever reached a lot of people. This video, for example, a comprehensive Power Pivot and DAX video, has 6,000 views, when the actual demand for this sort of knowledge is so much greater...
Love the video. I found the functions in Dax Studio to export the query onto a new table in your current workbook with or without a connection to be very useful. Also you can have it override an existing table; however if your using an DMV function to analyze the data model you need to use a new table. I personally use this at work as a replacement to SQLs as I load the data to the data model and based on the users choices through a single cell table connected to the data model, it loads the table in conjunction with my dashboard. This solves the issue of being able to drill down to the individual transaction. This create similar functionality to Power Bi, with its ability to export the raw data from its dashboard. Thanks again I learned far more than I expected.
Wait a second... You wrote: "I found the functions in Dax Studio to export the query onto a new table in your current workbook with or without a connection to be very useful" Do you mean that there is a button in DAX Studio that can send the query to the Excel Sheet!?!?!?! Can you please tell me where that button is? Or did you use the verb "export" loosely and you were referring to the copy and past action that i showed?
Yes, knowing how to get data from Excel Model to sheet was one of the first tricks I learned a number of years back that allowed me to check and start to believe the DAX and Power Pivot system. Before being able to do that, as an Excel guy who always wanted to "see' and double check, it was hard to get fully behind it...
You wrote: "I personally use this at work as a replacement to SQLs as I load the data to the data model and based on the users choices through a single cell table connected to the data model, it loads the table in conjunction with my dashboard." What specific aspect of the video is a replacement for SQL? Also, you said that you have a single cell table, do you access that single cell table in DAX with the VALUES function?
@ExcelIsFun Single Cell: I use the ALLSELECTED within the CALCULATETABLE function to access the linked table in a similar way to how you linked a single cell table to Power Query to affect a query filter; however, in this case, I used it to affect the filter function in CALCULATETABLE. EVALUATE ADDCOLUMNS(CALCULATETABLE(Usage,Usage[DistWh]=ALLSELECTED(Wh_Selection[Dist Wh])), "Item Name",RELATED(NSN[Item Name]), "Class",RELATED(NSN[Class]), "Inv Seg",RELATED(NSN[Inventory Segment Code]), "SIO",RELATED(NSN[Stock Item Owner]), "Stock Type",RELATED(NSN[Stock Type]), "Warehouse",RELATED(Warehouse[Warehouse Description])) As to the SQL, I load big data from my massive database at work to the data model and using the above method, my collogues can access the data in tabular format without a database management system/SQL and can use it off-site when conducting audits. Using VBA I have massively expanded upon this to include many DAX queries based on a selection( using a user form); however, the only VBA you need is to refresh the data model connections only. (Refresh all will not work as it tries to refresh the queries as well): Sub RefreshDM() ActiveWorkbook.Connections("WorksheetConnection_SHP lite.xlsx!Wh_Selection"). _ Refresh End Sub As to the export form Dax studio: Click on the down arrow under output and you have many options on how you want to export the data and time the query. (I use the timer to assess if I can optimize it). If it is not on your version, you might need to update it. I use DMV to assess my Schema; however, do not seem to export to an existing table. Have to use a new one. Another trick is to click anywhere on a new sheet and insert a table. -Then add the table to the data model. -Copy the table and past it next to the original table. -Edit the DAX query. -Delete the original table. -From there you can add both DAX and DMV queries. Hope this helped. Data mining and dashboards is now my full-time job for a company of 24,000+ employees and over 100 warehouses. I got this job after learning of Power Query through your channel. Before that I just did VBA and SharePoint design (Low level), now I’m the company manager (For data Mining) at the executive level. So a very big thanks from me.
I have gone through most of your MSPTDA lessons these past few days, and many more of your incredible videos. Just wanted to leave a comment to show my gratitude and appreciation of what you do. You are an incredible person and a gifted teacher. Thank you so much for everything. I feel much more confident in my Excel abilities, and hopefully I can become an Excel master like yourself in the future ;-)
You will become an Excel Master, Fredrik!!!! After gaining skills fro the free resource that i post, please help support on each video with a thumbs up, comment and of course your Sub : )
Thank you for your lessons and the way of your teaching. I start from knowing some but now I think that I can teach the essential fundamentals in Thai to share my understanding with others. More time I've spent on your video, more delopment I've got.
That is why I post, Surinee! Be sure to help out my goal of bringing free education to the world, especially to educators like you, with a comment and thumbs up on every video that you watch and of course your Sub : )
Wow.!! That was great...It's like watching Die Hard every Christmas...You know how it ends but it's such a great video, you can watch it over and over again! I will definately be watching this one again. Thanks Mike.
You are welcome! I did put as much knowledge in one place, so you know where to go when you wanted the Basics of Power Pivot and DAX formulas!! Glad it helped, Matt! Thanks for your Die Hard support : )
You are welcome, Manoj!!! I will keep posting if viewers like you support with your comments, Thumbs Ups and Subs!! That way I know you like it and want more : ) If there is the support, I will keep posting in this series for about six more months, and that means a LOT more fun with DAX Formula!
@@excelisfun Oh Mike! My Goodness, you read and replied so quickly knowing you are so busy ! Thanks and you really deserve a big thumbs up! Your Narrative style and explanation on the subject is so amazing that i never saw. In fact I saw this video 4 times and just could not sleep for 2 nights just thinking of amazing fast dashboard creation until I created a dashboard and now tomorrow showing the same to my boss. Thanks a lot! Only one difficulty I faced , I was to pull data through Organization API which is authenticated through Access token ( Oauth) but was not successful. Ultimately I used Postman to get data in CSV file and the n use it. But I want to pull data autoamtically through Powere query. Can you help which option I can use and pass access token for thsi secured API?
You do own me: but not any money : ) Just thumbs up and comment on each video : ) Thank you very much for telling all your peers, and I am glad that the videos and resources help you, Rodrigo : )
I always asked myself: how to create a customized pivot table?. After watching your video, you gave me the answer to that question. Thanks for everything!
Couldn't help it....but to comment again to thank you once again for the video. Downloaded all and having fun trying all sorts of new DAX to learn the Nuts and Bolts. Thanks Mike. Can't wait for what will come next. :)
Thanks very much for the new comment. Always comment as much as you want : ) It helps support the channel. Yes, this video has a LOT in it so it takes some time to absorb. I have been working on the next one for weeks. It should be out in a few days here ...
@@johnborg6005 I hope so too - but I never know because this class just is taking me so long to create. With all the pdf notes, Excel data Sets, Excel workbooks, Video filming, video editing and so on, it just is unpredictable how long it will take. For example, video #9 took over 200 hours, last video #15 took 128 hours and I am already 120 hours into this video... And the funny thing is that the pdf notes took longer to make for this video and #9 than the time it took to make the videos...
Hi Mike, You're my guru from today itself. I found your teaching methodology so easy, crisp and powerful, specially with those explanatory graphics. It helps in visualizing how the things are working behind the scene. You won't believe, but I have watched over 10 hours of videos from your channel in just last 2 days. Thank you for your time and efforts in creating these videos. Merry Christmas & a Happy New Year! :)
I am so glad that the materials that I post help and that the visualizations help you to understand. I know for me, if I did not visualize DAX, I could never understand it. As I help provide you and other free education, you can help support what I do with comments and thumbs ups on every video that you learn from. Thanks, Chanda : )
I can't really find proper words to appreciate your work mate. Simply an amazing human being. But, unfortunately, there are asshole people who bold enough to "dislike" the contents that u put. These are people who dont do a thing for others but still find mistakes of the people who do good... Anyways, thank you very much again for your amazing work and support....
Thank you very much for trying to find the words to say thanks, Asantha : ) I try to make the world a better place by providing these free resources, and so your thanks is appreciated. I also appreciate that you hit that Thumbs Up and leave comments on each vid - that is enough thanks for me : )
भवन्ति नम्रास्तरवः फलोद्गमैः नवाम्बुभिर्भूरिविलम्बिनो घनाः । अनुद्धताः सत्पुरुषाः समृद्धिभिः स्वभाव एवैष परोपकारिणाम् ॥ - नीतिशतक Transliteration: bhavanti namrāstaravaḥ phalodgamaiḥ navāmbubhirbhūrivilambino ghanāḥ । anuddhatāḥ satpuruṣāḥ samṛddhibhiḥ svabhāva evaiṣa paropakāriṇām ॥ - nītiśataka Meaning of the subhAShita: Trees bow down when fruit are shooting forth; clouds hang low when filled with water; the noble become humble in opulence - this is the very nature of the benevolent. Commentary: "If you wish to have happiness, provide happiness for others" - this is probably the motto of the trees, clouds and the noble! They attain their internal peace and calm through their acts of benevolence towards others. They are not just affable, they are humble too! Humility is their prime attribute. When a tree is full of fruit, it doesn't stiffen itself straight in arrogance that it is providing fruit to everyone. It bends down in humility as if to make it easier for everyone to access its fruit! When clouds are brimming with water, they hang low as if in diffidence, so they can shower their cool waters at the earliest to quench the thirst of the earth! The noble when bestowed with opulence, display utmost humility. They do not have even the slightest of arrogance about any of their resources or boons. They display utmost modesty and believe that humility is a lifelong lesson. They do not slack from their humility at any time! It is a natural trait that comes spontaneously to them at all times. Clearly, their stance is that 'humility is the cornerstone of all other virtues'. Hence, they never take a respite from this wonderful attribute. Those that do good to others never take pride in themselves. Pride makes us artificial, humility makes us real. Let's be real : For more such precious pearls of wisdom enshrined in the mother of all languages, i.e. the ancient Sanskrit language, as a great grand mega warehouse of Knowledge, visit the blog : sanskritpearls.blogspot.com/search/label/%E0%A4%AC%20%E0%A4%AD
I can't find the words to describe how amazing and powerful this video was! It was as powerful as Power Tools! :) I have watched a lot of your videos, many of them were epic, but this one blew my mind! Thanks a lot!
Yes!!!! I love to hear that it was powerful for you!!! Just keep going, MSPTDA #16-19 are big Heavy hitters like this one too : ) Thanks as always for stopping by in the comments, ljubicar1987!!!
Great video Mike - going through it again! I like the epic videos as you have all info one place and often takes the whole day to work through. I pause the video and try and do the task or write the DAX formula before you then go through and check my workings!
This is a good video to return to in understanding how Power Query works in the background. Really like the iterator functions (SUMX and AVERAGEX) it can help keep the Applied Steps area nice and clean while still creating your measures.
I am glad that this MSPTDA video helps, Darren! If you like Iterators, have you seen #18 MSPTDA, all about iterators? Here is is: th-cam.com/video/_DHeTnDa2Q4/w-d-xo.html Please help support my efforts to provide free education to the world, and if you learn from a video that you watch, click that thumbs up and comment, and of course your Sub : )
Umbelievable videos very nice No Comment many topics and tricks has been not clear for me and now i see very clear...1000000000000000000000000000000 thankssssssssssssssssssssssssssssssssss
Sure it is, I am using Excel from 3 to 4 hours per day, so every video helps me to refresh my khnowledge or to leean something new or to give me ideas to nest formulas, so I save more and more time, thats why every things is good for me :-) I recognze that without your videos my level has to be still beginner.
Great Overview of DAX! I am working hard to improve my MTDA skills--so greatly appreciated!! My schedule required that I watch it over several days in 30 min segments--so just finished. I finally gave up my perpetual and got 365 on 11/14! Signed up for Insider and 2 days later Dynamic Arrays were available to me. Took Dynamic Arrays for a hard test drive. Great tools, but took lots of notes for MS.
I love to hear that you got the 365, Richard Hay!!!!! I am also glad to hear that you take your time and study the videos in the MSPTDA class, because these videos are dense : ) I trust that you download the Excel files, read the pdf notes and try the practice (homework) problems also? Thank you for supporting this free resource, Richard, with your comments and Thumbs Up on each video that you watch : )
Personally, National Treasure was my favorite but this "Slicer" film may be a close second. Nicholas Cage takes us for a ride in this Power Pivot/Dax Indy masterpiece; Great video. While Cage moves fast he is very easy to follow and covers so much ground, sequentially building upon each step, all ways explaining "why". No "Cage Rage" here, but plenty of tricks.
Awesome! To quote my Excel hero, "Wow THAT was an EPIC Video!" I finally got to sample DAX Studio in a useful way per your good example. Always illuminating, always FUN!
That is why I post here at TH-cam and not at a pay site. Trying to bring Excel and Power BI to the world!!!! Glad all the resources help, marko! Thank you for your support on each video with your thumbs Ups, Comments and of course your Sub : )
@@excelisfun Yes! the homework and the note hepled me review what I learned from the video! Also, today my Boss just asked me clean the data which has hundreds of thousands of rows in EXCEL,and fortunately I used power query tought by you to do that which saved me a lot of time!! Thank you again for keeping making those practical videos!!
Hi Mike.. this looks intense. Looking forward to carving out some time to work through it. As always, thanks for your incredible videos and unbelievably comprehensive presentations. Thumbs up!
Excel is the king...as you have power query to get and transform, power pivot for establishing relationships, power view for interactive dashboards and power map for 3d maps...plus formulas and pivot tables..excel is really versatile..
❤️❤️❤️❤️❤️❤️🤲🤲🤲🤲🤲🤲🤲🤲 Thank you very much. From 🇵🇰 It clears me lot of ambiguities about when to use power query, power pivot and measures. You're awesome teacher. Great video. ❤️❤️❤️❤️❤️
Awesome video! I just watched all 2 hours and will go back and watch 1-14 and the others. I am almost done studying to take 70-779 & 70-778 for my MCSA in Bi Reporting. New subscriber!
That's a fantastic video. It took me over six hours to watch it all. I kept stopping the video to practice the tasks and test alternatives. I learned so much. Thank you very much.
A hint for others who aren't from the USA either. When loading the .csv files you have to enter the country code at the command Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table. Otherwise I got wrong data. How this works is explained in video number 12.
That is my intent: Make a comprehensive video on this topic that has everything in one video and is for those learners who wants to know the full story, including all the whys. Your approach is perfect: 1) Download all files, 2) Watch Video, 3) Pause, 4) Practice what you see, including alternatives (like you said), 5) Un Pause, 6) Watch More, 7) Practice More, 80 Take a long time to learn because this is such valuable and life/career changing info. Thank you for the tip for non-USA viewers. Yes, Video #12 shows how to deal with international dates: th-cam.com/video/JGppsnepJFo/w-d-xo.html
Glad it helps you, Frank! You can help support my efforts in delivering free education like this with comments and thumbs ups on each video that you learn from : )
Frank I go over a few times and always find something new to learn. Mike is best. Works really hard to prepare detailed examples and notes.. May this to him
@@sanjeevsoni6793 Thanks for the kind words, and thanks to both of you for supporting on the videos that you learn from : )
/_///////////ๅ///////_/////ๅๅ/_//
I have never seen better excel teacher in my life than you
Thank you very much
you are doing very good work for humanity sir
Yes!!! That has been my goal for 15 years here at TH-cam: free Excel and Power BI education for the world!!! Glad you like it : )
WOW! This really is an EPIC GOLDEN Video! So much valuable information packed in one place. I love the overview and comparisons. Thank you so much Teammate Mike.
You are welcome so much, Teammate Leila!!! Thanks for the Epic and Golden words ; )
I came here because of you.
@@excelisfun mmmmm
M
We pay more for less in schools, but you give MORE for NOTHING, what a wonderful blessing you are to the excel world.
I wonder if you know how many people call you #MENTOR. If you can imagine the number, I am one of them. I draw so much inspiration from you that even my students wish to see you, talk less of me.Thanks for this epic video and great lesson.
It is TRUE that I have been trying to give away fun education for 10 years here at TH-cam! I am so glad that it helps you and your students. You can help, though, and really there is a fee for you to use these resources: leave a comment and Click the Thumbs Up on each video that you watch and Sub : ) Thanks, THE REFORM I.T. CONSULT
!!!!
@@excelisfun Thanks for permitting me to use your resources. They are or greater value to me than you can ever imagine. If any one asked me the reason why I teach Excel today, I would simply point to your channel. Its has built me to a point where you have become the center in my TRIBE OF MENTORS
You are a true genius and hero
I will always support your channel the best way I can even though I always feel it's never enough for such great value I reap from your channel
@@reanalytics1863 , I will keep posting videos, Excel files and free written material for you and others around the world!!!
I do agree too 👍👍👍👍👍
गौरवं प्राप्यते दानात् न तु वित्तस्य सञ्चयात् ।
स्थितिरुच्चैः पयोदानां पयोधीनामधः स्थितिः ॥
- सुभाषितरत्नसमुच्चय
Transliteration:
gauravaṃ prāpyate dānāt na tu vittasya sañcayāt ।
sthitiruccaiḥ payodānāṃ payodhīnāmadhaḥ sthitiḥ ॥
- subhāṣitaratnasamuccaya
Meaning of the subhAShita:
Respectability comes from giving and not collecting. Clouds (attain) a higher position and the oceans, a lower one.
Commentary:
What kinds of teachings has God not given us? He has left us hints to all kinds of essentials in nature itself. Even the code of conduct is given to us in nature!
Naturally, clouds exist high up in the sky and the oceans are down below. According to the poet, anyone who 'gives', attains higher respect than the one who 'collects' all for himself. The clouds give away all the water they contain and they disintegrate themselves in due course. Once the rain falls they, (clouds) cease to exist. Whereas, the oceans collect all the water that comes their way (even turning the freshwater salty). That is why they haven't reached heights!
The essence here is: giving has more value than accumulating or pooling. It could be money, resources, knowledge, wealth or anything of the like. Sharing our knowledge like exce, power query, power pivot, MSBI, etc., by respected Mike is resources and wealth endowment for future generations with our fellow beings brings a higher permanence to the giver, Mr.Mike, as well as, what he is giving to mankind. For example, if one shares knowledge with others, that knowledge gets passed down for generations to come and will live forever. If one shares his wealth, he not only benefits the receiver, but he instils the spirit of giving in himself too. Greed and stinginess have never been approved in any culture.
Giving is always an honorable act.
pada vigrahaH:
गौरवं प्राप्यते दानात् न तु वित्तस्य सञ्चयात् ।
gauravaṃ prāpyate dānāt na tu vittasya sañcayāt ।
स्थितिः उच्चैः पयोदानां पयोधीनां अधः स्थितिः ॥
sthitiḥ uccaiḥ payodānāṃ payodhīnāṃ adhaḥ sthitiḥ ॥ For more such precious pearls of great grand mega knowledge warehouse in India's in fact, world's oldest mother of all languages, Sanskrit, visit the blog :sanskritpearls.blogspot.com/search/label/%E0%A4%97%20%E0%A4%98
This is the Best of the Best among Pivot Table and DAX training. I spent whole week to watch all videos twice to get the most out of it. Thanks a lot. Sandra
You are welcome a lot, Sandra : )
The most comprehensive, insightful video on Power Pivot and DAX. Thank you for providing this knowledge for free.
You are just amazing!!! Thank you from Italy!!!!!!!!I stumbled across your channel 1 week ago....I have been watching your content all day long since then!!! Thank you for the priceless knowledge you share!!!!!!🙏
You are welcome!
Refreshing Series, can't believe i had watched this and i am back again, after this series, DAME Series.
Thanks for this free class as always.
Go Team!!!
Go Team!!!!
Welcome to this this EPIC Video Event!! This is not just a video, but more: there are Excel Files so you can follow along, there are pdf notes with notes about each one of the 40 topics, and there are practice problems (homework problems) so you can practice after studying the video. All these files are available in the links below the video. This Video Event is intended to be a free complete lesson in Power Pivot and DAX. Free education for the world!!!!!!
I really liked your explanation of Context Transition. I have heard about it and read about it in many books and other videos but after watching yours I now finally UNDERSTAND it! Thanks!!!
No words to thank enough the teachers of the world!! With great respect.
@@RobMichaels1 , You are welcome! As I mentioned in another comment, I am the same! i read about and took classes that taught Context Transition and I did not understand. But it is really easy when you visualize it and relive that the row condition just needed to floe back across the Relationship to Filter the table on the other side of the relationship. The visualizations are what helped me. So, Rob, glad it helped!!
@@br31woodlands69 , thanks for the words you wrote! And thanks for your support with comments and thumbs up on the videos that you watch : ) Sub and tell all your friends too!
Yes, as always: a complete coverage on the topic, with lots of practice. Thank you. After your full Power Pivot (DAX) session (from a previous release) this was really about practice and deepening. Thanks again. Oh, and happy Thanksgiving!! (I hope I am not too late.)
It there was a ranking for best instructors covering all topics in all of TH-cam, you would have to be near the top, IMHO. You are amazing.
Glad to help, Bill!!!!
The simplest thing I can say it, it's amazing work from amazing person, thank you so much
I am glad that you like the stuff I post, Mohamed!!!!
I'm from Nigeria and I have to say, what you have done is to bring WORLDCLASS education even to the most remote parts of the world. Thank you so so much Mike. The best part is having to work on the Practice Problems :) You are my hero!!!
I am happy to help you, Temidayo!!!!! It has been my goal at TH-cam for 13 years to try and bring free Excel Education to the world!!!!
Fantastic presentation...it's as if he understands EXACTLY what the student needs to know!!!
Glad it helps!!!
Judging by the comments here, this will be highly redundant but I just want to thank you from the bottom of my heart for doing this! Absolutely amazing resource for anyone trying to learn this stuff.
There is an epic practice problem (fun homework) below the videos in the links!! But it is for after you study the video : )
P.S. I initially did not have the source data files posted as a link, but now they are posted so you can try the homework problem after studying the video : )
Just curious.. @ 1:46:13 why is the % Grand Total 24.985% and not 100%
@@cd8490 Gross Profit is being compared to Total Revenue, Not total Gross Profit. If each Gross Profit Amount was being compared to Total Gross Profit, then you would get a column of percentages that added up to one. 100%.
@@excelisfun Makes sense thank you for clarifying and thanks for all the content I love your videos!
Great Video !!! Is it possible to add columns and rows after the pivot table has been created? I want to be able to manual comments to each row/column. thank you.
Thanks!
Thank you soooooooooo much for your kind donation : ) : ) : ) : ) It really helps, Santosh!
Hi Mike, I am from India (Chennai), this MSPTDA videos are nothing but awesome, thanks a lot for democratizing your knowledge to others across the globe. your workings files / pdf notes all are amazing, once again thanks for your selflessness knowledge enlightenment to all. Kudos to your efforts...
You are welcome, Vinayaga!!!! I am happy that my goal of bringing free Excel and Data Analysis Education to the world for Free is working : )
@vinayagamoorthy128 Can we connect?
Just leave the comment here to prove my strength to go through the whole story :) 2-hour videos but it must take at least 2 days to watch, re-watch, do, re-do homework!
I am working in the biggest fast retail company in an Asian country, and I can say this video must be top 1 recommendation for our fresher to first build a new report :)
Glad it all helps!!!!
It is hard to belive that someone can give so much valuable material for free. I know our way to correspond is liking, commenting and subscribing. I have done all of it apart from recommending your channe. It just can't be better.
THANK YOU SO MUCH!!!
Yes, it is all free! Thank you for doing your part for the Team with your support : ) i am glad that it is useful for you, 1877Pegasus!!!
simply overwhelmed , thanks
You are welcome!!
This is hands-down the best comprehensive guide Power Pivot that I have seen! There are so many valuable terms, concepts, and information packed into one video. What an epic video!
What a truly excellent course! Oftentimes when following along with something like this, you get lost, or something doesn't match what you are seeing, or one of the data files is off. That was not the case with this course. From beginning to end I was able to follow along exactly. A very informative and well-presented tutorial. I learned an enormous amount. Thank you very much indeed. I will for sure be watching more of these.
I am very glad that the class is smooth and you can learn a lot! Yes, the MSPTDA class is quite epic in all of the topics for Data Analysis a& BI using the Mircosoft tools, Power Query, Power Pivot and Power BI ! Thanks for your support on each video with your cthumbs up and comment , and of course your Sub, Franklin : )
I would have to say I really liked the way he presented and tought through out the video.he was quick and quick to the point and skiped the other stuff (going from one point to another).There are soo many boring lecturers (including Udemy), who are very slow to make their point and the way they move their cursor is like snail taking a walk in the park and they are very slow in speach that half way though i lose my concentration.But, this guy is very good and what i like about him is that he is fast and doesn't waste time. For that you have my like and new subscriber.
I am glad that you like what I post for free here at TH-cam for past 11 years, none of that for pay Udemy junk! It is all about editing and telling a story. I depend of viewers like you who will pay for this, but not with cash, but instead with your comment and thumbs ups on each video!!! Thanks for the sub, Nouer!!!
You are the best. Thank you for your teaching us for free. I also follow Leila Gharani and she acknowledges you. Mike Girvin, you are great. God bless you
Yes, Munyaradzi, I am glad the the videos, files and notes that I post for free help you!!!! Thank you for your support with your commnet, thumb up and Sub : )
Before even going through this massive learning journey, I would not imagine we would be having such introductory else where. 2hrs of hands-on , man, that’s a lot of fun indeed.
The fact you are even providing homework, make learning curve boosts in a great deal as we would be able to apply and grasp the tips at our own pace.
Thanks Mike for sharing your expertise.
You are welcome, Ahmed! Yes, with homework and detailed video and pdf notes that summarize videos - you can learn and have fun! Thanks you for giving back with your comment, Thumbs Up, Sub and telling all your friends : )
I watched your videos back in 2012 when i first tried to understand how excel works. I still remember your intonations and emphasis on certain words, and the way you segue to explain nuances of the operation. Theses small things that made all the difference. Learning Excel through your tutorials allowed me to drive our organization by learning to extract, clean and work with data. The organization 10X in size since then. Now im back trying to learn power query-pivot-and eventually power bi and just so i know how to ask the right questions, and this video gives me the same aha moments i used to get before. thanks so much!
I am happy to hear that the nuances of how I create the stories about Excel to help complicated things seem less complicated works for you Easy PC!!! Thanks for your support : )
Epic is the proper adjective to apply to this video. I learn so much from this TH-cam channel, I am a better professional and an Excel and Power BI ace at my company thanks to Mike Girvin.
Every time I watch your video then I become a changed person. Thanks for your excellent teaching, I learned a lot from you. You are my mentor for Excel!
I am glad that I can help with some positive change, Shahin!!!!
Excellent Video!! You Sir are my EXCEL GURU. Thank you very much Professor Mike.
You are welcome, Sivakumar!!!
Mike i have not watch the video yet and just see the notification when get up in the morning which brought happiness in my day...thank you Mike!
Happiness in your day is great! And you get to have fun learning with Power Query, Power Pivot, DAX and Data Modeling. Thank you for your support, ZAHID !
You are such a genius. Thanks Mike for this fabulous video.👍👍👍
Not so much a genius, as just a giy having fun with Excel and Power BI ; ) I am glad that the lessons that I post help, Kebin!!!!
I don't know how to express my appreciation to you.
You are God!
Keep doing great and safe!
You expressed it just fine! For each video you learn from, just click thumbs up, leave a comment if you want, and tell all your friends about excelisfun : )
Best Two hours viewing I have spent on my annual leave Re MSPTDA 15. Regards Andrew
Yes, this two hours should take more like 10 hours to get the full fun : ) Glad that you liked it, Andrew : )
This is the best overview I have seen yet. Thanks for putting so much into one video with practice data and with links to make reviewing easy. Your videos are the greatest. Thanks.
Glad all the varied resources that I have posted for this "Video Event" are helpful! Thanks for your support, as always, Rob : )
Professor Mike, this is an EPIC VIDEO! I stop watching on laptop and started watching it on my television. Thank you so much for taking your time to explain everything! YOU SIR ARE A GOD SEND! I am so thankful YOU know that EXCELISFUN and LOVE sharing your knowledge with us! I can't wait to do the homework tomorrow morning after church!
You are welcome for the television fun, Arree!!! You KNOW that are are having fun learning when you can't wait to do the homework : ) : ) : ) : )
You are my favourite youtuber..... I u literally my teacher in excel..... have learnt everything from your videos..... best past is your free excess on useful sheets......
Thanks
Love from Kashmir........
Yes, that is what I have been doing for over 12 years at TH-cam: free videos, notes and files. Thanks for helping me to continue to bring free Excel Education to the world with your comments and thumbs ups on each video that you learn from, Mehboob!
It might be an introduction as well as summary for those who already tryied to work with PP or PBI. Your work is highly appreciate. More youtubers like you please. Thanks!
Yes, that is a really good point, Aron!!! It really is an Intro AND a Summary. Even for me, where I have used Power Pivot and DAX for over 5 years, making this video required that I remember and organize all the tips in a sort of helpful summary. Thanks for your support, Aron!!!!
Thank you, Sir, for the splendid work you put in here
You are welcome!!!
If this world is fair, TH-cam should create a prise to appreciate TH-cam channel owner Like you, FREE amazing and useful learning, Thank you again and again
The TH-cam world is not fair. I hate to say it, but TH-cam does not value channels like this, that have long videos that tell the complete story. But I post these video anyway, because I know that there are some viewers like you that find value!!! So thanks, Basel : ) : ) : )
I know my life will be changing very soon just because of you MIKE!!.......Lots of Love chuck..
Thank you very much, you are a great teacher!
You are welcome very much!!!
Timestamp 1:26:10 "what in the world?".....gets me every time! Huge thanks Mike. 2023, let the Fun roll on.😎
Yes!!!!! Let the fun roll on : ) : ) Got have lots of fun like at 01:26:10 ; )
Can’t believe that I just sat through 2 hours without any breaks.
WOWOWOW! Thanks!
Thank you ExcellsFun!! I learned excel from you and I want to learn Power BI and DAX. I have gone through so many channels and finally I came back to you guys. I really appreciate the effort you put in. Thank you again!!
This MSPTDA series is pure gold
That's Perfect .... when you talk about reporting in Excel .... I just listen and learn .... thanks Mike
You are welcome, Hussein! Glad it all helps and thanks for the support : )
It's worth going over this video over and over... covers everything from start to end. Super..
This man is legendary
Always glad to help!!!!
Oh my god what an Event!! Massive knowledge beautifully gathered together in a video that could easily be a semester course! I had to watch the video in "episodes" in order to be able to understand each topic and its usage. I still need many more views and lots of homework to fully integrate all the mentioned concepts. Indeed, not only most introductory videos don't teach such valuable knowledge but also NO-where in TH-cam you will find this kind of teaching! Highly qualified teaching!! Thank you so so much Mike! You give enormous value to the world not only as an instructor but also as an influential person! Your selfless "free education to the world" attitude is awe-inspiring!! Thank you :)
Thank you so much, Evgenia M, for your kind words : ) : ) I do try to put most all the information into one video so you and the other Teammates can have one-stop-shopping!!!!
And you have succeded it to the maximum on every video of yours! Compact and yet detailed teaching worthy of a million thanks that is never enough! I will never stop being grateful to you! :D
Been working through your videos the past few weeks and it's been a great experience. Please accept my gratitude for this awesome education you have provided and for free...you are an amazing individual. I honestly feel that I should contribute to your cause in some way and happy to do so.
Yes, you can contribute simply by clicking thumbs up and comment on every video that you watch : ) Go Team!!!!!!
Never seen better courses than yours, I'm from Madagascar an I want to thank you
Truly the most incredible series.
Glad you like the classes, Spencer!!!
Mike You are Really a Genius Man!! Have no word to express my gratitude about what a detailed information you are delivering for NOTHING.Would request few more videos on M code. God bless you!!
I have more videos about M Code. Did you watch this class from the beginning? Here is the full playlist: th-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html and here is a 2 minute video about how to find just what you want at the excelisfun channel: th-cam.com/video/l1-1aVgFth4/w-d-xo.html
It is easy to express gratitude: all I ask in exchange for this fun and free education: a comment and thumbs up on each video that you learn from : )
@@excelisfun3903 Mike I always give a thums UP for each video I watch. I will follow your guidence on M code playlist that you have shared. Thanks for replying on my queries👍
@@sanjeevgupta7177 Thanks : ) : ) : ) : )
Wow! I feel my current Excel knowledge is at Caveman level after watching this video.
Great great video like always. Everytime I have to learn something new from Excel I come to your channel and follow your step by step guides. I have been subscribed for about 5 years now. Thank you very much
You are welcome very much, Alexander!!!
This video resolved many issues I encountered in my reports.
I view it again and again every time I face difficulty sure that I will find the resolution.
Really super
Glad it helps resolve many issues. Thanks for watching many times, commenting and doing that Thumbs Up and Sub : ) Many more videos to come, Imad!
OMG, This video is Mammoth task as compared to other videos. All 39 topic were important in learning DAX. "Thanks Mike" for putting your valuable two hours time in creating this introduction video. This wonderfull video will surely help all the learners out their. In addition to above you have also included supporting data for practices tats wonderful. I am Learning MSPTDA simultaneously with your E-DAB video. Learning never ends with you.😊👍
What? It took 128 hours to make! No human anywhere could create this in 2 hours... I am glad that the E-DAB and MSPTDA help you. That is why i post. Please help with your support on each video with Thumbs Up and Comment : ) Thanks, Santash.
This is an awesome video！It is so clearly instructed and easy to follow with the right pace. As someone who is not that familiar with power pivot, I almost get the points you are making without having to pause or look back. It answered so many question I have and saved me tons of time from googling around. Thank you and good job on it！
You are welcome, Sarah!!
Huge pack of information about power pivot and dax and a lot of related features...
Fantastic !!!
Thanks a lot for this useful and helpful video
You are welcome for the full length movie about Power Pivot and DAX!!!!
Thank you...a lot of information to take in and absorb, I will come back to watch this video again many times to remind myself how to do these... Mike - you are the greatest!
Yes, the intention of this video is to have many of the important tips for DAX and Data Model PivotTables in one place. The Video Event of course has the Excel files, pdf notes and the practice problem so help us all learn the power and fun of Power Pivot and DAX! Thanks for the support, rf05mjy : )
Saying thank you is not enough. I am so grateful for your work. :)
It is sufficient to leave a comment and thumbs up on each video like you do. Thank you for your always kind words.
While watching this video, every time I had a question in mind, the very next moment you would clarify that!
Perfect compilation and totally relatable to the business problems in analytics!
You are awesome.
Same situation here....indeed Mike is awesome
I have watched Marco & Alberto, but could not wrap my head around filter context. You made it so clear. Thanks!
I am glad to hear that the resources that I have posted help. It gets even better in video #19 in this MSPTDA class, that video shows all about Filter Context. Check it out and let me know if that helps : )
As always Mike invaluable videos on any topic from Excel, Power Query, Power Pivot & Power BI are always packed with great knowledge and you can always decipher the underlying passion from him when teaching. Have really learned so much from your videos since I when I started the Excel learning journey few years back. Inspirational as always Thanks
You are welcome!
Context Transition is really well explained; Great coverup including CALCULATETABLE and bringing Table back to Excel Grid. Enjoyed
Yes, I never understood Context Transition with the normal description of "Convert Row Context To Filter Context...", it was not until I actually visualized that we needed the criteria in each row to become a filter that needs to flow through the Relationship (just like normal PivotTable Filter Context) and filter the Fact Table. Pictures helped me to understand, so I am glad the words I chose and the pictures I showed in the video helped you too : ) Glad you enjoyed it, Sanjeev!!!
Hi Mike..I am a constant follower of your videos. This one is EPIC !!! You make concepts so clear. Thank you so much for this learning.
Yes, I have to make "concepts so clear" because I am not that smart and in order for me to understand difficult concepts like DAX, I have to break it down into simplistic terms and then try and organize the terms, concepts and ideas into an easy to understand story. If I did not spend the 100s of hours doing this, I would never understand it. So really, what makes me an adequate teacher steams from the fact that i am not that smart : )
I know that you have watched 100s of my videos. I REALLY appreciate that you leave a comment on each video and Thumbs Up each video that you watch. You and a few others are the exception to the rule. Most humans just watch, and take the free knowledge without giving back. Further, I do not understand why this is so, given that the videos are often among the most clear and concise videos on a given topic anywhere in the world. Still further, I do not understand why a resource like this, which is accurate and easy to follow AND Free, does not get more views... I have been giving away free education for over 10 years here at TH-cam. And in fact, what I post can really be thought of as "Video Events" that have a video, Excel Files, pdf notes and practice files. My hope for over a decade is that what I post here at TH-cam would reach more people who need free education. But very few of the over 3,000 videos that I have posted have ever reached a lot of people. This video, for example, a comprehensive Power Pivot and DAX video, has 6,000 views, when the actual demand for this sort of knowledge is so much greater...
Love the video.
I found the functions in Dax Studio to export the query onto a new table in your current workbook with or without a connection to be very useful. Also you can have it override an existing table; however if your using an DMV function to analyze the data model you need to use a new table.
I personally use this at work as a replacement to SQLs as I load the data to the data model and based on the users choices through a single cell table connected to the data model, it loads the table in conjunction with my dashboard.
This solves the issue of being able to drill down to the individual transaction. This create similar functionality to Power Bi, with its ability to export the raw data from its dashboard.
Thanks again I learned far more than I expected.
Wait a second... You wrote: "I found the functions in Dax Studio to export the query onto a new table in your current workbook with or without a connection to be very useful" Do you mean that there is a button in DAX Studio that can send the query to the Excel Sheet!?!?!?! Can you please tell me where that button is? Or did you use the verb "export" loosely and you were referring to the copy and past action that i showed?
Yes, knowing how to get data from Excel Model to sheet was one of the first tricks I learned a number of years back that allowed me to check and start to believe the DAX and Power Pivot system. Before being able to do that, as an Excel guy who always wanted to "see' and double check, it was hard to get fully behind it...
You wrote: "I personally use this at work as a replacement to SQLs as I load the data to the data model and based on the users choices through a single cell table connected to the data model, it loads the table in conjunction with my dashboard." What specific aspect of the video is a replacement for SQL? Also, you said that you have a single cell table, do you access that single cell table in DAX with the VALUES function?
Thanks for watching, Kris, and thanks for your great comments : )Thanks for your support!
@ExcelIsFun Single Cell:
I use the ALLSELECTED within the CALCULATETABLE function to access the linked table in a similar way to how you linked a single cell table to Power Query to affect a query filter; however, in this case, I used it to affect the filter function in CALCULATETABLE.
EVALUATE
ADDCOLUMNS(CALCULATETABLE(Usage,Usage[DistWh]=ALLSELECTED(Wh_Selection[Dist Wh])),
"Item Name",RELATED(NSN[Item Name]),
"Class",RELATED(NSN[Class]),
"Inv Seg",RELATED(NSN[Inventory Segment Code]),
"SIO",RELATED(NSN[Stock Item Owner]),
"Stock Type",RELATED(NSN[Stock Type]),
"Warehouse",RELATED(Warehouse[Warehouse Description]))
As to the SQL, I load big data from my massive database at work to the data model and using the above method, my collogues can access the data in tabular format without a database management system/SQL and can use it off-site when conducting audits. Using VBA I have massively expanded upon this to include many DAX queries based on a selection( using a user form); however, the only VBA you need is to refresh the data model connections only. (Refresh all will not work as it tries to refresh the queries as well):
Sub RefreshDM()
ActiveWorkbook.Connections("WorksheetConnection_SHP lite.xlsx!Wh_Selection"). _
Refresh
End Sub
As to the export form Dax studio: Click on the down arrow under output and you have many options on how you want to export the data and time the query. (I use the timer to assess if I can optimize it).
If it is not on your version, you might need to update it.
I use DMV to assess my Schema; however, do not seem to export to an existing table. Have to use a new one.
Another trick is to click anywhere on a new sheet and insert a table.
-Then add the table to the data model.
-Copy the table and past it next to the original table.
-Edit the DAX query.
-Delete the original table.
-From there you can add both DAX and DMV queries.
Hope this helped.
Data mining and dashboards is now my full-time job for a company of 24,000+ employees and over 100 warehouses. I got this job after learning of Power Query through your channel. Before that I just did VBA and SharePoint design (Low level), now I’m the company manager (For data Mining) at the executive level. So a very big thanks from me.
I have gone through most of your MSPTDA lessons these past few days, and many more of your incredible videos. Just wanted to leave a comment to show my gratitude and appreciation of what you do. You are an incredible person and a gifted teacher. Thank you so much for everything. I feel much more confident in my Excel abilities, and hopefully I can become an Excel master like yourself in the future ;-)
You will become an Excel Master, Fredrik!!!! After gaining skills fro the free resource that i post, please help support on each video with a thumbs up, comment and of course your Sub : )
Great vedio ❤️...love the dax studio, calculate , sumx function 🤟😊
Glad you love it!!!!! : ) : )
Thank you for your lessons and the way of your teaching. I start from knowing some but now I think that I can teach the essential fundamentals in Thai to share my understanding with others. More time I've spent on your video, more delopment I've got.
That is why I post, Surinee! Be sure to help out my goal of bringing free education to the world, especially to educators like you, with a comment and thumbs up on every video that you watch and of course your Sub : )
Only 10 minutes into the video and I can already tell this is gonna be epic. Thanks so much, Mike!!
Yes, Epic and for those who really want to know the full story!!!
Wow.!! That was great...It's like watching Die Hard every Christmas...You know how it ends but it's such a great video, you can watch it over and over again! I will definately be watching this one again. Thanks Mike.
You are welcome! I did put as much knowledge in one place, so you know where to go when you wanted the Basics of Power Pivot and DAX formulas!! Glad it helped, Matt! Thanks for your Die Hard support : )
Excellent- Liked the way of explaining the concept and usage of DAX formula - Pls keep posting !!!
You are welcome, Manoj!!! I will keep posting if viewers like you support with your comments, Thumbs Ups and Subs!! That way I know you like it and want more : ) If there is the support, I will keep posting in this series for about six more months, and that means a LOT more fun with DAX Formula!
@@excelisfun Oh Mike! My Goodness, you read and replied so quickly knowing you are so busy ! Thanks and you really deserve a big thumbs up! Your Narrative style and explanation on the subject is so amazing that i never saw. In fact I saw this video 4 times and just could not sleep for 2 nights just thinking of amazing fast dashboard creation until I created a dashboard and now tomorrow showing the same to my boss. Thanks a lot! Only one difficulty I faced , I was to pull data through Organization API which is authenticated through Access token ( Oauth) but was not successful. Ultimately I used Postman to get data in CSV file and the n use it. But I want to pull data autoamtically through Powere query. Can you help which option I can use and pass access token for thsi secured API?
Thank you so much for this wonderful video. You're a STAR.
I am glad to help, Pradeep!!!!
Mike, I owe you, man, you are the best indeed. Thank you!
I advise all my pears to watch your videos and take it like classes. Thank you so much!
You do own me: but not any money : ) Just thumbs up and comment on each video : ) Thank you very much for telling all your peers, and I am glad that the videos and resources help you, Rodrigo : )
I always asked myself: how to create a customized pivot table?. After watching your video, you gave me the answer to that question. Thanks for everything!
Couldn't help it....but to comment again to thank you once again for the video. Downloaded all and having fun trying all sorts of new DAX to learn the Nuts and Bolts. Thanks Mike. Can't wait for what will come next. :)
Thanks very much for the new comment. Always comment as much as you want : ) It helps support the channel. Yes, this video has a LOT in it so it takes some time to absorb. I have been working on the next one for weeks. It should be out in a few days here ...
I took some days off work :) I have all this week to play about. Hope that the next one will be out by the end of this week. :) God bless you Mike!!
@@johnborg6005 I hope so too - but I never know because this class just is taking me so long to create. With all the pdf notes, Excel data Sets, Excel workbooks, Video filming, video editing and so on, it just is unpredictable how long it will take. For example, video #9 took over 200 hours, last video #15 took 128 hours and I am already 120 hours into this video... And the funny thing is that the pdf notes took longer to make for this video and #9 than the time it took to make the videos...
Just take your time as long as it will come out in the end. :) I will run this one again and again, and some of the old ones. Thanks Mike :)
Hi Mike, You're my guru from today itself. I found your teaching methodology so easy, crisp and powerful, specially with those explanatory graphics. It helps in visualizing how the things are working behind the scene. You won't believe, but I have watched over 10 hours of videos from your channel in just last 2 days. Thank you for your time and efforts in creating these videos. Merry Christmas & a Happy New Year! :)
I am so glad that the materials that I post help and that the visualizations help you to understand. I know for me, if I did not visualize DAX, I could never understand it. As I help provide you and other free education, you can help support what I do with comments and thumbs ups on every video that you learn from. Thanks, Chanda : )
I can't really find proper words to appreciate your work mate. Simply an amazing human being.
But, unfortunately, there are asshole people who bold enough to "dislike" the contents that u put. These are people who dont do a thing for others but still find mistakes of the people who do good...
Anyways, thank you very much again for your amazing work and support....
Thank you very much for trying to find the words to say thanks, Asantha : ) I try to make the world a better place by providing these free resources, and so your thanks is appreciated. I also appreciate that you hit that Thumbs Up and leave comments on each vid - that is enough thanks for me : )
भवन्ति नम्रास्तरवः फलोद्गमैः
नवाम्बुभिर्भूरिविलम्बिनो घनाः ।
अनुद्धताः सत्पुरुषाः समृद्धिभिः
स्वभाव एवैष परोपकारिणाम् ॥
- नीतिशतक
Transliteration:
bhavanti namrāstaravaḥ phalodgamaiḥ
navāmbubhirbhūrivilambino ghanāḥ ।
anuddhatāḥ satpuruṣāḥ samṛddhibhiḥ
svabhāva evaiṣa paropakāriṇām ॥
- nītiśataka
Meaning of the subhAShita:
Trees bow down when fruit are shooting forth; clouds hang low when filled with water; the noble become humble in opulence - this is the very nature of the benevolent.
Commentary:
"If you wish to have happiness, provide happiness for others" - this is probably the motto of the trees, clouds and the noble! They attain their internal peace and calm through their acts of benevolence towards others. They are not just affable, they are humble too! Humility is their prime attribute.
When a tree is full of fruit, it doesn't stiffen itself straight in arrogance that it is providing fruit to everyone. It bends down in humility as if to make it easier for everyone to access its fruit!
When clouds are brimming with water, they hang low as if in diffidence, so they can shower their cool waters at the earliest to quench the thirst of the earth!
The noble when bestowed with opulence, display utmost humility. They do not have even the slightest of arrogance about any of their resources or boons. They display utmost modesty and believe that humility is a lifelong lesson. They do not slack from their humility at any time! It is a natural trait that comes spontaneously to them at all times. Clearly, their stance is that 'humility is the cornerstone of all other virtues'. Hence, they never take a respite from this wonderful attribute.
Those that do good to others never take pride in themselves. Pride makes us artificial, humility makes us real. Let's be real : For more such precious pearls of wisdom enshrined in the mother of all languages, i.e. the ancient Sanskrit language, as a great grand mega warehouse of Knowledge, visit the blog : sanskritpearls.blogspot.com/search/label/%E0%A4%AC%20%E0%A4%AD
I can't find the words to describe how amazing and powerful this video was! It was as powerful as Power Tools! :) I have watched a lot of your videos, many of them were epic, but this one blew my mind! Thanks a lot!
Yes!!!! I love to hear that it was powerful for you!!! Just keep going, MSPTDA #16-19 are big Heavy hitters like this one too : ) Thanks as always for stopping by in the comments, ljubicar1987!!!
Great video Mike - going through it again! I like the epic videos as you have all info one place and often takes the whole day to work through. I pause the video and try and do the task or write the DAX formula before you then go through and check my workings!
This is a good video to return to in understanding how Power Query works in the background.
Really like the iterator functions (SUMX and AVERAGEX) it can help keep the Applied Steps area nice and clean while still creating your measures.
I am glad that this MSPTDA video helps, Darren! If you like Iterators, have you seen #18 MSPTDA, all about iterators? Here is is: th-cam.com/video/_DHeTnDa2Q4/w-d-xo.html Please help support my efforts to provide free education to the world, and if you learn from a video that you watch, click that thumbs up and comment, and of course your Sub : )
You are awesome.. I suggested your channel to many of my friends... ❤️❤️
Great tutorial! You are the best I've seen on TH-cam!! God bless
Thank you Sir.
You are welcome, NC nc!!!!! Glad that the video event is helpful. Thanks for your support : ) : )
Just completed the video. I did not know anything about DAX, but I know quite a bit now. Thanks Bro!
This is actually a deep dive in to data modeling and dax not in power bi but in excel 👍👏
Umbelievable videos very nice No Comment many topics and tricks has been not clear for me and now i see very clear...1000000000000000000000000000000 thankssssssssssssssssssssssssssssssssss
You are 1000000000000000000000000000000000000000000 welcomes!!!! i am so glad that eveything is good for you : ) : ) : )
Sure it is, I am using Excel from 3 to 4 hours per day, so every video helps me to refresh my khnowledge or to leean something new or to give me ideas to nest formulas, so I save more and more time, thats why every things is good for me :-) I recognze that without your videos my level has to be still beginner.
Great Overview of DAX! I am working hard to improve my MTDA skills--so greatly appreciated!! My schedule required that I watch it over several days in 30 min segments--so just finished. I finally gave up my perpetual and got 365 on 11/14! Signed up for Insider and 2 days later Dynamic Arrays were available to me. Took Dynamic Arrays for a hard test drive. Great tools, but took lots of notes for MS.
I love to hear that you got the 365, Richard Hay!!!!! I am also glad to hear that you take your time and study the videos in the MSPTDA class, because these videos are dense : ) I trust that you download the Excel files, read the pdf notes and try the practice (homework) problems also? Thank you for supporting this free resource, Richard, with your comments and Thumbs Up on each video that you watch : )
I can't believe this is free! I'm definitely buying your mugs and Tshirts. Thanks a bunch!
Personally, National Treasure was my favorite but this "Slicer" film may be a close second. Nicholas Cage takes us for a ride in this Power Pivot/Dax Indy masterpiece; Great video. While Cage moves fast he is very easy to follow and covers so much ground, sequentially building upon each step, all ways explaining "why". No "Cage Rage" here, but plenty of tricks.
You go it!!! AND, if you use the rewind button, then the fast is not so bad. Did you watch the 14 videos in the class before this #15 video?
YOU ARE GREAT TEACHER. ❤️
I am glad that my videos help you : )
Awesome! To quote my Excel hero, "Wow THAT was an EPIC Video!" I finally got to sample DAX Studio in a useful way per your good example. Always illuminating, always FUN!
Glad it was helpful and illuminating, Peter! Thanks for your support : )
@@excelisfun I see you have an EMT video #1433 that covers DAX Studio. I'll check that out too. Thanks
Awesome video. Thanks for the steps to backdoor into Power Pivot on older versions of Excel. We still use 2013 on some of our machines.
You are welcome!
Sir you literally changed my life :D
That is why I post here at TH-cam and not at a pay site. Trying to bring Excel and Power BI to the world!!!! Glad all the resources help, marko! Thank you for your support on each video with your thumbs Ups, Comments and of course your Sub : )
Fully agree
Thanks for making those tremendously useful videos!! Watching your videos has become my daily routine🤣 so excited to learn more content from you!!
You are welcome, Jerry! Do you do the homework and read the notes for full fun and knowledge?
@@excelisfun Yes! the homework and the note hepled me review what I learned from the video! Also, today my Boss just asked me clean the data which has hundreds of thousands of rows in EXCEL,and fortunately I used power query tought by you to do that which saved me a lot of time!! Thank you again for keeping making those practical videos!!
@@hgocers1230z You are welcome and thanks for the support with those thumbs ups : )
Hi Mike.. this looks intense. Looking forward to carving out some time to work through it. As always, thanks for your incredible videos and unbelievably comprehensive presentations. Thumbs up!
Yes, it is intense! But good intense : ) Thanks for your support, Wayne!
Excel is the king...as you have power query to get and transform, power pivot for establishing relationships, power view for interactive dashboards and power map for 3d maps...plus formulas and pivot tables..excel is really versatile..
❤️❤️❤️❤️❤️❤️🤲🤲🤲🤲🤲🤲🤲🤲
Thank you very much. From 🇵🇰
It clears me lot of ambiguities about when to use power query, power pivot and measures. You're awesome teacher. Great video. ❤️❤️❤️❤️❤️
You are welcome very much, Asif!!! Thanks for all the love!!!
Awesome video! I just watched all 2 hours and will go back and watch 1-14 and the others. I am almost done studying to take 70-779 & 70-778 for my MCSA in Bi Reporting. New subscriber!
Thanks for the new sub and support of this excelisfun channel at TH-cam : ) Let me know how your test goes, Tom R!
What an amazing video!! Thanks a lot Mike. Respect!!
You are welcome, Santiago!!!