Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Excel Data Modeling with Power Pivot & DAX
Section 1: Welcome to Your Power Pivot, Data Modelling & DAX Course
What You Can Achieve with Power Pivot, Data Model & DAX (4:06)
⚙️How to Use the Platform for Best Learning Experience
🔻Download These Files to Follow Along
Quick Check-in
🔍 Looking for a Specific Topic? Use the Transporter!
******* Power Pivot, DAX & Data Modelling Essentials *******
🛣️ Roadmap for the Essentials Module
Brush up Your Pivot Table Knowledge (if you need to…)
Section 2: What You Can Do with Power Pivots That You CAN'T DO with Pivot Tables
Imagine this... (5:24)
What it Costs You When You Don't Know DAX (or Data Model) (10:59)
What Happens When You Know About the Data Model (6:54)
More Benefits: Convert Pivot Table to Formulas & Show Filtered Totals (7:36)
Best Combination for Data Modelling: Power Query & Power Pivot Together (7:16)
When to Use Pivot Tables Vs. Power Pivots (Become Pro) (5:12)
3 Quick (Power) Pivot Table Tips
🤔 Quiz - Power Pivot vs. Pivot Table
📝 Key Takeaways: When to Use a Data Model
Section 3: Getting Started with Power Pivot & the Data Model
Power Query Tips: Data Types, Changing Source File & Forgetting to Load to Model (6:54)
Power Pivot Window: Create Relationships from Diagram View (7:20)
Lookup Tables vs. Data Tables (Dimension vs. Fact Table) (8:54)
Star Schema vs. Snowflake Schema (3:45)
How Relationships Flow (Become Pro) (6:32)
Tip: Adding Hierarchies & Hiding from Client Tools (4:13)
Activity: Create a Data Model & Set up Your First Power Pivot
Adjust Default PivotTable Options (Become Pro) (9:01)
💪 Challenge: How Do You Handle One BIG Table? (8:58)
📝 Key Takeaways: Best Practice for Data Models (4:00)
🤔 Quiz - Power Pivot & Data Model View
Note on Handling Many-to-Many Relationships
Section 4: DAX Measures & Syntax
Introduction to DAX: Implicit vs. Explicit Measures (8:05)
Overview of Common DAX Functions (6:48)
Creating Measures, Syntax & What to Watch Out For (9:30)
💪 Challenge: Create Measures - Unique Count & Median - Preference?
Challenge Solution: Create Measures - Unique Count & Median (6:40)
Quick Useful Tips: QAT, Value Field Settings, All Vs Active, Pivot Settings (Become Pro) (4:24)
IF Function & Avoiding Errors When Writing Measures (6:37)
How Measures Work - Introduction to Filter Context (4:16)
Add KPI's to your Reports (Become Pro) (5:39)
Add Conditional Formatting to Your Reports (3:59)
💪 Challenge: Add a Color Scale to Your Report
🤔 Quiz - Measures & Syntax
📝 Key Takeaways
Section 5: Calendar Tables
Why you Need a Calendar Table (2:23)
Easiest Way to setup a Calendar Table (5:40)
Setup a Calendar Table in Excel (3:25)
Create a Dynamic Calendar Table in Power Query (Become Pro) (5:08)
How to Sort Calendar Columns Correctly (MMM-YY, Months, Week Days) (6:51)
TOTALYTD for YTD Calculations (Including Fiscal Periods) (8:01)
💪 CHALLENGE: MTD Calculation
Quiz - Calendar Tables
📝 Key Takeaways
🆕 Enjoying the Course so far? Refer a friend and get 30% off your next XelPlus purchase!
Section 6: Calculated Columns Vs Measures
Calculated Columns in DAX (vs. Power Query vs. Measures) (6:14)
Tips for Working with Calculated Columns (3:12)
💪 CHALLENGE: New Calculated Columns (Half Year, Members & Product Type) (7:47)
Calculated Column for Total Sales Calculation (5:33)
DAX Iterator Functions: SUMX versus SUM (Total Sales Calculation) - Become Pro (6:31)
🤔 Quiz - Calculated Columns Vs Measures
📝 Key Takeaways: When to Use & When to Avoid Calculated Columns
Section 7: Common DAX Functions & Multiple Fact Tables
What You'll Learn in this Section
DAX RELATED Function (8:08)
Multiple Fact Tables (Become Pro) (8:22)
DIVIDE Function & Why it's Better than Division Operator (3:50)
💪 CHALLENGE: Find the Mistake
VALUES Function: Calculate Average Daily Sales (8:26)
Disconnected Tables & HASONEVALUE & SWITCH Functions (Become Pro) (8:57)
🤔 Quiz - Common Functions & Multiple Tables
📝 Key Takeaways: Common DAX function & Multiple Fact Tables
Section 8: Dashboard Project #1 - You're Responsible for OfficePlus California!
Dataset and your Challenge (2:02)
Dashboard Challenge: Your Tasks in Detail
Setting Up the Data Model (5:49)
Stacked BAR Chart with DAX Data (Total Sales by Department) (11:06)
Total Cost & Margin % Calculation by Product & Department (Slicer) (8:51)
Doughnut Chart by Custom Region (Connected to Slicer) (9:17)
Hiding & Showing a Pivot Table (Smart Trick) (6:30)
Periodic & YTD Slicer Selection (Smart Trick) (13:57)
Dynamic Text Boxes & CUBE Formulas (9:07)
Final Touches: Updating Source Data & Formatting Tips (6:24)
📝 What You Achieved: Key Takeaways (1:45)
Section 9: Introduction to Advanced DAX Functions
What You'll Learn in this Section
CALCULATE Function: First Introduction (7:26)
ALL Function: Remove Filters (9:29)
Variables in DAX Functions (Become Pro) (6:05)
💪 CHALLENGE: Calculate Variance Actual to Budget for Innovative Products (4:53)
📝 Key Takeaways: Introduction to Advanced DAX Functions
🎁 BONUS TIPS
CUBE Functions - How to Write these from Scratch!
How to Copy Your Data Model to Another File (2:27)
******* Advanced DAX: Master Difficult DAX Concepts *******
What You Achieve & Introduction to Dataset (3:19)
🛣️ Roadmap for the Advanced Module
Section 10: Data Compression & Smart Tips
Workbook Info & Notes
Setup the Model with a Separate Measures Table (Hidden Tip) (7:54)
Handling Drill To for Separate Measures Table
DAX Columnar Database & the Advantages it Brings (5:38)
Data Compression: How to Optimize Your Data Model with Smart Decisions (4:26)
🤔 Quiz
📝 Key Takeaways: Data Compression
Section 11: Evaluation Context & Relationships
Filter Context, Relationships & Grand Totals (9:00)
💪 CHALLENGE: Filter Context - Calculate Last Day of Sales for Each Month (4:45)
Concept of Expanded Tables (Become Pro) (7:01)
RELATED & RELATEDTABLE (When to Use These) (7:15)
Iterator Functions: When to Iterate & Use Related (SUMX vs SUM) (6:34)
Row Context: The Skeleton Underneath (6:54)
💪 CHALLENGE: Find the Mistake! DAX Function is Wrong! (6:39)
📝 Key Takeaways: Evaluation Context
Section 12: DAX Table Functions & Useful Accompanying Functions
Filter Function to Restrict the Filter Context (7:49)
CHALLENGE: How Many Orders Below Retail Price? (5:55)
ALL Function with FILTER to Unfilter (8:32)
FILTER Function & Filter Context Interaction (Become Pro) (9:56)
Use Variables for More Clarity
VALUES & HASONEVALUE Function (8:55)
CONCATENATEX Function to Show Slicer Selection (Become Pro) (7:58)
💪 CHALLENGE: Get a List of Sales Dates for Each Product (4:45)
📝 Key Takeaways: DAX Functions
Section 13: CALCULATE Function
CALCULATE Function: Let's Review the Basics (4:34)
How to Visualize the Behavior of CALCULATE (8:06)
How CALCULATE Overwrites Existing Filters - KEEPFILTERS & FILTER(ALL) Combo (7:03)
OR, AND Conditions in CALCULATE (7:40)
CALCULATE with OR Condition from Multiple Columns (5:16)
Behavior of ALL in CALCULATE & Why Context Matters (Become Pro) (9:04)
ALLEXCEPT & ALLSELECTED in CALCULATE (Become Pro) (6:04)
Measures Based on Dynamic Thresholds (Disconnected Tables) - Become Pro (9:39)
Using Aggregation Functions in CALCULATE Filter (Unique Customers To Date) (9:00)
Using Variables for Complex Calculations (7:30)
💪 CHALLENGE: Find Last Follower Count (Value for Last Date) (6:54)
🤔 Quiz
📝 CALCULATE: Key Takeaways (8:21)
Section 14: Context Transition
Context Transition Explained with a Simple Example (Become Pro) (7:15)
When and How to Avoid Context Transition (3:38)
Where You NEED Context Transition (5:04)
💪 CHALLENGE: Maximum & Average Daily Sales (5:52)
Hidden Secret of Measures & Context Transition in Calculated Columns (6:36)
💪 CHALLENGE: Explain Why this Measure Returns Wrong Results (7:05)
📝 Key Takeaways: Context Transition
Section 15: Practical Advanced Calculations
What you Learn: Practical Advanced Calculations (1:26)
DAX Studio & Visualizing Tables (7:55)
TOPN Function (Sales of Top 3 Products) (7:25)
TOPN - Differentiate Between Ties (5:47)
Find Date with Highest Sales (CONCATENATEX & TOPN) (5:57)
🎁 BONUS: ADDCOLUMNS Function in DAX Studio (2:43)
Get Product Name with Highest Sales (5:53)
💪 CHALLENGE: Get the Customer Number with Highest Sales
RANKX Function for Dynamic Ranking (10:36)
Ranking Multiple Columns: CROSSJOIN Function (Become Pro) (3:25)
Many to One Side Calculations: Distinct Count of Sales City by Month (6:01)
💪 CHALLENGE: Distinct Count of Product Category by Month (Based on Sales)
📝 Key Takeaways: Advanced Calculations
******* PRO: Time Intelligence, Difficult Relationships and Advanced Concepts *******
Introduction to PRO Section (1:38)
🛣️ Roadmap for the PRO Module
Section 16: Data Modeling Challenges
What You'll Learn in this Section
USERELATIONSHIP for Multiple Relationships (Active vs. Inactive) (5:01)
💪 CHALLENGE: Calculate Total Sales Based on Delivery Date
Multiple Lookup Tables for Multiple Relationships (Duplicating Calendar) (3:25)
Many to Many Relationships: Handling Budget Data with Different Granularity (10:16)
TRUE Many to Many Relationships (7:25)
📝 Key Takeaways: Data Modeling Challenges
Section 17: Working with Dates & Time Intelligence Functions
Dataset Used in this Section
Overview of Time Intelligence Functions (6:34)
Year-to-date (YTD), MTD & QTD (5:52)
Year-to-Date From Scratch (with Filter Function) (6:51)
Previous Period Comparison (Last Month, Last Year etc.) (5:12)
Running Total Calculations (Annual Total, 10 Day Total etc.) (5:14)
Average Moving Total Calculations (Visualize with a Line Chart) (7:29)
Custom Calendars (e.g. 4-4-5 Calendar) & YTD Calculations (9:16)
Custom Calendar Previous Year Week Comparison (2:42)
💪 CHALLENGE: Previous Day Sales & Running Totals
📝 Key Takeaways: Time Intelligence
🎁 BONUS: COMPLETE GUIDE TO POWERPIVOT & DAX
🔻DOWNLOAD Your Complete eBook
🎁 BONUS: Dashboard Project #2 - You're Responsible for OfficePlus Headquarter Reporting!
What You Learn with this Dashboard Project
Project Overview (3:15)
Import Data & Create a Dynamic Calendar Table (8:00)
Online Sales % Trend by Reporting Region (9:09)
PY & YTD Comparison with Dynamic Filter for Latest Period (6:50)
Hidden Excel Trick to add Symbols to Pivot Table without Conditional Formatting (10:07)
Bar Chart for Instore vs Online Sales (showing current period) (3:50)
Finalize & Test whether Latest Period Updates on its Own (6:54)
Final Words
🎇 Wrapping up & What You've Achieved (1:48)
✍️Leave a Review
⬆️ Upgrade to Power BI
My Favorite Resources
Access to Bonus Items
🏅 Share Your Digital Certificate (0:40)
When to Use Pivot Tables Vs. Power Pivots (Become Pro)
Complete and Continue
Discussion
12
comments
Load more
12 comments