Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Master Excel Power Pivot & DAX (Beginner to Pro)
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:21)
DIVIDE Function & Why it's Better than Division Operator (3:50)
πͺ CHALLENGE: Find the Mistake
VALUES Function: Calculate Average Daily Sales (8:25)
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:52)
π 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) (8:59)
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:24)
π 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)
πͺ CHALLENGE: Find Last Follower Count (Value for Last Date)
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock