Download Zipped Folder with All Files: https://excelisfun.net/files/17-M365ExcelClassVideoFiles.zip
50 pages of pdf notes: https://excelisfun.net/files/17-M365ExcelClassDax..pdf
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 full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
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

Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ . Artist: http://audionautix.com/

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