Power BI: Advanced Course – Data Science Academy

About Course

Course Overview

Duration: 35-40 hours (7-8 weeks at 5 hours/week)
Level: Advanced
Prerequisites:

Completed Beginner to Intermediate Power BI course or equivalent
Strong understanding of data modeling and relationships
Solid foundation in basic and intermediate DAX
Experience building and publishing Power BI reports

Target Audience: Experienced Power BI developers, BI architects, data analysts seeking mastery, and professionals preparing for advanced certifications

Course Objectives

By the end of this course, students will be able to:

Write complex DAX formulas using advanced patterns
Implement sophisticated data models with complex requirements
Optimize Power BI solutions for enterprise-scale performance
Build advanced analytics including what-if scenarios and forecasting
Implement row-level security with dynamic patterns
Master Power Query M language for complex transformations
Design enterprise-grade BI solutions with governance
Troubleshoot and optimize poorly performing reports

Course Schedule (Sample 8-Week Plan)
- Week 1: Module 1-2 (Advanced DAX Fundamentals & Patterns)
- Week 2: Module 3 (Complex DAX Functions)
- Week 3: Module 4-5 (DAX Optimization & Power Query M)
- Week 4: Module 6-7 (Advanced Modeling & RLS)
- Week 5: Module 8 (Advanced Analytics) + Project 1 Start
- Week 6: Module 9-10 (Enterprise Deployment & Performance) + Project 1 Due
- Week 7: Module 11-12 (Advanced Visualizations & Integration) + Project 2
- Week 8: Final Capstone Project + Presentations
Career Advancement

Roles This Course Prepares You For:
- Senior Power BI Developer
- BI Architect
- Data Analytics Consultant
- Power BI Center of Excellence Lead
- Enterprise BI Solution Designer

Master Data Modeling for Scale:
Move beyond simple Star Schemas. Learn to handle complex scenarios like Many-to-Many relationships and role-playing dimensions correctly.
Implement Composite Models (mixing DirectQuery and Import mode) for massive datasets.
Utilize Aggregations to make petabyte-scale data respond instantly.
The Art of Performance Tuning:
Why is your report slow? Learn to use the Performance Analyzer and external tools like DAX Studio to diagnose bottlenecks.
Understand the VertiPaq engine under the hood to optimize compression and query speed.
DAX Mastery (The "Black Box"):
Deep dive into Evaluation Context: Mastering Row Context vs. Filter Context and the crucial concept of Context Transition.
Write complex patterns for advanced Time Intelligence (e.g., "Moving Averages across non-standard calendars").
Master iterator functions (SUMX, FILTER) and variables for optimized code.
Advanced Power Query & The "M" Language:
Go behind the GUI. Learn to read and write raw M code.
Create robust Custom Functions to automate complex ETL logic across multiple sources.
Implement Incremental Refresh to handle large data loads efficiently in the Service.
Enterprise Deployment & Security:
Implement robust Row-Level Security (RLS) (static and dynamic) to ensure users only see their data.
Manage On-premises Data Gateways for connecting cloud reports to local servers.
Introduction to Application Lifecycle Management (ALM) and deployment pipelines.

Course Content

Module 1: Advanced DAX Fundamentals (6 hours)

1.1 Deep dive into row context and filter context
1.2 Automatic context transition in calculated columns
1.3 Context transition with CALCULATE
1.4 Iterator functions and context transition
1.5 Common context transition pitfalls and solutions
1.6 Performance implications of context transition
1.7 Filter context propagation through relationships
1.8 Understanding ALLSELECTED in depth
1.9 The expanded table concept
1.10 Arbitrary shape filters
1.11 Shadow filter context
1.12 Context modification hierarchy
1.13 KEEPFILTERS function and filter overwrite
1.14 REMOVEFILTERS vs ALL vs ALLEXCEPT
1.15 Multiple filter arguments optimization
1.16 Filter tables vs filter columns
1.17 CROSSFILTER function for dynamic filter direction
1.18 Lab Exercise: Complex sales allocation across multiple dimensions

Module 2: Advanced DAX Patterns (7 hours)

2.1 Custom fiscal calendars and implementation
2.2 Semi-additive measures (opening/closing balances)
2.3 Snapshot fact tables and point-in-time calculations
2.4 Moving averages and running totals
2.5 Period-over-period growth rates
2.6 YTD, QTD with fiscal calendars
2.7 Multiple calendars in one model
2.8 Lab Exercise: Financial reporting with fiscal calendar
2.9 Distinct count optimization techniques
2.10 Weighted averages
2.11 Median, percentile, and statistical calculations
2.12 Cumulative totals and rankings
2.13 Top N dynamic calculations with TOPN
2.14 ABC analysis implementation
2.15 Pareto principle calculations – bounce reading lessons
2.16 Creating and managing calculation groups – bounce reading lessons
2.17 Calculation group precedence – bounce reading lessons
2.18 Format string expressions in calculation groups – bounce reading lessons
2.19 Dynamic measure selection with calculation groups – bounce reading lessons
2.20 Time intelligence calculation groups – bounce reading lessons
2.21 Complex calculation group scenarios – bounce reading lessons
2.22 Performance considerations – bounce reading lessons
2.23 Lab Exercise: Build multi-dimensional time intelligence with calculation groups

Module 3: Complex DAX Functions and Patterns (6 hours)

3.1 VAR and RETURN statements
3.2 Variable scope and benefits
3.3 Performance optimization with variables
3.4 Materialization and storage engine optimization
3.5 Complex nested variable scenarios
3.6 SUMMARIZE and SUMMARIZECOLUMNS deep dive
3.7 ADDCOLUMNS and SELECTCOLUMNS
3.8 GENERATE and GENERATEALL for complex combinations
3.9 UNION, INTERSECT, and EXCEPT
3.10 ROW function for single-row tables
3.11 TREATAS function for virtual relationships
3.12 Creating virtual dimension tables
3.13 Nested iterators and performance
3.14 RANKX for dynamic ranking
3.15 EARLIER function for row-by-row calculations
3.16 Complex SUMX scenarios
3.17 Iterator optimization techniques
3.18 Lab Exercise: Customer RFM analysis with advanced DAX

Module 4: DAX Performance Optimization (5 hours)

4.1 Understanding DAX Studio
4.2 Reading query plans
4.3 Storage Engine vs Formula Engine
4.4 Scan, CallbackDataID, and other operators
4.5 Identifying bottlenecks in DAX queries
4.6 Server timings analysis
4.7 Reducing formula engine operations
4.8 Optimizing iterators
4.9 Using variables effectively for performance
4.10 Avoiding expensive functions
4.11 Breaking down complex measures
4.12 Materialization strategies
4.13 Optimizing relationships for query performance
4.14 User hierarchies vs DAX calculations
4.15 Minimizing cardinality impact
4.16 Calculated columns vs measures performance comparison
4.17 Lab Exercise: Optimize a poorly performing dashboard

Module 5: Advanced Data Modeling (5 hours)

6.1 Many-to-many relationships without bridge tables
6.2 Role-playing dimensions optimization
6.3 Slowly changing dimensions (SCD Type 2)
6.4 Parent-child hierarchies with DAX
6.5 Bridge tables for many-to-many
6.6 Multiple fact tables at different granularities
6.7 Bi-directional filtering strategies and risks
6.8 Inactive relationships and USERELATIONSHIP
6.9 Multiple relationship paths resolution
6.10 Cross-filtering best practices
6.11 Virtual relationships with TREATAS
6.12 Understanding composite models
6.13 Combining Import and DirectQuery
6.14 Aggregation tables setup and configuration
6.15 Automatic aggregations
6.16 User-defined aggregations
6.17 Performance impact of aggregations
6.18 Lab Exercise: Design an enterprise data model with aggregations

Module 6: Row-Level Security (RLS) Advanced (3 hours)

7.1 Static vs dynamic RLS
7.2 RLS with security tables
7.3 USERNAME() and USERPRINCIPALNAME() functions
7.4 Manager hierarchy security
7.5 Multi-level security implementation
7.6 RLS testing strategies
7.7 RLS with many-to-many relationships
7.8 Combining RLS with object-level security
7.9 Performance optimization for RLS
7.10 RLS in composite models
7.11 Troubleshooting RLS issues
7.12 Lab Exercise: Implement multi-tier hierarchical security

Module 7 :AI Agents for Power BI with MCP

7.1 What MCP is and why it matters for BI
7.2 Microsoft’s Power BI MCP servers
7.3 Setup lab
7.4 Reading a model through the agent
7.5 Writing DAX measures with an agent, then auditing them
7.6 Bulk operations
7.7 Automatic model documentation
7.8 DAX performance through the agent
7.9 Remote MCP server (demo)
7.10 Preparing a model for AI
7.11 Limits and failure modes
7.12 Governance, data leakage, and safe practice

Module 8: Advanced Power Query and M Language (5 hours) (Reading Articles)

5.1 Understanding M language syntax
5.2 Functions in M: built-in and custom
5.3 Let expressions and step referencing
5.4 Error handling with try/otherwise
5.5 Conditional logic in M
5.6 List and record operations
5.7 Dynamic parameters and parameterization
5.8 Functions for reusable transformations
5.9 Recursive functions in Power Query
5.10 Combining files from folder with custom logic
5.11 Web scraping and API consumption
5.12 JSON and XML parsing advanced scenarios
5.13 Query folding deep dive
5.14 Native query evaluation
5.15 Diagnosing folding issues
5.16 Balancing transformation location (source vs Power Query)
5.17 Lab Exercise: Build a parameterized data pipeline with custom functions

Module 9: Advanced Analytics Features (6 hours) (Reading Articles)

8.1 Key Influencers visual advanced usage
8.2 Decomposition tree deep dive
8.3 Q&A visual optimization
8.4 Smart narrative customization
8.5 Anomaly detection configuration
8.6 Creating what-if parameters
8.7 Scenario analysis with field parameters
8.8 Sensitivity analysis implementation
8.9 Multi-parameter simulation
8.10 Dynamic pricing and forecasting models
8.11 Setting up R and Python in Power BI
8.13 Python for machine learning integration
8.14 Time series forecasting with R/Python
8.15 Clustering and segmentation
8.16 Custom visual creation basics
8.17 Standard deviation, variance calculations
8.18 Correlation and regression analysis in DAX
8.19 Monte Carlo simulation concepts
8.20 Lab Exercise: Build a comprehensive forecasting solution

Module 10: Enterprise Deployment and Governance (4 hours) (Reading Articles)

9.1 Understanding deployment pipelines
9.2 Dev, test, production workflow
9.3 Deployment rules and automation
9.4 Managing dataset parameters across environments
9.5 Version control integration strategies
9.6 Power BI Dataflows creation and management
9.7 Computed entities and linked entities
9.8 Incremental refresh in dataflows
9.9 Datamarts overview and use cases
9.10 When to use dataflows vs datasets
9.11 Power BI tenant settings
9.12 Workspace governance
9.13 Endorsement: certification and promotion
9.14 Usage metrics and monitoring
9.15 Audit logs and activity tracking
9.16 Lab Exercise: Design governance framework for enterprise deployment

Module 11: Performance Optimization Mastery (4 hours) (Reading Articles)

10.1 Reducing model size with data types
10.2 Column-based compression understanding
10.3 Auto-date/time tables impact
10.4 Calculated columns vs computed columns
10.5 DirectQuery optimization strategies
10.6 Hybrid tables and incremental refresh
10.7 Reducing visual count per page
10.8 Optimizing custom visuals
10.9 Page load optimization techniques
10.10 Bookmarks and performance impact
10.11 Interaction optimization
10.12 Reducing unnecessary queries
10.13 Premium capacity optimization
10.14 Power BI Embedded optimization
10.15 Large dataset strategies
10.16 Caching strategies
10.17 Query reduction techniques
10.18 Lab Exercise: Performance audit and optimization project

Module 12: Advanced Visualization Techniques (3 hours) (Reading Articles)

11.1 Power BI Visuals SDK overview
11.2 TypeScript basics for custom visuals
11.3 Creating simple custom visuals
11.4 Publishing to AppSource process
11.5 Custom visual certification
11.6 Conditional formatting with DAX measures
11.7 Dynamic titles and labels
11.8 Field parameters for dynamic visuals
11.9 Calculation groups for measure selection
11.10 Creating measurement switchers
11.11 Master-detail report patterns
11.12 Drill-through with multiple parameters
11.13 Tooltip pages advanced usage
11.14 Mobile report optimization
11.15 Lab Exercise: Build an award-winning executive dashboard

Module 13: Integration and Automation (3 hours) (Reading Articles)

12.1 Authentication and authorization
12.2 Programmatic report generation
12.3 Embedding Power BI in applications
12.4 Automating dataset refresh
12.5 Pushing data with APIs
12.6 Workspace automation
12.7 Triggering flows from Power BI
12.8 Data-driven alerts with Power Automate
12.9 Exporting and distributing reports
12.10 Integration with Teams and SharePoint
12.11 Automated governance workflows
12.12 Azure Synapse Analytics integration
12.13 Azure Data Lake connectivity
12.14 Azure Analysis Services vs Power BI
12.15 Lab Exercise: Build an automated reporting pipeline

Student Ratings & Reviews

No Review Yet

About Course

Course Overview

Course Objectives

Course Schedule (Sample 8-Week Plan)

Career Advancement

Roles This Course Prepares You For:

What Will You Learn?

Course Content

Module 1: Advanced DAX Fundamentals (6 hours)

1.1 Deep dive into row context and filter context

1.2 Automatic context transition in calculated columns

1.3 Context transition with CALCULATE

1.4 Iterator functions and context transition

1.5 Common context transition pitfalls and solutions

1.6 Performance implications of context transition

1.7 Filter context propagation through relationships

1.8 Understanding ALLSELECTED in depth

1.9 The expanded table concept

1.10 Arbitrary shape filters

1.11 Shadow filter context

1.12 Context modification hierarchy

1.13 KEEPFILTERS function and filter overwrite

1.14 REMOVEFILTERS vs ALL vs ALLEXCEPT

1.15 Multiple filter arguments optimization

1.16 Filter tables vs filter columns

1.17 CROSSFILTER function for dynamic filter direction

1.18 Lab Exercise: Complex sales allocation across multiple dimensions

Module 2: Advanced DAX Patterns (7 hours)

2.1 Custom fiscal calendars and implementation

2.2 Semi-additive measures (opening/closing balances)

2.3 Snapshot fact tables and point-in-time calculations

2.4 Moving averages and running totals

2.5 Period-over-period growth rates

2.6 YTD, QTD with fiscal calendars

2.7 Multiple calendars in one model

2.8 Lab Exercise: Financial reporting with fiscal calendar

2.9 Distinct count optimization techniques

2.10 Weighted averages

2.11 Median, percentile, and statistical calculations

2.12 Cumulative totals and rankings

2.13 Top N dynamic calculations with TOPN

2.14 ABC analysis implementation

2.15 Pareto principle calculations – bounce reading lessons

2.16 Creating and managing calculation groups – bounce reading lessons

2.17 Calculation group precedence – bounce reading lessons

2.18 Format string expressions in calculation groups – bounce reading lessons

2.19 Dynamic measure selection with calculation groups – bounce reading lessons

2.20 Time intelligence calculation groups – bounce reading lessons

2.21 Complex calculation group scenarios – bounce reading lessons

2.22 Performance considerations – bounce reading lessons

2.23 Lab Exercise: Build multi-dimensional time intelligence with calculation groups

Module 3: Complex DAX Functions and Patterns (6 hours)

3.1 VAR and RETURN statements

3.2 Variable scope and benefits

3.3 Performance optimization with variables

3.4 Materialization and storage engine optimization

3.5 Complex nested variable scenarios

3.6 SUMMARIZE and SUMMARIZECOLUMNS deep dive

3.7 ADDCOLUMNS and SELECTCOLUMNS

3.8 GENERATE and GENERATEALL for complex combinations

3.9 UNION, INTERSECT, and EXCEPT

3.10 ROW function for single-row tables

3.11 TREATAS function for virtual relationships

3.12 Creating virtual dimension tables

3.13 Nested iterators and performance

3.14 RANKX for dynamic ranking

3.15 EARLIER function for row-by-row calculations

3.16 Complex SUMX scenarios

3.17 Iterator optimization techniques

3.18 Lab Exercise: Customer RFM analysis with advanced DAX

Module 4: DAX Performance Optimization (5 hours)

4.1 Understanding DAX Studio

4.2 Reading query plans

4.3 Storage Engine vs Formula Engine

4.4 Scan, CallbackDataID, and other operators

4.5 Identifying bottlenecks in DAX queries

4.6 Server timings analysis

4.7 Reducing formula engine operations

4.8 Optimizing iterators

4.9 Using variables effectively for performance