Course Introduction
Effective business intelligence depends on well-structured data models and accurate analytical calculations that transform complex organizational information into reliable insights. Microsoft Power BI provides powerful capabilities for developing enterprise data models, creating sophisticated calculations, and delivering interactive analytical reports. However, achieving accurate results and optimal performance requires more than basic report development. This course focuses on advanced Power BI data modelling and Data Analysis Expressions (DAX), equipping participants with the technical knowledge and practical skills required to design scalable analytical models, develop complex business calculations, and improve the performance of enterprise reporting solutions.
Data modelling is a fundamental component of successful Power BI development because it determines how information is organized, connected, filtered, and analyzed. Participants will explore advanced modelling principles, including dimensional modelling, star and snowflake schemas, fact and dimension tables, relationship management, and role-playing dimensions. The programme examines how to design efficient semantic models that support multiple reporting requirements while maintaining accuracy, consistency, and usability. Particular attention will be given to relationship cardinality, filter propagation, inactive relationships, bridge tables, many-to-many relationships, and the appropriate use of calculated columns and measures.
The course provides an in-depth examination of DAX as the analytical language used to develop calculations within Power BI. Participants will progress beyond basic aggregation functions to explore evaluation contexts, context transition, iterators, variables, virtual tables, and advanced filtering techniques. They will learn how to develop sophisticated measures using functions such as CALCULATE, FILTER, ALL, REMOVEFILTERS, VALUES, SUMX, and related expressions. Practical applications will include financial analysis, cumulative calculations, ranking, contribution analysis, period comparisons, forecasting support, and the development of dynamic key performance indicators that respond accurately to user selections.
Advanced analytical reporting requires a clear understanding of time intelligence and complex business calculation requirements. Participants will examine techniques for year-to-date, quarter-to-date, month-to-date, rolling periods, year-over-year comparisons, moving averages, and cumulative performance analysis. The course will also address custom calendar tables, fiscal calendars, working-day calculations, and scenarios where standard time intelligence functions require modification. Through practical exercises, participants will learn to develop reusable DAX measures that support consistent financial, operational, sales, and strategic performance reporting across different organizational functions.
The programme also emphasizes model optimization, calculation efficiency, and the management of enterprise-scale Power BI solutions. Participants will explore storage modes, Import and DirectQuery considerations, composite models, aggregation tables, incremental refresh, and semantic model optimization. They will examine performance analysis using Power BI Performance Analyzer and DAX Studio, where available, alongside techniques for reducing unnecessary model complexity and improving query execution. Emerging developments involving Microsoft Fabric, Direct Lake, calculation groups, dynamic format strings, and AI-assisted analytical development will also be considered, with attention to security, governance, and maintainability.
By the end of the programme, participants will be able to design advanced Power BI data models, write sophisticated DAX formulas, troubleshoot complex calculations, and optimize analytical solutions for performance and scalability. They will gain the confidence to develop reusable measures, manage complex relationships, implement advanced time intelligence, and support enterprise reporting requirements. These capabilities will enable organizations to improve analytical accuracy, reduce reporting inconsistencies, strengthen decision-support systems, and maximize the value of Microsoft Power BI as a strategic business intelligence platform.
Duration
5 days
Who Should Attend
This course is designed for professionals with foundational Power BI knowledge who want to develop advanced data modelling and DAX capabilities, including:
-
Power BI developers and specialists
-
Business intelligence developers and analysts
-
Data analysts and senior reporting analysts
-
Data modelers and data architects
-
Business analysts and management information specialists
-
Financial analysts and management accountants
-
Business intelligence managers and team leaders
-
Database developers and administrators
-
Microsoft data platform professionals
-
Enterprise reporting and performance management specialists
-
Data scientists and analytics engineers
-
Management consultants and business intelligence advisers
-
Finance and operational reporting professionals
-
Enterprise architects and information systems specialists
-
Professionals responsible for enterprise Power BI semantic models
Course Objectives
By the end of this course, participants will be able to:
-
Design advanced Power BI data models using dimensional modelling, star schemas, fact tables, dimension tables, and appropriate relationship structures.
-
Evaluate and manage complex data relationships, including inactive relationships, many-to-many relationships, bridge tables, and advanced filter propagation scenarios.
-
Apply advanced DAX evaluation concepts, including row context, filter context, context transition, and the interaction of calculation functions within semantic models.
-
Develop sophisticated DAX measures using CALCULATE, FILTER, iterators, variables, virtual tables, and advanced aggregation functions to solve complex business problems.
-
Implement advanced time intelligence calculations, including rolling periods, fiscal year reporting, year-over-year comparisons, cumulative totals, and moving averages.
-
Create dynamic analytical measures for ranking, contribution analysis, percentage of total, variance reporting, segmentation, and context-sensitive performance evaluation.
-
Apply calculation groups, dynamic format strings, and reusable measure design techniques to improve consistency and maintainability across enterprise reports.
-
Optimize semantic model performance using appropriate storage modes, aggregation strategies, incremental refresh, and efficient DAX calculation techniques.
-
Diagnose and troubleshoot complex DAX calculations using performance analysis tools, query inspection, and systematic evaluation of filter and calculation behavior.
-
Develop scalable Power BI analytical solutions that integrate advanced data modelling, accurate calculations, security controls, and enterprise reporting requirements.
Comprehensive Course Outline
Module 1: Advanced Power BI Architecture and Data Modelling Principles
-
Reviewing Power BI architecture, semantic models, storage engines, formula engines, and the relationship between data modelling and analytical performance.
-
Applying advanced dimensional modelling principles, including star schemas, snowflake schemas, fact tables, dimension tables, and granularity management.
-
Designing conformed dimensions, role-playing dimensions, degenerate dimensions, and bridge tables for complex enterprise reporting requirements.
-
Evaluating model design alternatives to improve calculation accuracy, reduce unnecessary complexity, and support scalable business intelligence solutions.
Module 2: Advanced Relationships and Filter Propagation
-
Managing one-to-one, one-to-many, and many-to-many relationships while understanding their implications for analytical accuracy.
-
Applying active and inactive relationships using USERELATIONSHIP and other techniques to support alternative analytical perspectives.
-
Understanding single-direction and bidirectional filtering, ambiguous filter paths, and the effects of relationship configuration on DAX calculations.
-
Implementing bridge tables, virtual relationships, and advanced relationship techniques for complex business data structures.
Module 3: Advanced DAX Fundamentals and Evaluation Contexts
-
Understanding the DAX formula engine, expression evaluation, data lineage, and the differences between calculated columns, measures, and calculated tables.
-
Examining row context, filter context, context transition, and their interaction when evaluating complex analytical expressions.
-
Applying CALCULATE, CALCULATETABLE, FILTER, ALL, ALLEXCEPT, REMOVEFILTERS, and KEEPFILTERS to control analytical filter behavior.
-
Using variables, nested expressions, and reusable calculation patterns to improve DAX readability, accuracy, and maintainability.
Module 4: Advanced DAX Functions, Iterators, and Virtual Tables
-
Applying iterator functions such as SUMX, AVERAGEX, MINX, MAXX, and COUNTX to perform row-by-row analytical calculations.
-
Creating and manipulating virtual tables using ADDCOLUMNS, SUMMARIZE, SUMMARIZECOLUMNS, SELECTCOLUMNS, and related table functions.
-
Applying VALUES, DISTINCT, UNION, INTERSECT, EXCEPT, and CROSSJOIN to develop advanced analytical table expressions.
-
Combining table functions, filtering techniques, and context manipulation to solve complex business calculations and reporting requirements.
Module 5: Advanced Time Intelligence and Period-Based Analysis
-
Developing year-to-date, quarter-to-date, and month-to-date calculations using appropriate DAX time intelligence functions.
-
Creating year-over-year, month-over-month, quarter-over-quarter, and prior-period comparison measures for management reporting.
-
Implementing rolling totals, moving averages, cumulative calculations, and dynamic period analysis for operational and financial performance.
-
Designing custom calendar tables, fiscal calendars, working-day calculations, and time intelligence solutions for non-standard reporting periods.
Module 6: Complex Business Calculations and Analytical Measures
-
Developing dynamic ranking, segmentation, percentile, and top-N calculations using RANKX and related DAX functions.
-
Calculating percentages of totals, market shares, contribution margins, variance percentages, and performance ratios under changing filter conditions.
-
Applying advanced financial calculations for profitability, budget versus actual analysis, cash flow monitoring, and financial performance evaluation.
-
Building dynamic KPIs, conditional calculations, exception indicators, and comparative measures that respond accurately to report interactions.
Module 7: Advanced Semantic Model Features and Reusable Calculation Design
-
Implementing calculation groups to centralize reusable calculation logic and reduce duplication across measures.
-
Applying dynamic format strings to display currencies, percentages, units, and other analytical values according to business requirements.
-
Using field parameters, dynamic measure selection, and disconnected tables to create flexible analytical experiences for report users.
-
Managing perspectives, display folders, measure organization, descriptions, and model documentation to improve enterprise usability and governance.
Module 8: Power BI Performance Optimization and Troubleshooting
-
Understanding VertiPaq storage engine principles, column compression, cardinality, dictionary encoding, and their effects on model performance.
-
Optimizing DAX expressions by reducing unnecessary iterations, controlling filter operations, and selecting efficient calculation patterns.
-
Using Performance Analyzer, DAX Studio, and other available tools to investigate query execution, calculation bottlenecks, and model inefficiencies.
-
Evaluating Import, DirectQuery, composite models, aggregation tables, incremental refresh, and other approaches for managing larger datasets.
Module 9: Enterprise Data Integration, Security, and Emerging Technologies
-
Integrating advanced Power BI models with SQL databases, cloud data sources, enterprise systems, and Microsoft Fabric data environments.
-
Exploring Direct Lake, lakehouse architectures, semantic model development, and modern data platform capabilities for enterprise analytics.
-
Implementing row-level security, object-level security, dynamic security roles, and appropriate data access controls within Power BI models.
-
Examining AI-assisted analytical development, automated insights, governance requirements, and responsible adoption of emerging Microsoft data technologies.
Module 10: Practical Advanced DAX and Data Modelling Project
-
Analyzing a complex business dataset to identify modelling requirements, define table structures, establish relationships, and determine appropriate calculation logic.
-
Developing an advanced semantic model using dimensional modelling techniques, relationship configurations, and optimized data structures.
-
Creating sophisticated DAX measures for time intelligence, financial analysis, dynamic ranking, performance evaluation, and management reporting.
-
Testing and optimizing the completed solution through calculation validation, performance analysis, security configuration, and practical reporting scenarios.
Training Approach
This course will be delivered by our skilled trainers who have vast knowledge and experience as expert professionals in the fields. The course is taught in English and through a mix of theory, practical activities, group discussion and case studies. Course manuals and additional training materials will be provided to the participants upon completion of the training.
Tailor-Made Course
This course can also be tailor-made to meet organization requirement. For further inquiries, please contact us on: Email: training@upskilldevelopment.com Tel: +254 721 331 808
Training Venue
The training will be held at our Upskill Training Centre. We also offer training for a group (at a discount of 10% to 50%) at requested location all over the world. The Onsite course fee covers the course tuition, training materials, two break refreshments, buffet lunch, airport transfers, Upskill gift package, and guided tour.
Visa application, travel expenses, dinners, accommodation, insurance, and other personal expenses are catered by the participant
Certification
Participants will be issued with Upskill certificate upon completion of this course.
Airport Pickup and Accommodation
Airport pickup and accommodation is arranged upon request. For booking contact our Training Coordinator through Email: training@upskilldevelopment.com, +254 721 331 808
Terms of Payment:
Unless otherwise agreed between the two parties’ payment of the course fee should be done 3 working days before commencement of the training so as to enable us to prepare better.
