Discover advanced Excel techniques in this 36-minute tutorial video that demonstrates how to build a complete Data Model with relationships and DAX formulas without purchasing Power Pivot in Excel 2016. Learn to import tables, create relationships, and construct a Date Table using functions like YEAR, MONTH, and TEXT. Master DAX measures with functions such as CALCULATE, SAMEPERIODLASTYEAR, TOTALYTD, and DATEADD. Explore the creation of variables in DAX using VAR and RETURN keywords. Gain insights on efficient practices, including avoiding Implicit Measures and utilizing HASONEVALUE for hiding grand totals. Download accompanying files to practice along and reinforce your understanding of these powerful Excel data modeling techniques.
Overview
Syllabus
) How to Download Files..
) Introduction to building Data Model with Relationships and DAX Formulas without buying Power Pivot in Excel 2016..
) Import Tables and Build Relationships using the Relationship button in the Data Ribbon Tab..
) 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 complete list of dates, see the functions YEAR, MONTH and TEXT..
) Add Relationship to Date Table..
) 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”..
) Create first DAX Measure (Formula) to add Total Sales. See SUM Function and how to add Number Formatting to Measure..
) 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..
) Discuss Formula to get Last Year’s Sales..
) Discussion about Filter Context and how DAX Measures calculate or evaluate to get the correct answer..
) Create Second Measure (Formula) to get Last Year’s Sales using the CALCULATE and SAMEPERIODLASTYEAR DAX Functions..
) How to hide Grand Total Formula with IF and HASONEVALUE DAX Functions. We use HASONEVALUE Function on the Year Column..
) Formula to calculate Difference From Last Year using Two Previously Defined Measures and the IF Function..
) Third DAX Measure (Formula) for Running Total using TOTALYTD, IF and HASONEVALUE function on the Month Column..
) Fourth DAX Measure (Formula) for Difference from Last Month using CALCULATE, DATEADD, IF, HASONEVALUE, and AND DAX Functions. Also see Variable in DAX..
) Create Variable in Excel 2016 DAX Function language using the VAR and RETURN Keywords..
) Summary.
Taught by
ExcelIsFun
