MSPTDA 15: Comprehensive Introduction to Excel Power Pivot, DAX Formulas and DAX Functions

Assigned Homework:
Download Excel File with Instructions for Homework:

This video teaches everything you need to know about Power Pivot, Data Modeling and building DAX Formulas, including all the gotchas that most Introductory videos do not teach you!!!

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
(00:15) Introduction & Overview of Topics in Two Hour Video
1. (04:36) Standard PivotTable or Data Model PivotTable?
2. (05:51) Excel Power Pivot & Power BI Desktop?
3. (12:31) Power Query to Extract, Transform and Load Data to Data Model – Get data from Text Files, Relational Database and Excel File.
4. (25:47) Build Relationships
5. (27:43) Introduction to DAX Formulas: Measures & Calculated Columns
6. (29:15) DAX Calculated Column using the DAX Functions, RELATED and ROUND
7. (31:20) Row Context: How DAX Calculated Columns are Calculated: Row Context
8. (33:49) We do not want to use Calculated Column results in PivotTable using Implicit Measures
9. (34:05) DAX Measure to add results from Calculated Column, using DAX SUM Function.
10. (35:29) Number Formatting for DAX Measures
11. (36:35) Data Model PivotTable
12. (39:31) Explicit DAX Formulas rather than Implicit DAX Formulas
13. (41:50) Show Implicit Measures
14. (45:00) Filter Context (First Look) How DAX Measures are Calculated
15. (50:14) Drag Columns from Fact Table or Dimension Table?
16. (53:30) Hiding Columns and Tables from Client Tool
17. (55:52) Use Power Query to Refine Data Model
18. (57:54) SUMX Function (Iterator Function). DAX Measure for Revenue using the SUMX Function to simulate Calculated Columns in DAX Measures
19. (01:01:00) Compare and Contrast Calculated Columns & Measures
20. (01:04:26) Why We Need a Date Table. Why we do NOT use the Automatic Grouping Feature for a Data Model PivotTable
21. (01:06:46) Build an Automatic Date Table in Excel Power Pivot. And then build Relationship.
22. (01:11:00) Introduction to Time Intelligence DAX Functions. See TOTALYTD DAX Function
23. (01:13:47) Introduction to CALCULATE Function: Function that can “see” Data Model and can change the Filter Context. (01:18:00) Also see the ALL and DIVIDE DAX Functions. Create formula for “% of Grand Total”. Also learn about (01:21:30) Context Transition and the Hidden CALCULATE on all Measures.
24. (01:27:18) DAX Formula Benefits.
25. (01:28:00) Example of DAX Formula that is easier to author than if we tried to do it with a Standard Pivot Table or Array Formulas
26. (01:31:50) AVERAGEX Function (Iterator Function) to calculate Average Daily Revenue.
27. (01:34:00) Filter Context (Second Look) AVERAGEX Iterator Function
28. (01:36:16) Build Dashboard. Create multiple DAX Formulas. Create Multiple Data Model PivotTables and a Data Model Chart.
29. (01:38:38) Create Measures for Gross Profit and Gross Profit %
30. (01:41:27) Continue making more Data Model PivotTables.
31. (01:41:50) Make Data Model Pivot Chart.
32. (01:45:10) Conditional Formatting for Data Model PivotTable.
33. (01:46:22) DAX Text Formula for title of Dashboard
34. (01:47:50) CUBE Function to Convert Data Model PivotTable to Excel Spreadsheet Formulas.
35. (01:50:05) Adding New Data and Refreshing.
36. (01:50:40) Update Excel Power Pivot Automatic Date (Calendar) Table. Clue is the blank in the Dimension Table Filter.
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
(02:03:15) Summary

[dr.frankpeffekoven]

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 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.

[LeilaGharani]

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.

[reanalytics]

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.

[datasciencewithdeepansh]

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

[temidayoadebiyi]

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!!!

[donreymo]

Fantastic presentation...it's as if he understands EXACTLY what the student needs to know!!!

[sandratran]

This is the Best of the Best among Pivot Table and DAX training. I spent whole week to watch all videos twice to get the most out of it. Thanks a lot. Sandra

[SHUBHADIP]

The most comprehensive, insightful video on Power Pivot and DAX. Thank you for providing this knowledge for free.

[gianpaolo]

You are just amazing!!! Thank you from 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

[excelisfun]

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

[MrBillleeds]

It there was a ranking for best instructors covering all topics in all of YouTube, you would have to be near the top, IMHO. You are amazing.

[ojiehkingsley]

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!!!

[excelisfun]

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 : )

[Pegasus]

It is hard to belive that someone can give so much valuable material for free. I know our way to correspond is liking, commenting and subscribing. I have done all of it apart from recommending your channe. It just can't be better.
THANK YOU SO MUCH!!!

[vinayagamoorthy]

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...

[sivakumar]

Excellent Video!! You Sir are my EXCEL GURU. Thank you very much Professor Mike.

[ahmedal-dossary]

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.

[ZAHIDHUSSAIN-rikg]

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!

[shoeshines]

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!

[RobMichaels]

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.