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: ua-cam.com/video/JGppsnepJFo/v-deo.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.
another advantage is that Excel can do incremental / "recursive" Power Query runs -- that is, data captured in Excel from prior PQ runs can inform and condition the current PQ run. A simple example is to store the PQ refresh date & time in Excel, which PQ in turn uses to delimit its refresh to only new files, and combine prior data with the new. Works well, for instance, if you want to decode data from thousands of live Excel files stored in SharePoint.
When you're doing the cumulative ytd(1:12:50), it shows the grand total as the last yearly total in the list. How do you fix that to be the actual grand total for all the years?
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 UA-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
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.
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.)
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.
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!!!
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!!!!
That is why I post here at UA-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 : )
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 !
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
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 : )
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!!!
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!
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!!!
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!!!!!!🙏
Thank you very much (ou como dizemos em Portugal Muito Obrigado) I'm begining now with Power Query and you are to excellent. I'm learning a lot with 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...
wow. this is 2 hours of free goodness easily charged elsewhere.it'll take my channel awhile to share about fundamentals before i can touch about PowerQuery, PowerPivot, and PowerBI
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 :)
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.
Hi Mr. Mike I would like to to watch this video more then 10 times it's really astonishing thank you so much Mr. Mike I really want to know if I can store any data in the database and use it in search or anything i want in excel sheet.
10 * 2 Hours = 20 hours of great learning fun : ) Yes, you can get data from data Model, as seen in the video, but it is not as easy as a Query in a database, like Access... However, I do have a video coming up in this series that will show how to enter data into a cell in an Excel Sheet and then run a DAX Query against it to extract data. Thta video is coming soon : ) Thanks for your support, Ismail!!!
It will take all day to watch, try, watch some more, rewind, watch again, try some more, read pdf notes, try home work, then start over for a rewind of the fun and learning : )
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 are welcome "Lots of Cs". Thank you for your donation: it definitely helps me to keep making videos. When you make donations, we are like a Team. Thanks, CCC ccc!!! Go team : )
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?
Mike , pls help me with Power query separate playlist and Also Power Pivot separate playlist. There are playlist but it's not sequential or I might be able to find the right one. Pls can you help me out
The Power Query and Power Pivot Playlist are not classes with sequential videos. For the Full class on Power Query, Power Pivot, DAX and More (the MSPTDA Class) here is the Playlist: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html (The MSPTDA class is ongoing, though. I will keep posting videos for the next 6 months. It will not have the full 40 + videos until Spring 2019 : )
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.
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 UA-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 UA-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...
Wow Mike, that’s another EPIC video in this wonderful series. And all these videos complement each other so nicely! I like this very much and I compliment you with the results! :-) Now, I’m only 9:50 into the video as I write this, so: back to the video...
Done. Mike, people argue that the data model can be used to efficiently store large amounts of data -- as opposed to txt- or csv-files. The only drawback is that you can't "see" all the data at once if you want to. Now, it looks like that cube function is a way to dump data from the data model "back" into Excel. And if the tables are sufficiently small, than it can be done. Do you agree?
@@GeertDelmulle , yes, the drawback is that you cannot see it... But even in Excel or text files, if you have 100,000, you never really "see" it all...
@@GeertDelmulle , it is not the CUBE functions that allow you to put data in the sheet, it is DAX Table Functions that we can use in Edit DAX in the Existing Connections trick.
Mike, you're right: I mixed it up with that CUBEMEMBER-formula that you used to make the title of your dashboard. It is, as you said, the DAX table-function that does it. It's always good to have a an index with active time markers in the video description for quick reference. :-)
It is all stuff that I have video blogged about before in other videos, but in this one video I tried to put it all together in a story that could be watched in one place - I also tried to make it an Epic Video Event with pdf notes, Excel workbook and practice problems!!! Thank you for your support, NoShadowOfDoubt - please tell all your friends and spread the word about thsi fun Video Event : )
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 : )
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!!
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.
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...
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..
Revision Topic 37. (01:52:20) How to Double Check that a DAX Formula is yielding the correct answer? 38. (01:53:22) DAX Table Functions. See CALCULATETABLE DAX Function. 39. (01:55:07) DAX Studio to visualize DAX Table Functions, and to efficiently create DAX Formulas 40. (02:00:12) Existing Connections to import data from Data Model into an Excel Sheet
The sheet Topics in workbook 015-MSPTDA-ComprehensiveIntroPowerPivot.xlsx only has 19 topics, not 39 (not that it matters much) I guess they have been overlooked somehow 😢
Dear Mr Mike When I tried to load the CSV files (in the folder 015-TextFiles) in Power Query, I am getting errors in around 87k rows. I have managed to create a table of these 87k rows. I request you to guide what could have possibly gone wrong.
Dear Mr Mike Further to my request of today, kindly further note that I am facing some problem with CSV of Sales2017.csv. When I excluded the said file from loading, I did not get any error. When I opened that CSV directly to view the data, I saw that the data is not proper from row no. 108731. I also found that an error appears when I extract CSV file Sales201.csv something regarding CRC etc. I request you to help
Thank you for this fantastic video! But from a business POV I disagree with your calculation for the daily average. If works if you have at least one sale for every day. But say you sell something on only two days in a year. Let's say one day it's $500 and the other one it's $1,000. If we use your function the daily average would be $750 and you'd be mislead to think everything is going very well. But the business reality is that on the other days your revenue was $0. A good and statistically relevant way to calculate the average would be Total Revenue/COUNT(Calendar[Date]). In this example it would be $4.11.
I download the homework file, but when I load the Start data into the Power Query, it always returned error "The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source. More Details: OLE DB or ODBC error: [Expression error] The key didnt match any row in the table. An error occurred while processing table 'data'. The current operation was cancelled because another operation in the transaction failed". How can I fix this? Thanks a lot!
If this world is fair, UA-cam should create a prise to appreciate UA-cam channel owner Like you, FREE amazing and useful learning, Thank you again and again
The UA-cam world is not fair. I hate to say it, but UA-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 : ) : ) : )
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.
i'm not able to change the data type from whole number to decimal number. I cannot visuzlice the dot which says that im working with decimal numbers. 19:10
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 : )
Can anyone help me figure out why power query locks up when I click transform data(used to be edit) after getting data at 15:44? Sometimes it works, but most times not. I've tried it with single files and multiple. The power query editor window shows, but the main section where the files should be displayed is blank and won't accept any input
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!
I'm Trying to use Power Pivot to create a Measure that is an Equivalente of Future Value in Excel. Is there a way I can acheve that in Power Pivot DAX. If I can get and Example it would be greate
Hi! I have a data model in PowerBI but am getting errors on some excel files because they contain extra columns. My client said some files have a couple more coilmns because they started collecting additional data over the past couple of years. How do I get the data model to match up the various columns that are in different positionsaand also allow for the additional columns to show up for the newer files? (Note: There is one excel file per week per year for 7+ years, so I don't want to have to do any manual edits to these excel files. Each Excel file has one table. All the tables are named the same, e.g. "VAorders".
Sir is it possible to convert location based data like city, country etc to set it to location data format in power query just like power pivot...thanks
I get the concept of AVERAGEX only when it uses the months as the row headers, but when I change to sth like the sales reps like in your dashboard where you get the average daily revenue for each sales rep, I am not in a position to explain how that figure is derived by the AVERAGEX function
Regarding the topic starting at 01:14:00, wouldn't it be faster to just adjust the pivot table value field settings to show values as % of grand total? Then you can just use the Total Revenue measure without creating a new one with CALCULATE?
Hello, Thanks for the tutorial. I am sorry but during the homework I don't know why I am getting all the country name when I put my slicer :/. Can you help me to change this in order to only have the countries needed. Thanks
Hi there, What about if I have 1 horizontal and 1 vertical table and I wish to conect them? I tried that myself, but for some reason the result from the horizontal table applies to every row of the vertical table. So my Q is this: table1: Horizontal, lets say I have 3 values: per unit,total, avarage, and I have lets say unique transaction ID. table2: Vertical, where I do have all the accounts and transaction that came thru. now I want to match the 2 tables - how would I do it without transforming one of the tables to other table design?
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 : )
we already add a revenue column in ftransaction table by row contaxt . Cann't we use related (vlookup) function in dSalesRep to extract the sum of revenue instead of calculate the sum of sales
I have a big transaction table in power pivot which contains date, data, device Name columns. I am trying to find the maximum value of the data column for a specific device name and once find then I need to extract the related date for that maximum value? I am new to DAX and would appreciate any help in this regard!
I am getting the following messages when trying to do a data model in Power Pivot "The requested operation cannot be performed on a file with a user-mapped section open" and "The current operation was cancelled because another operation in the transaction failed" Please assist with resolution of this error message? I do not have any file that is open, l have followed the instructions correctly but l keep getting the same message.
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: ua-cam.com/video/JGppsnepJFo/v-deo.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 : )
/_///////////ๅ///////_/////ๅๅ/_//
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
another advantage is that Excel can do incremental / "recursive" Power Query runs -- that is, data captured in Excel from prior PQ runs can inform and condition the current PQ run. A simple example is to store the PQ refresh date & time in Excel, which PQ in turn uses to delimit its refresh to only new files, and combine prior data with the new. Works well, for instance, if you want to decode data from thousands of live Excel files stored in SharePoint.
Absolutely fantastic
Glad it is fantastic for you, Bulent!!!
Great Video... I learnt a lot of things.. it will make my life a lot easier . Thanks @ExelIsFun for such remarkable work :)
You are welcome!
Great video! Thanks for your work!
You are welcome, Teammate pmsocho!!!!
When you're doing the cumulative ytd(1:12:50), it shows the grand total as the last yearly total in the list. How do you fix that to be the actual grand total for all the years?
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 UA-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
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.
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.)
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 : )
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 UA-cam for 13 years to try and bring free Excel Education to the world!!!!
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!!!!
Sir you literally changed my life :D
That is why I post here at UA-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
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 !
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
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 : )
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!!!
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 : )
Excellent Video!! You Sir are my EXCEL GURU. Thank you very much Professor Mike.
You are welcome, Sivakumar!!!
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!
EPIC video..by EPIC teacher... On EPIC topic...
Thanks for the EPIC comment, RRR!!!! I can't wait myself - to make more DAX videos over the months ahead : )
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 UA-cam: free Excel and Power BI education for the world!!! Glad you like it : )
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!!!!
Amazing and complete explanation, thnx !! Learned a lot from it...
Glad that you learned a lot, Marcel!!!! Thanks for your support : ) !!!!!
Fantastic presentation...it's as if he understands EXACTLY what the student needs to know!!!
Glad it helps!!!
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!
Thank you very much (ou como dizemos em Portugal Muito Obrigado)
I'm begining now with Power Query and you are to excellent.
I'm learning a lot with you.
Glad that I can help you to learn, Gosto!!
The most comprehensive, insightful video on Power Pivot and DAX. Thank you for providing this knowledge for free.
is so awesome Mike
the video take so much work
thanks for this inspiration
Youa er welcome for the work and inspiration!!! Thanks for your support, Sevag!!!!
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?
Great vedio ❤️...love the dax studio, calculate , sumx function 🤟😊
Glad you love it!!!!! : ) : )
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!!!!
wow. this is 2 hours of free goodness easily charged elsewhere.it'll take my channel awhile to share about fundamentals before i can touch about PowerQuery, PowerPivot, and PowerBI
Thanks for watching and supporting, Excel Bear!
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!!!!
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!!!!
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.
Hi Mr. Mike I would like to to watch this video more then 10 times it's really astonishing thank you so much Mr. Mike I really want to know if I can store any data in the database and use it in search or anything i want in excel sheet.
10 * 2 Hours = 20 hours of great learning fun : ) Yes, you can get data from data Model, as seen in the video, but it is not as easy as a Query in a database, like Access... However, I do have a video coming up in this series that will show how to enter data into a cell in an Excel Sheet and then run a DAX Query against it to extract data. Thta video is coming soon : ) Thanks for your support, Ismail!!!
@@excelisfun i can't wait thank u so much for the hard effort you put to make it easy for us I really appreciate
What an amazing video!! Thanks a lot Mike. Respect!!
You are welcome, Santiago!!!
Amazing!!!! Thanks Mike :) Great Fun. I only wish that I am not going to work tomorrow so I will go through all of this, All Day
It will take all day to watch, try, watch some more, rewind, watch again, try some more, read pdf notes, try home work, then start over for a rewind of the fun and learning : )
You are welcome, John!!! Thanks for your consistent support : )
Great tutorial! You are the best I've seen on UA-cam!! God bless
Thank you Sir.
You are welcome, NC nc!!!!! Glad that the video event is helpful. Thanks for your support : ) : )
Wow. THis is awesome. Thanks amazing Mike for this EXCELlent video
You are welcome for the EXCELlent video, Syed!!!!
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?
Excellent, thanks Mike
Glad it is EXCELlent for you, Dave! Thanks for your support!!! : )
Thanks!
You are welcome "Lots of Cs". Thank you for your donation: it definitely helps me to keep making videos. When you make donations, we are like a Team. Thanks, CCC ccc!!! Go team : )
Thank you, Sir, for the splendid work you put in here
You are welcome!!!
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?
Mike , pls help me with Power query separate playlist and
Also
Power Pivot separate playlist.
There are playlist but it's not sequential or I might be able to find the right one.
Pls can you help me out
The Power Query and Power Pivot Playlist are not classes with sequential videos. For the Full class on Power Query, Power Pivot, DAX and More (the MSPTDA Class) here is the Playlist: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html (The MSPTDA class is ongoing, though. I will keep posting videos for the next 6 months. It will not have the full 40 + videos until Spring 2019 : )
I hope you will support with comments, Thumbs Up and Sub : )
Thanks Mike :) Yes I do like your work, post ...
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.
Inspiring, as always, Mike, thank you very much once again!
You are welcome once again, Saul!!!! Thanks for your support!
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 UA-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 UA-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...
Wow Mike, that’s another EPIC video in this wonderful series.
And all these videos complement each other so nicely!
I like this very much and I compliment you with the results! :-)
Now, I’m only 9:50 into the video as I write this, so: back to the video...
Done.
Mike, people argue that the data model can be used to efficiently store large amounts of data -- as opposed to txt- or csv-files.
The only drawback is that you can't "see" all the data at once if you want to.
Now, it looks like that cube function is a way to dump data from the data model "back" into Excel.
And if the tables are sufficiently small, than it can be done. Do you agree?
Thanks for the comment at 09:50, Geert!!!! Thanks for your support!!!!!
@@GeertDelmulle , yes, the drawback is that you cannot see it... But even in Excel or text files, if you have 100,000, you never really "see" it all...
@@GeertDelmulle , it is not the CUBE functions that allow you to put data in the sheet, it is DAX Table Functions that we can use in Edit DAX in the Existing Connections trick.
Mike, you're right: I mixed it up with that CUBEMEMBER-formula that you used to make the title of your dashboard.
It is, as you said, the DAX table-function that does it.
It's always good to have a an index with active time markers in the video description for quick reference. :-)
YOU ARE GREAT TEACHER. ❤️
I am glad that my videos help you : )
Perfection. ..watching it for the second time
It is all stuff that I have video blogged about before in other videos, but in this one video I tried to put it all together in a story that could be watched in one place - I also tried to make it an Epic Video Event with pdf notes, Excel workbook and practice problems!!! Thank you for your support, NoShadowOfDoubt - please tell all your friends and spread the word about thsi fun Video Event : )
ExcelIsFun it is one of few good things of life joys ...thank you Mike ...
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 : )
simply overwhelmed , thanks
You are welcome!!
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!!
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!!!!!!
Fantastic series of Videos. Very grateful!
You are welcome, Muuip!!!! Thank you for your support with your comments, Thumbs Ups on each video and a sub : )
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 :)
Can’t believe that I just sat through 2 hours without any breaks.
WOWOWOW! Thanks!
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..
Revision Topic
37. (01:52:20) How to Double Check that a DAX Formula is yielding the correct answer?
38. (01:53:22) DAX Table Functions. See CALCULATETABLE DAX Function.
39. (01:55:07) DAX Studio to visualize DAX Table Functions, and to efficiently create DAX Formulas
40. (02:00:12) Existing Connections to import data from Data Model into an Excel Sheet
The title trick was a gold box by it self ...
Yes, title trick Gold Box!!!!!!! No shadow of a doubt : )
@@excelisfun thank you ...happy holidays .. be safe
I followed it and it took all day... from 3 pm to 11 pm. And I skipped some measures ... used implicit variables. F** it. Laptop for 2K can handle it.
@01:11:00 (onwards revise) Introduction to Time Intelligence DAX Functions. See TOTALYTD DAX Function (for self-reference)
The sheet Topics in workbook 015-MSPTDA-ComprehensiveIntroPowerPivot.xlsx only has 19 topics, not 39 (not that it matters much)
I guess they have been overlooked somehow 😢
Dear Mr Mike
When I tried to load the CSV files (in the folder 015-TextFiles) in Power Query, I am getting errors in around 87k rows. I have managed to create a table of these 87k rows. I request you to guide what could have possibly gone wrong.
Dear Mr Mike
Further to my request of today, kindly further note that I am facing some problem with CSV of Sales2017.csv. When I excluded the said file from loading, I did not get any error. When I opened that CSV directly to view the data, I saw that the data is not proper from row no. 108731. I also found that an error appears when I extract CSV file Sales201.csv something regarding CRC etc. I request you to help
Thank you for this fantastic video! But from a business POV I disagree with your calculation for the daily average. If works if you have at least one sale for every day. But say you sell something on only two days in a year. Let's say one day it's $500 and the other one it's $1,000. If we use your function the daily average would be $750 and you'd be mislead to think everything is going very well. But the business reality is that on the other days your revenue was $0. A good and statistically relevant way to calculate the average would be Total Revenue/COUNT(Calendar[Date]). In this example it would be $4.11.
How can we achieve this in power query/dax/MCode??
=countif(A:A,{"abc","xyz"})
Please help me if it is possible or not?
I download the homework file, but when I load the Start data into the Power Query, it always returned error "The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.
More Details: OLE DB or ODBC error: [Expression error] The key didnt match any row in the table.
An error occurred while processing table 'data'.
The current operation was cancelled because another operation in the transaction failed". How can I fix this? Thanks a lot!
If this world is fair, UA-cam should create a prise to appreciate UA-cam channel owner Like you, FREE amazing and useful learning, Thank you again and again
The UA-cam world is not fair. I hate to say it, but UA-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 : ) : ) : )
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.
i'm not able to change the data type from whole number to decimal number.
I cannot visuzlice the dot which says that im working with decimal numbers. 19:10
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 ; )
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 : )
Thank you very much,
Any help? In the video at 22:22, the Excel file "015 msptda comprehensive powerpivot" is not in the list. How to get that file
Truly the most incredible series.
Glad you like the classes, Spencer!!!
this is truly epic!
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.
Awesome video- thank you!
You are welcome!
Can anyone help me figure out why power query locks up when I click transform data(used to be edit) after getting data at 15:44? Sometimes it works, but most times not. I've tried it with single files and multiple. The power query editor window shows, but the main section where the files should be displayed is blank and won't accept any input
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 : ) : ) : ) : )
I'm Trying to use Power Pivot to create a Measure that is an Equivalente of Future Value in Excel. Is there a way I can acheve that in Power Pivot DAX. If I can get and Example it would be greate
Hi! I have a data model in PowerBI but am getting errors on some excel files because they contain extra columns. My client said some files have a couple more coilmns because they started collecting additional data over the past couple of years. How do I get the data model to match up the various columns that are in different positionsaand also allow for the additional columns to show up for the newer files? (Note: There is one excel file per week per year for 7+ years, so I don't want to have to do any manual edits to these excel files. Each Excel file has one table. All the tables are named the same, e.g. "VAorders".
Sir is it possible to convert location based data like city, country etc to set it to location data format in power query just like power pivot...thanks
I get the concept of AVERAGEX only when it uses the months as the row headers, but when I change to sth like the sales reps like in your dashboard where you get the average daily revenue for each sales rep, I am not in a position to explain how that figure is derived by the AVERAGEX function
Regarding the topic starting at 01:14:00, wouldn't it be faster to just adjust the pivot table value field settings to show values as % of grand total? Then you can just use the Total Revenue measure without creating a new one with CALCULATE?
Hello, Thanks for the tutorial. I am sorry but during the homework I don't know why I am getting all the country name when I put my slicer :/.
Can you help me to change this in order to only have the countries needed.
Thanks
Hi, thanks for this comprehensive work. But the link doesn't work, we can't download the files.
"Dollar sign English" aka pound lol
Amazing video though. Thanks :)
Wow! I feel my current Excel knowledge is at Caveman level after watching this video.
Hi there,
What about if I have 1 horizontal and 1 vertical table and I wish to conect them?
I tried that myself, but for some reason the result from the horizontal table applies to every row of the vertical table.
So my Q is this:
table1: Horizontal, lets say I have 3 values: per unit,total, avarage, and I have lets say unique transaction ID.
table2: Vertical, where I do have all the accounts and transaction that came thru.
now I want to match the 2 tables - how would I do it without transforming one of the tables to other table design?
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 : )
we already add a revenue column in ftransaction table by row contaxt . Cann't we use related (vlookup) function in dSalesRep to extract the sum of revenue instead of calculate the sum of sales
I have a big transaction table in power pivot which contains date, data, device Name columns. I am trying to find the maximum value of the data column for a specific device name and once find then I need to extract the related date for that maximum value? I am new to DAX and would appreciate any help in this regard!
You are awesome.. I suggested your channel to many of my friends... ❤️❤️
I am getting the following messages when trying to do a data model in Power Pivot "The requested operation cannot be performed on a file with a user-mapped section open" and "The current operation was cancelled because another operation in the transaction failed"
Please assist with resolution of this error message? I do not have any file that is open, l have followed the instructions correctly but l keep getting the same message.
Thanks Mike! Epic video indeed. Can't wait for #16.
The heart of data modeling is in this video: DAX. #16 will be out in a few weeks... I can't wait either : ) Thanks for your support, Robert Chong!!!