Microsoft Power BI DAX and Data Modelling Training Course

Course overview

Power BI reports become unreliable when source tables are joined at the wrong grain, measures are written as calculated columns, or DAX formulas ignore filter context. Analysts often inherit semantic models that produce duplicated totals, slow visuals and inconsistent KPI definitions across departments. This five-day intermediate course addresses those issues by teaching participants to design governed Power BI data models and write DAX measures that return trustworthy business results under slicers, drill-through and row-level security.

Participants build star schemas, define fact-table grain, create and manage relationships, and distinguish measures, calculated columns and calculated tables. They learn DAX evaluation context, CALCULATE, FILTER, iterator functions, variables, time-intelligence patterns, virtual tables and calculation groups. The course also covers performance diagnosis using DAX Studio, model optimisation in Power BI Desktop, and deployment considerations in the Power BI Service. Each technique is tied to common reporting requirements such as sales variance, rolling forecasts, inventory ageing and margin analysis.

Instruction combines short demonstrations with guided model-building labs using a realistic multi-table business dataset. Participants progressively turn an untidy reporting model into a reusable semantic model, document its measures and test its output against business scenarios. They leave with a completed Power BI Desktop file containing a star-schema model, a KPI measure library, time-intelligence calculations, validation checks and a practical improvement plan for applying the methods to their own reporting environment.

Course objectives

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

• Design a star-schema Power BI model by defining fact-table grain, dimensions and conformed keys
• Configure relationships, cross-filter directions and active or inactive relationship patterns for reliable reporting
• Write reusable DAX measures using CALCULATE, variables, filter modifiers and explicit filter context
• Differentiate measures, calculated columns and calculated tables for appropriate model design
• Build time-intelligence measures for year-to-date, prior-period, rolling-period and variance analysis
• Create virtual-table and iterator-based DAX calculations for rankings, distinct counts and weighted metrics
• Diagnose slow measures and inefficient models with Performance Analyzer and DAX Studio
• Produce a documented Power BI semantic model with a tested KPI measure library and deployment recommendations

Benefits of attending

Target competencies

Who should attend

• Power BI Analysts — who need to replace fragile report calculations with governed measures and reusable models
• Business Intelligence Developers — who build semantic models and must deliver accurate, performant analytics
• Data Analysts — who prepare management dashboards and need DAX for filtered and time-based KPIs
• Finance Analysts — who require auditable variance, budget, actual and rolling-period calculations
• Data Engineers — who support Power BI datasets and need to understand downstream model and relationship design
• Reporting Managers — who standardise KPI definitions and need to review the quality of team-built Power BI models

Requirements and prerequisites

Participants should be comfortable creating basic reports in Power BI Desktop, loading data with Power Query and building simple visuals, relationships and aggregations. They should understand tables, columns, rows, data types, primary keys and basic business measures such as revenue, cost and margin. Some experience writing basic DAX measures, such as SUM or COUNTROWS, is useful but not essential. Attendees need access to a Windows laptop on which Power BI Desktop can be installed. SQL, Python, Azure services, advanced mathematics and prior use of DAX Studio or Tabular Editor are not required.

Training methodology

The instructor demonstrates each modelling and DAX pattern in Power BI Desktop before participants apply it to a shared sales, finance and operations dataset. Guided labs move from relationship design to measure authoring, time intelligence and performance tuning; participants compare outputs, inspect query behaviour and correct deliberately flawed models. Small-group case discussions focus on choosing the right calculation logic for business questions. On the final day, each participant refines a working semantic model and records a prioritised application plan for an existing workplace report or dataset.

Course outline

Day 1: Semantic model foundations and relationship design

• Power BI semantic model architecture and reporting-layer responsibilities
• Fact-table grain and the consequences of mixed-grain data
• Star schemas, snowflake schemas and denormalisation decisions
• Dimension tables, surrogate keys and conformed dimensions
• One-to-many, many-to-many and inactive relationship configurations
• Cross-filter direction, ambiguity and bidirectional filtering risks
• Date table design and marking a table as a date table

Workshop: Rebuild a flawed sales model into a star schema, define its grain and produce a relationship map with validation notes.

Day 2: DAX fundamentals and evaluation context

• Measures versus calculated columns versus calculated tables
• Row context, filter context and context transition
• Basic aggregation measures using SUM, COUNTROWS and DISTINCTCOUNT
• CALCULATE as the core filter-context transformation function
• Filter modifiers including ALL, REMOVEFILTERS, ALLEXCEPT and KEEPFILTERS
• Logical and conditional patterns with IF, SWITCH and COALESCE
• DAX variables for readable, testable measure logic

Workshop: Create a core KPI measure pack for revenue, margin, customer count and percentage contribution, then test each measure under report filters.

Day 3: Advanced calculations and time intelligence

• Iterator functions including SUMX, AVERAGEX and RANKX
• Table expressions with FILTER, VALUES, SUMMARIZE and ADDCOLUMNS
• Virtual tables and their use in business-rule calculations
• Date intelligence using DATEADD, SAMEPERIODLASTYEAR and TOTALYTD
• Rolling-period measures with DATESINPERIOD and DATESBETWEEN
• Actual versus budget and prior-period variance patterns
• Inactive date relationships using USERELATIONSHIP

Workshop: Build a management performance page with year-to-date, rolling 12-month, prior-year and budget-variance measures.

Day 4: Reusable modelling patterns and performance tuning

• Calculation groups and dynamic measure selection in Tabular Editor
• Field parameters and controlled metric switching
• Role-playing dimensions and multiple-date reporting patterns
• Model size reduction through cardinality and column-use analysis
• Power BI Performance Analyzer for visual and DAX query diagnosis
• DAX Studio server timings, query plans and measure inspection
• Measure naming, display folders, descriptions and model documentation

Workshop: Profile a slow dashboard, identify inefficient measures and model fields, then implement and document targeted performance improvements.

Day 5: Validation, security and workplace application

• Reconciling DAX results to source-system control totals
• Edge-case testing for blanks, missing dates and zero denominators
• Row-level security roles and DAX filter implications
• Power BI Service datasets, refresh considerations and endorsement
• Deployment pipelines and change-control practices for semantic models
• KPI definition templates and measure governance conventions
• Model review checklist and production-readiness assessment

Workshop: Complete a capstone model review and produce a documented KPI library, test evidence and 90-day implementation plan for a workplace use case.

Tools & standards covered

Microsoft Power BI Desktop, Microsoft Power BI Service, DAX Studio, Tabular Editor

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions