HomeОбразованиеRelated VideosMore From: ExcelIsFun

MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI

275 ratings | 6103 views
Download Start files: .xlsx Start 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DAX-IteratorsTables-Start.xlsx Zipped Folder with data that was already loaded into previous file: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018Files.zip Download Other Start Files: .pbix Start 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DoubleCountStart.pbix .xlsx Start 2: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-ALLVALUES-Start.xlsx .pbix Start & Finished 2: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-BigData.pbix Zipped Folder with data that was already loaded into previous file: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018BigDataTextFiles.zip Download Finished files: .xlsx Finished 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DAX-IteratorsTables-Finished.xlsx .pbix Finished 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DoubleCountFinished.pbix .xlsx Finished 2: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-ALLVALUES-Finished.xlsx pdf Notes: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DAX-IteratorsTables.pdf Assigned Homework: Download Word Document and read: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/HomeworkVideo018.docx Then download the rest of the files and complete the homework: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-HomeworkFile01Start.pbix Examples of Finished Homework: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-HomeworkFile01Finished.pbix https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-HomeworkFile02Finished.pbix This video teaches you about the power and pitfalls of DAX Iterator Functions and DAX Table Functions in Excel Power Pivot and in Power BI. Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor. Topics: 1. (00:15) Introduction 2. (00:31) Summary of what we know so far in class about Iterators and Table Functions 3. (01:38) Summary of what we will learn in this video about Iterators and Table Functions 4. (02:51) What does DAX stand for? Data Analysis eXpressions 5. (03:36) Introduction to Iterator Functions 6. (04:51) Learn how Iterators Work using SUMX & AVERAGEX Iterating Over Fact Table at Transaction Grain 7. (08:24) Be Careful of Context Transition & Iterating over a Fact Table that "Materialize" Unnecessary Tables. 8. (11:14) DAX Studio to Time DAX Formulas. Including how to use the ROW DAX Function to help time Measures. 9. (18:55) Create PivotTable with Measures and see that the Measure that has to materialize a Fact Table for each row in the iteration takes a LONG time to calculate in a PivotTable. 10. (19:49) Power BI Example of “Double Count” Problem with Context Transition. Be Careful of Context Transition & "Double Count" Problem. Solutions #1: Use Formula rather than Measure. 11. (25:07) Solution #2: Use Power Query to add Primary Key can fix the problem also. 12. (26:11) AVERAGEX at Day Grain use dDate Table. We want Context Transition in this formula and we will Never have the “Double Count” problem because dDate Table has no duplicates. 13. (27:37) Grain of Fact Table or Iterator. 14. (30:18) AVERAGEX at Month Grain with VALUES(Column) 15. (30:18) Introduction to idea that we need DAX Table functions like VALUES to help create the correct Grain for Tables that we can use in Iterator Functions. 16. (31:35) VALUES DAX Function 17. (33:17) DAX Studio to Visualize or Materialize Tables 18. (33:48) CROSSJOIN DAX Function. 19. (35:05) CONCATENATEX and VALUES to list values in the Current Filter Context 20. (40:15) AVERAGEX at Month Grain with CROSSJOIN(VALUES(Column),VALUES(Column)) 21. (41:30) Can we reduce "Cardinality"? Try not to Iterate over Fact Table. Alternative Formula for Total Revenue in Power BI Example. Timing formulas in DAX Studio. 22. (48:20) Time DAX Measures from Power BI with DAX Studio. 23. (50:20) Closer look at ALL and VALUES DAX Functions. Discuss the Blank Row that shows up from unmatched item in a relationship. 24. (55:21) Compare and Contrast ALL and VALUES. 25. (56:10) DISTINCT and ALLNOBLANKROW DAX Functions. 26. (57:23) Look at other DAX Table Functions. 27. (58:15) Discussion of FILTER DAX Table Function and CALCULATETABLE DAX Table Function 28. (01:01:00) Look at ADDCOLUMNS DAX Function. 29. (01:01:30) Excel Existing Connections to pull data from Data Model into Excel Sheet. 30. (01:02:54) DAX VALUES Function to pull a variable from an Excel Sheet into the Data Model. 31. (01:05:05) Summary
Html code for embedding videos on your blog
Text Comments (111)
DIGITAL COOKING (2 days ago)
hi teacher! , please I come up with those questions because power bi is a very powerful tool for presentation and dynamic do we still need a power point !! (because I didn't try it yet, so I don't know if I should study it ) the second question I know we can create a database with access thing we can't do with excel what are the other things we can do only with access and we should learn them mike I'm confused about that tow software if I should learn them, I believe that learning must be done with a teacher even short answer from you will save me a lot of hours thanks :)
+ExcelIsFun ok clear :)
ExcelIsFun (1 day ago)
+DIGITAL COOKING We still need Power Point becasue not all presentations come from a Power BI File : )
+ExcelIsFun yes mike I understand you but why not just do a presentation on power bi directly so saving time from learning power point and we can change things directly( like filter region or customer )in power bi things we can't do in powerpoint I don't know if my question is clear(sorry still poor in English language)
ExcelIsFun (1 day ago)
I do NOT understand your question. Power Point? That is a presentation program. Database = store raw data, and, create information like queries for reports. Power Query = Clean, Transform & Import Data, or ETL (Extract, Transform and Load Data). Power Pivot and Power BI = self service reporting and analytics on Big Data.
Gustavo Muñoz (14 days ago)
You are the best, thans. From Colombia.
Sanjeev Soni (15 days ago)
Very informative and focused information. Mike you are helping a lot to learn on DAX. The Context Transitions is getting more clearer now. Great way to update the Data Model from Excel and update the records/reports. Happy New Year. Looking forward more DAX and MCode Videos
ExcelIsFun (14 days ago)
Glad the videos help, Sanjeev!!! The next video has even more about Context Transition, including the seeming difficult ALLSELECTED and KEEPFILTERS functions which interact with Context Transition : ) Thank you very much for your support : ) : )
Hasan Toma (16 days ago)
Leila Gharani (19 days ago)
Thank you for this wonderful series! Happy 2019!
lmoraferia (20 days ago)
What a video!! You are a great PBI mentor. Thanks for guiding us all around the world to get the most out of this tool. I can't wait for the next video. In the meantime, I am going to work on the homework on MSPTDA 18. Thanks so much. Muchas gracias !!
ExcelIsFun (20 days ago)
You are welcome, Imoraferia!!!! Thanks for your support, with your comment, Thumbs Up and Sub : )
Malina C. (20 days ago)
Thanks for DAX...BI fun :) Thumbs up!!!
ExcelIsFun (20 days ago)
You are welcome, Teammate : )
Victor Friesen (20 days ago)
Happy New Year Mike !! One of the best videos I have ever seen !!! Thanks for sharing and Thumbs UP !
ExcelIsFun (20 days ago)
That is amazing if it is one of the best : ) Glad it is best for you. More to come too : ) Thanks for the support, Victor : )
nimrodzik1 (20 days ago)
I've iterated through all topics with a great pleasure :)
ExcelIsFun (20 days ago)
Yes: iterate through all the topics with pleasure. But what was the grain, nimrodzik1? Thanks for the support : )
Elbina Rizakhanova (21 days ago)
Dax explanation has started! So goooood !
Elbina Rizakhanova (20 days ago)
I'm pretty sure, there are a lot of people waiting for your DAX lessons! Wish you and your dear ones all the best in this new year!
ExcelIsFun (20 days ago)
Yes, the DAX fun has begun !! Much more to come! Thanks for the support, Elbina, with your comment, Thumbs Up and Sub : )
Gentle Raj (21 days ago)
Now it's deadly thrilling, i like thrill, however need revision. Too much fun
ExcelIsFun (20 days ago)
Yes!!!! I am glad that the thrilling fun is too much : ) Much more to come, Gentle Raj! Thanks for your support!!!
fabry Espejo (21 days ago)
El 2018 fue un año de mucho aprendizaje. Muchas gracias y Feliz año! 2019
ExcelIsFun (20 days ago)
Glad it all helps! Happy 2019 : )
Usman Elahi (21 days ago)
Dear Sir, Happy New Year! I am using excel at a very basic level. I want to start watching your videos but do not know from where to start. May you please help me in this regard. I want to start from the scratch. Thank you!
excel is fun (16 days ago)
+Usman Elahi Thanks for your support on each video with a comment and Thumbs Up : )
excel is fun (16 days ago)
+Usman Elahi You are welcome! I look forward to hearing from you at each video : )
Usman Elahi (17 days ago)
+excel is fun Thank you!
excel is fun (21 days ago)
Go to excelisfun home page and watch the intro video : ) It shows the Excel Basics Playlist - which is what you want!
John Borg (21 days ago)
Amazing Mike. I am trying the AverageX with a formula and a Measure. With a Unique identifier, the measure will give the proper value. This is amazing!!! By the way....is there a formula to create a unique identifier with DAX instead of index in Power query or Power BI?? I am using the Data from MSPTDA 15 so I had to go to Power Query using the Index and updated the fTransaction table. Thanks and sorry if i am being so persistent. :) :)
John Borg (21 days ago)
+ExcelIsFun Just wanted to know out of curiosity, just to see the build-up of the formula.
ExcelIsFun (21 days ago)
There probably is a DAX way, but why, when the easy Data Transformer tool is Power Query? I am not sure of the DAX method... Also, consider that on very large Fact Tables, you might not want to add a Unique Identifier to the Columnar Database because it might add a lot to the RAM storage...
Wayne Edmondson (22 days ago)
Hi Mike.. looks epic.. looking forward to a deep dive into your MSPTDA series in 2019. Thanks and Thumbs up!
ExcelIsFun (21 days ago)
Yes, Wayne, it is an intense and dense and powerful series. Efficiency and fun with MS Power Tools : ) Thanks for your support : )
Jackie Sosa (22 days ago)
Hi, I'm leaving a message for my dad whose currently trying to hyperlink a batch of files coming from the same file but of different names. If you have a chance and time can you email [email protected]
ExcelIsFun (21 days ago)
This is not the place for back and forth dialog to get Excel solutions. Try this great Excel Question site: mrexcel.com But, you have to ask a detailed question rather than just ask someone to send someone else an e-mail.
Miltinho Camo (22 days ago)
Great video Mike... Thanks a lot.
ExcelIsFun (21 days ago)
You are welcome a lot, Miltinho!!!! Thanks for your support : )
kiwikiow (22 days ago)
Happy New Year Mike. Thank you very much for great Excel teaching :)
ExcelIsFun (22 days ago)
You are welcome for the EXCELlent Excel fun, kiwikiow!!!! Thanks for your support : )
Rob Michaels (22 days ago)
Thanks for explaining "grain/granularity" so clearly. I have not seen it mentioned in other people's DAX videos and it seems important to understand. Happy new year!
ExcelIsFun (22 days ago)
Yes, Grain and Cardinality are important if we are going to iterate : ) Glad you like the videos and thanks for your support, Rob! Happy New Year!
Thanks a lot! Mike I look forward your next videos soon. Happy New Year and New EXCELlent
ExcelIsFun (22 days ago)
Happy EXCELlent New Year to you, Manh!!!!! Thanks for the support : )
DIGITAL COOKING (23 days ago)
Totally awesome Just can't figure out how the index column fixes the problem for duplicate rows. Mike, you're very adept in excel for me you are master, in theory, thanks keep going because we really need your videos Note: don' t see the thumb down i think he finally admits he must click thumb up :)
ExcelIsFun (22 days ago)
With the Index, then there can be no duplicate records because each has a different number in that column. Thanks for your support, DIGITAL COOKING!!!!
Beng Hwa Chan (23 days ago)
Best wishes and a Happy New Year, Mike. A very good video about table functions, some optimization tips and risk of double counting. Thumbs up!
ExcelIsFun (22 days ago)
Glad you like the video, Beng!!! Happy New Year to you, with many more videos to come in the new year. Thanks for your support : )
Janaka Perera (23 days ago)
Happy New Year to you, Mike!
ExcelIsFun (22 days ago)
+Janaka Perera , thank you very much for your kind words! I work hard to tell a story that can reveal and inspire all of us to be efficient and have fun with excel!!! Thank you very much for your support, Janaka : ) : )
Janaka Perera (22 days ago)
@ExcelIsFun All your videos are excellent, and this particular one is no exception. They deserve to be liked by everybody. Thanks for iterating the need to be cautious when using the iterative functions! I am sure they have bugged a few of us when dealing with "big data". Mike, you, as a teacher, is unique among others. Your tutorials are truly professional up to the highest standards. The effort you make to bring up a perfect tutorial is just incredible. Your explanations are always precise, concise, and brilliantly presented. It is unquestionable that you render a tremendous service to unveil the hidden capabilities of Excel. It may not be an exaggeration to say, "Power Tools & Mike ExcelIsFun Girvin" transformed Excel from an ordinary spreadsheet application into a robust data analysis tool for business". Microsoft owes Mike ExcelIsFun Girvin more than an array of MVPs! Keep up your great work, mate!
ExcelIsFun (22 days ago)
Happy New Year, Janaka!!!!! Hope you like the video and thanks for your support : )
sujeet samal (23 days ago)
Happy New Year Sir... Mike.... for your continue input in a very classic and simple way..
ExcelIsFun (22 days ago)
Thank you, sujeet!!!! Happy New Year to you too! Thanks for your support : )
ozan şen (23 days ago)
Another great video . It is so nice to see that you also included the use of other dax functions like “ filter , calculatetable , values , distinct etc in this video . I also liked DAX Studio . Thank you for your efforts in preparing this epic video . Happy New Year 🎈🎆
excel is fun (23 days ago)
You are welcome, ozan!!! Thanks for your support : ) Much more to come...
Thank you for your contribution in the growth of many people Happy New Year 2019
ExcelIsFun (23 days ago)
You are welcome! It is my duty as a human to try and make the world a better place !!! Happy New Year!!!!
Richard Hay (23 days ago)
Great video to end the year!! Awaiting more in 2019 and beyond. :)
ExcelIsFun (22 days ago)
+Richard Hay I also use Ctrl + Alt + F5 more than I use a local refresh, but sometimes... BTW, yes, people click Thumbs Down but it is just because they were not taught the fundamental truth in life that as humans we must fulfill our duties and rights, not just rights...
Richard Hay (22 days ago)
Very true and on occasion I have done a local refresh for that reason, but personally I rarely take that approach. In fact, I have the "refresh data on opening the file" (or however it is worded) selected.
ExcelIsFun (22 days ago)
+Richard Hay . It will refresh everything in the Workbook, and if you are pulling from Power Query and you have a lot of data, you may not want to refresh all, and just do a local refresh.
Richard Hay (22 days ago)
You continue to get that 1 Thumbs Down!! Either that viewer is from one of those cultures where "thumbs up" actually means what we know of as "Thumbs Down" or else its one of those excel users who just doesn't know UP from DOWN (I have met a few LOL!!!). I do have a question. Late in this video you use the keyboard to refresh the Pivot Table. That is only the second of your videos where I have noticed you using the Keyboard Shortcut (undoubtedly there are more such videos that I have not seen!!). You "always" mention the keyboard to create a Pivot Table, and you love keyboards (cuz they're FAST!!) as much or more than I do so why the "reluctance" to use it to REFRESH? I find it to be one of the most helpful shortcuts!
ExcelIsFun (23 days ago)
Glad it is a good end... and yes: so much more to come in 2019!!!! Happy New Year!
Thanks Mike for this amazing video. Happy New Year, and we will celebrate when we'll have a new video from you :P
ExcelIsFun (23 days ago)
Thanks, Syed!!!! Happy New Year to you : )
Sevag Barsoumian (23 days ago)
He Mike, what a fine episode for DAX thanks A lot and very good 2019 with more success Sevag
ExcelIsFun (23 days ago)
Glad it was fine for you, Sevag!!!! Happy New Year : ) : )
Geert Delmulle (23 days ago)
This is yet another Epic Video right here, Oh My! And thank God for DaxStudio: it's the perfect visualisation, simulation, formula builder and timing tool for DAX! For now: Best wishes to you Mike, have some great New Year festivities and see you in 2019!
ExcelIsFun (23 days ago)
Thanks, Geert!!!! Happy New Year!
John Borg (23 days ago)
Thanks Mike :) :)
ExcelIsFun (23 days ago)
You are welcome, John Borg!!!!! Happy New Year!
Chris Mancinelli (24 days ago)
Wow, that was an amazing video Mike! Can;t wait to keep learning from you in 2019, Happy New Year!
ExcelIsFun (23 days ago)
Glad it helps, Chris!!!! Thanks to viewers like you, I keep providing all these resources! Happy New Year, Chris : )
Mohamed Chakroun (24 days ago)
Waw Excellent end of year with this nice video on dax iterating...Happy new year 2019 Mike and all teammates :-)
ExcelIsFun (23 days ago)
Yes, we can iterate through all our Teammates in the new year : ) Happy New Year!
Ismail Ismaili (24 days ago)
this is really awesome I don't know how to thank you Mr. Mike by the way happy new year.
ExcelIsFun (23 days ago)
You always thank me on each video and I love it! Happy new Year to you : )
Robert Chong (24 days ago)
Thank you so much for this Mike! I'm trying to move some reporting done on mails to Power BI at work so these DAX videos help me out quite a bit!! I wish you a happy new year and will be patiently waiting for #19.
ExcelIsFun (24 days ago)
You are welcome so much, Robert! Glad the DAX helps : ) Thanks for the support and Happy New Year : )
robert cline (24 days ago)
Fantastic Presentation.... That was dynamite.... Happy New Year!
ExcelIsFun (24 days ago)
Happy New Year to you, robert cline! Glad you liked it and thanks for the support : )
Evelyn C (24 days ago)
Happy New Year to you & a big Thank You💚💝💖❤️
ExcelIsFun (24 days ago)
A Big Thank You to you Evelyn C!!!!! Thank you for your support : ) Happy New Year : ) : )
Arun Thandapani (24 days ago)
Happy New Year Mike, Thank you for all these videos, They are fantastic!
ExcelIsFun (24 days ago)
Happy New Year to you, Arun!!!! Thank you very much for your support with your comment, Thumbs Up and Sub : )
RRR program (24 days ago)
Brilliantttt stufff mike.... This channel has given me the most learnings than any other channel out there in youtube... Your commitment... Love... And fun towards excel is the BEST...
ExcelIsFun (23 days ago)
+RRR program , cool : ) Thanks for watching, repeating and supporting, RRR : )
RRR program (23 days ago)
+ExcelIsFun Thats awesome mikee.... I love watching all the video even if the concepts are repeating...
ExcelIsFun (23 days ago)
+RRR program , I will try to finish this MSPTDA series by summer 2019, then re-do Busn 218 (Advanced Excel). Then continue Excel, Power Query, Power Pivot, Power Bi and maybe R or Pyton....
RRR program (23 days ago)
+ExcelIsFun Woowwww.. That is kinda biggest honour for meee.... Whats ur next 5 year plan mike .. On a broad level I would ask..... Continue with Excel ONLY ?? or any plans to start with other leading statistical languages like Python or R ??? Microsoft is all integrating R and Python ( though MS is inclined more towards R ) then why not ExcelISfun ???
ExcelIsFun (24 days ago)
Thanks RRR!!!!! I thought of you as I made this video because a while back you asked for more DAX videos... I will keep making fun videos if you and our other Teammates keep watching : )
Excel Bear (24 days ago)
yes! another MSPTDA. happy new year, mike!
ExcelIsFun (24 days ago)
Thanks, Excel Bear! Happy New Year to you!!!
Valdimir Wallace (24 days ago)
Thanks a lot Mike. Your MSPTDA series was one of the major highlights of 2018 for me. I have learnt so much. Keep up the good work. Wishing you and your team a Happy New Year when it comes. Cheers.
ExcelIsFun (24 days ago)
You are welcome a lot! Glad the MSPTDA series is helpful - and - even more videos to come in 2019!!! Thanks for your support, Vladimir!!!
Alexis Georgantidis (24 days ago)
Happy new year!!! Thank you so much
ExcelIsFun (24 days ago)
You are welcome so much and Happy New Year, Alexis!!!! Thanks for your support!!!!
LongTimeTTFan (24 days ago)
Thanks a lot, Mike. Wish you and your family a very happy New Year!
ExcelIsFun (24 days ago)
Thanks for the New Year wishes, LongTimeTTFan!!! And thanks for the support : )
trevor m (24 days ago)
Happy New Year Mike. Video 18 last video of 2018. 2019 we keep learning
ExcelIsFun (24 days ago)
Yes, in 2019 there will be even more MSPTDA videos to come!!! Happy New Year to you, trevor m, and thanks for your support : )
ali mogharrebi (24 days ago)
Thank you Mike and happy new year! I'll see this amazing tutorial.
ExcelIsFun (24 days ago)
Yes, this tutorial covers a LOT of topics about tables and iterators!!!! Glad it helps, happy new year and thanks for the support, ali!!!! : )
Pavan Lalwani (24 days ago)
Last video of the year Yeah !!!!
ExcelIsFun (23 days ago)
+Pavan Lalwani : )
Pavan Lalwani (24 days ago)
Definitely definitely sir :).
ExcelIsFun (24 days ago)
Last video for the year, and so many more videos to come in 2019!!!! Thanks for your support, Pavan!!!!
Duy Diep (24 days ago)
Happy New Year Mike!!!
ExcelIsFun (24 days ago)
Happy New Year to you too, Duy!!! Thanks for watching and for the support : )

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.