DAME 06: DAX Fundamentals in Power BI & Power Pivot: 2.5 Hour Free Class

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