Completed
) Summary
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Hack Data Model, Date Table & DAX Measures Without Buying Power Pivot
Automatically move to the next video in the Classroom when playback concludes
- 1 ) How to Download Files.
- 2 ) Introduction to building Data Model with Relationships and DAX Formulas without buying Power Pivot in Excel 2016.
- 3 ) Import Tables and Build Relationships using the Relationship button in the Data Ribbon Tab.
- 4 ) Build Data Table in Excel, including TEXT Function formula to get Months to Sort Correctly in a Data Model PivotTable, without using the Sort By Column feature. See the Fill Series Trick to get a c…
- 5 ) Add Relationship to Date Table.
- 6 ) Start building Data Model PivotTable and notice a problem with month names not sorting correctly. Then fix it with the TEXT Function using custom Number Formatting “mm – mmm”.
- 7 ) Create first DAX Measure (Formula) to add Total Sales. See SUM Function and how to add Number Formatting to Measure.
- 8 ) What NOT to do when you are hacking the Data Model. Do not drag Sales or Date Fields to Values Area or Row Area of Pivot Table. These are called Implicit Measures and they are not efficient.
- 9 ) Discuss Formula to get Last Year’s Sales.
- 10 ) Discussion about Filter Context and how DAX Measures calculate or evaluate to get the correct answer.
- 11 ) Create Second Measure (Formula) to get Last Year’s Sales using the CALCULATE and SAMEPERIODLASTYEAR DAX Functions.
- 12 ) How to hide Grand Total Formula with IF and HASONEVALUE DAX Functions. We use HASONEVALUE Function on the Year Column.
- 13 ) Formula to calculate Difference From Last Year using Two Previously Defined Measures and the IF Function.
- 14 ) Third DAX Measure (Formula) for Running Total using TOTALYTD, IF and HASONEVALUE function on the Month Column.
- 15 ) Fourth DAX Measure (Formula) for Difference from Last Month using CALCULATE, DATEADD, IF, HASONEVALUE, and AND DAX Functions. Also see Variable in DAX.
- 16 ) Create Variable in Excel 2016 DAX Function language using the VAR and RETURN Keywords.
- 17 ) Summary
