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
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:
Examples of Finished Homework:
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.
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
I am absolutely flabbergasted by the quality of these videos. I cannot begin to imagine the amount of work you must put in. Thank you so much!!
Quick question regarding the first homework exercise (fixing the measure): is there any reason not to just use AVERAGE() rather than AVERAGEX()? The revenue is after all just a column (no expression evaluation needed). Am I missing something?
No, AVERAGE is fine. Really, that was a mistake on my part that I just never got around to fixing... As you can tell, these productions take an epic amount of time and I just never got back to it. This class goes live at Highline College in April and so I imagine I will fix it then... I am glad that these productions and free resources help you and many others. Please do help and always leave a small comment and click that thumbs up on each video that you watch.
Thank you Mr. Girvin for this exciting series. I have learnt a lot from the MSPTDA classes; you are a top notch profesor. I have a problem that I haven't been able to solve myself, so a Little help from any of the viewers will be really appreciated: I need to do some sort of CountIf in the fTransactions table granularity level; for example, taking the fTransactions table Mr.Girvin uses throughout the video, what would be the formula to Add a column to the fTransactions table that counts how many occurrences of the product Carlotta appear in fTransactions. For example, at 26:08 the first record would be: December 13, 2018 | Carlotta | 1,425.45 | 6 | 51 | 1,425.45 | 1,425.45 | 1 | New Column = 6 (if there were only the 18 records shown from fTransactions at 26:08). Thanks!
Great Job, for unknown reasons my AVERAGEX for revenue calculated at the beginning does not give same result when using the measure approach and the formula approach.Formula gives 2585.46 while measure gives 3067.138
hi mike, trying to understand the cardinality i used some small data sets (less than 50 rows) to calculate price of services purchased for some stores.
1- CALCULATE(SUMX(ftransactions,RELATED(dPricePerService[Price])),VALUES(ftransactions[Store Code]))
2- SUMX(dPricePerService,dPricePerService[Price]*CALCULATE(COUNTA(ftransactions[Store Code])))
Prices for each service is stored in a table and then linked to the ftransaction Table which only contains Store Code and purchased Services (text fields). amazingly when i use the 1st method, its much faster than the second one (4 to 5 times)!!
is it the result of small ftransaction Table i used or i've missed something?
I am already at 200 hours in creating the CALCULATE video... It is taking more time to create than even #9 in this series Power Query M Code... Should be out by Wed or Thursday this week, just a few more days... The pdf notes (free book) might even be better than the video!!!!
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 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)
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.
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
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 : ) : )
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 !!
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!
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. :) :)
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...
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]
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.
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 :)
+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 : ) : )
@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!
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 🎈🎆
+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...
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.
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!
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!
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 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 ???
Sechskies Eun Ji Won and rookie singers Lee Soo Hyun and Kim Eun Bi performed the third OST single titled "Love Song". The rookies, who are both training to debut in HYWY Entertainments girl group HYWY Girls, joined the veteran to sing about falling in love with an unlikely person. The rhythmic medium temp track is the perfect tune to make your spring days even brighter.
As a child, there was a portrait in our family home in Paris that I always loved. Today, it’s known as Maya with Doll – but to me it was just a portrait of my mother, albeit a remarkable one. “Your grandfather was a painter,” she would say, whenever the subject of the canvas, one of many that hung around the house, came up in discussion. It was only when I began school, and whispers about my heritage started to follow me, that I realised what an understatement that was. My grandfather was far more than a painter. He was the defining figure of 20th-century art – and, as I would learn later from years of academic study, a true genius. It was a revelation that would shape the course of my life in many ways. When Picasso died – in 1973, the year before I was born – he left behind 45,000 works, not to mention personal objects and correspondence.