excelisfun | DAME 06: DAX Fundamentals in Power BI & Power Pivot: 2.5 Hour Free Class @excelisfun | Uploaded 3 months ago | Updated 16 hours ago
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
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
