Download Start Excel File: https://excelisfun.net/files/EMT1678Start.xlsm
Download Finished Excel File: https://excelisfun.net/files/EMT1678Finished.xlsm
Learn how to build a data model with Power Query and DAX Formulas to force a Slicer to Sort by the PivotTable Values.
Topics:
1. (00:00) Introduction.
2. (01:00) Concept for Data Model
3. (01:26) Power Query to create Dimension Table and Index Columns, then load to Power Pivot Data Model. See Add Index Column and Merge feature.
4. (03:07) Add Sort Table From Excel to the Data Model.
5. (03:13) Create Automatic Date Table.
6. (03:32) Create Relationships.
7. (03:45) Create Measure with SUM Function.
8. (04:11) Discuss Aggregate Formula vs. Measure & Filter Context..
9. (04:50) Add Key to Dimension Table to avoid Circular Dependency using Table Properties.
10. (05:09) Create DAX Calculated Column using: a Measure, and Aggregate SUM Function, IF Function, VALUES Function.
11. (05:20) Discuss Content Transition to allow Filter Content into Measure in a Calculated Column.
12. (05:43) Example of Aggregate Function with NO Filter Context.
13. (05:55) Discuss how to get formula to contain two numbers to sort in opposite directions.
14. (07:48) Sort By Feature.
15. (08:20) Create PivotTables and Add Slicer.
16. (09:14) Test.
17. (09:30) Edit Dax Calculated Column.
18. (10:05) Add VBA code to update automatically.
19. (11:06) Summary.
20. (11:39) End Video Links