Full Free DAX Class: Fundamentals of DAX in Power BI & Power Pivot. 365 MECS Class 17

This video teaches how the fundamentals of Columnar Database, DAX Calculated Columns, DAX Measures, Row Context, Filter Content, Context Transition, Overwrite Operation, DAX X Iterator functions, DAX CALCULATE function and much more!
This video covers.
1. (00:00) Introduction and video topics
2. (00:25) Topics in Video
3. (01:13) Why we use DAX, M Code & Worksheet Formulas: What makes Each Special?
4. (04:54) Review data, data structure, Excel files, Power BI Desktop files and pdf notes
5. (05:57) What is DAX?
6. (08:17) Comprehensive Discussion about Star Schema Data Model Components and how they interact with DAX: Columnar Database, Relationships, DAX Formulas, Hidden Columns
7. (14:49) Calculated Columns
8. (17:52) Row Context
9. (21:22) Measures
10. (23:28) Power BI Desktop Calculated Columns and Measures
11. (27:02) Data Model PivotTable
12. (28:05) Filter Context
13. (31:22) COUNTROWS Function (Super Charged COUNTIFS Function)
14. (32:15) Expanded Table Diagram
15. (34:49) Implicit Measures
16. (36:44) SUMX function and Iterator Functions to Replace Calculated Columns
17. (38:20) Compare SUMX One Step Method to Calculated Column SUM Two Step Method
18. (40:30) Row and Filter Context Work Together
19. (42:31) Using Measures in other Measures
20. (43:45) DIVIDE Function
21. (45:14) CALCULATE Function to Change Filter Context
22. (46:34) ALL Function to remove filters and get a Grand Total
23. (48:50) % of Grand Total Measure
24. (49:25) ALLSELECTED Function to get Filtered Grand Total and % of Filtered Grand Total
25. (51:55) % of Parent Total DAX Measure: 1) ALLEXCEPT Function and 2) ALLSELECTED & VALUES
26. (53:38) Compare ALL and VALUES Functions
27. (54:47) CONCATENATEX Function
28. (58:45) YOY Change Formula with SAMEPERIODLASTYEAR, CALCULATE, DIVIDE, HASONEVALUE and a special filtering Calculated Column in Data Table
29. (01:01:41) Variables in DAX using VAR and RETURN
30. (01:05:22) Calculated Helper Column to make Measure less complicated
31. (01:10:30) Boolean Filters
32. (01:12:45) Second Look at Filter Context
33. (01:14:00) Overwrite Operation
34. (01:15:50) FILTER & ALL for Boolean Filter
35. (01:17:18) Logical Tests in FILTER and CALCULATETABLE Functions
36. (01:19:34) FILTER & VALUES for Boolean Filter
37. (01:21:26) KEEPFILTER to Convert Overwrite Operations to an AND Logical Tests
38. (01:23:08) Boolean OR Logical Test with Double Vertical Bar Operator
39. (01:24:31) Self Filtering Report with KEEPFILTER
40. (01:25:00) Filter Context with KEEPFILTTERS
41. (01:25:45) Boolean OR Logical Test with IN Operator
42. (01:26:40) NOT Logical Test
43. (01:27:42) Context Transition in Calculated Columns
44. (01:31:42) Hidden CALCULATE in Measure
45. (01:33:50) Context Transition in Iterator like AVERAGEX. Calculate Average Monthly Sales.
46. (01:38:54) Context Transition and Filter Context
47. (01:40:27) Context Transition Error: Iterate Over Table with Duplicate Errors
48. (01:43:10) Context Transition: Correct Formulas and Incorrect Formulas
49. (01:45:47) Context Transition Error: Iterate over Fact Table
50. (01:46:52) Grain of Calculation & Iterator Functions for Transactional, Daily and Monthly Averages
51. (01:49:10) Average using DISTINCTCOUNT to make a faster formula
52. (01:51:13) Cardinality and Iterator Functions. See five examples of howto reduce cardinality and increase formula calculation speed
53. (01:57:11) DAX Studio to time formulas. EVALUATE Command.
54. (01:59:27) Complex Filter and Complex Filter Reduction Error (from Overwrite process): KEEPFILTERS or Data Modeling?
55. (02:02:01) KEEPFILTERS in Power BI Quick Measure
56. (02:03:22) 12 Month Moving Average DAX Measure: CALCULATE, AVERAGEX, DATESINPERIOD, IF, MAX Functions
57. (02:07:42) Table Filters in CALCULATE to go backwards across a Many-To-One Relationship
58. (02:10:52) Unmatched Items in a Relationship
59. (02:12:27) DAX Approximate Match Lookup
60. (02:17:07) DAX to create Date Tables in Power BI using GENERATE, ROW, CALENDAR and more
61. (02:19:08) Extract Data From Power Pivot Data Model using Existing Connections
62. (02:25:01) Video Summary and Conclusions
63. (02:25:48) Closing and Video Links


#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #datamodel #DAX

[Ath.Kar.]

Well Mike. It is obvious that you love what you do and this must be a big payoff for all these years of free sharing a huge load of excel knowledge with us. Nonetheless, let me express my sheer gratitude for the unparalleled level of your teaching, the excellence of videos and supporting materials, the exhaustiveness of the topics you present. What can we say? You have managed to create a global online community and inspire us to improve daily. My respect.

[excelisfun]

Important Notes (including error correction):
1)
This video is not for the faint of heart because it is 2.5 hours in length, it has 50 pages of pdf notes and provides 10 files for you to use and follow along. This DAX Flix shows 67 different DAX formulas with all the basic to advanced topics and concepts such as Row Context, Filter Context, Context Transition, Overwrite Operator, DAX Queries, SUMX and CALCULATE and ALL and ALLSELECT and AVERAGEX/VALUES and KEEPFILTERS and SELECTCOLUMNS and so many other powerful functions!!! You can call this video hard core because all in one video I will cover the many invisible and difficult topics in DAX, but I will try to visualize and describe those topics and illuminate the unique and spectacular power of DAX as compared to Worksheet Formulas and Power Query M Code! If you are a beginner, this is your ticket to DAX mastery, but get ready to study the video and pdf notes and examples many times to truly absorb and assimilate the many concepts! If you are already good with DAX, the video and pdf notes should put all the pieces together into a cohesive understanding so that you can deploy DAX to solve your calculation and data analysis tasks with easy and fun! DAXisfun!! Go Team!!

2)
12 month moving average formula in Power BI Desktop file is not correct. I incorrectly added the filter dDate[Year]>2017 to CALCULATE. I fixed the downloaded files, but not the video. This is correct:
12MonthMovingAverage =
VAR Move12MonthAve =
CALCULATE(
AVERAGEX(VALUES(dDate[EOMonth]), [TotalSales($)]),
DATESINPERIOD(dDate[Date], MAX(dDate[Date]), -12, MONTH))
RETURN
IF(MAX(fSales[Date]), Move12MonthAve)

3)
I added Homework and Homework Solutions to the kipped folder as of 01:16 PM Pacifc Standard Time May 18, 2023

[ExcelxlNl]

Hello Mike, it's been a long time since I've been actively following you on YouTube. Don't ask me why. Because if I learned a lot of Excel from one person, it's you! Since a few weeks, I started to get into PBI, PQ and DAX. And of course I end up on your channel. Where again I come across hundreds of videos on this subject. This small contribution does not cover the efforts you make to teach the whole world Excel, PBI and much more for free but is the least I can do to "express" my appreciation.

PS: Slaying Excel Dragons is still the best Excel book for beginners as far as I am concerned. Even though this one is now about 12 years old. The principles are still the same.

[ivanzhelyazkov]

Speechless! If there is one Excel/PBI source in the world that every one should learn, this channel is.
Thank you, sir!

[girishghadge]

Gratitude, Mike! Your presentation on Power BI transcended mere instruction; it was an odyssey into comprehension. Your adeptness in unraveling intricate formulas and abstract notions bestowed upon me the clarity of vision, revealing the inner workings of Power BI's essence.

[barttitulaerexcelbart]

Hi Mike: this was awesome!! 🙏🙏👍👍. Actually I have seen all those DAX formulas a 100 times, but with your explanation it all comes together. I love it that you compare Power Pivot and Power BI. One advantage of Power Pivot is that you can filter the fact table and the measure updates. Mike you can really be proud on yourself creating this masterpiece. I did not do research but I am convinced the Excel community is also for sure proud on you... Thanks again!!

[loose.electricity]

Incredible course. I have watched / tried out so many courses and methods to get where I need to be with PBI/DAX and I just wasn't finding what I needed, until now. This is the one. You go into explanations and show examples of so many things that I feel are often brushed over. What a gift.

[Richkotite]

Thank you very much for all the help you provide. Back in 2016 I passed the Microsoft Certification test and could not have done it without your help. I'm now working on enhancing my DAX skills and your site is amazing. Thanks again.

[hthienel]

What an unbelievably useful "summary" (it is condensed but at the same time has loads of detailed examples). This is the art of teaching at play from the "grand master of data".

[hill]

I don't know which I am more in awe of, the Knowledge or the Generosity

[benrogers]

3:20 Avg = Sum/Count, so you can calc a month avg with 1 sheet formula Not the right solution everywhere, but is cool that you can do it. Appreciate these videos!

Edit: the point of this formula being that no intermediate group by pivot table is required! You can work directly from the raw fact table.

[spilledgraphics]

Minute 10:00 is EYE - OPENING. Thank you Mike! I will watch this completely. Up to minute 10, for now.

[martinpaulbannister]

2hrs 26 minutes off pure heaven. Looking forward to watching your tutorial over the weekend. 👍

[markosiridzanski]

thank you sir! Finally I will get the chance to work on PBI projects as Business Analyst thanks to your unreserved and inexhaustible support!

[yvlove]

This is absolutely amazing. I've watched so many videos regarding DAX, but your explanations are so thorough and clear; I am so thankful that you have decided to share this! I know I'll watch it at least one more time, along with all the other videos in this series. Thank you so much for sharing your experience and knowledge!

[reng]

¡Thanks Amigo, the effor that you've done by preparing all this material deserves at least a this tiny recognition from my side ...Please keep this kind of outstanding videos+ materials with all your foreing students!!! regards from Uruguay!!

[gvitullib]

Another Masterpiece !! Really Mike, I'm impressed how you keep going improving more and more the quality of your videos. Such detailed information and the way how is represented the concepts and all the integration behind scenes for better understanding, just amazing... Thanks dude, the level of effort for produce this must have been huge.

[richardhay]

Super great! Irarely use spreadsheet Pivot Tables any more but I often use the Data Model and find it really difficult to keep up with growing list of DAX capabilities. Its been hard to find good help with DAX so tis video is especially helpfull. Obviouly I haven't progressed too far (about 45 minutes) but so far so great!! Thanks.

[Darshanam-Jatra]

Simply amazing basics are so well explained for anyone to master DAX Thanks a lot ❤

[terrylee]

As always, the ONE WHO DELIVERS, delivers again. And; as always Mike, I am in your debt. Coffee fund on it's way