Power BI – Analytics & BI Expert (AI L2) — Semantic Models | DAX | Power Query | Copilot | Direct Lake | Deployment Pipelines

Duration: 56 Hours
Target Level: L2 (Intermediate)
Delivery Mode: Classroom / Online (ILT)
AI Platform: Power BI Copilot + Azure OpenAI

Course Overview

This course equips Data Engineering & Analytics professionals with core Power BI analytics and BI skills and the AI layer required for L2 proficiency. It is designed for practitioners who already build enterprise reporting and want to master semantic modelling, DAX, and governed deployment alongside modern Gen AI capabilities — specifically Power BI Copilot and Azure OpenAI applied to analytics workflows.

The course follows a learn-by-doing philosophy. Every module includes hands-on labs and flexible case study scenarios that trainers can adapt to their domain context.

L2 Outcome Statement

• Design enterprise semantic models (star schema, composite, aggregations) on Power BI
• Build robust data preparation with Power Query and Dataflows Gen2 and optimize refresh
• Author advanced DAX for analytics, time intelligence and performance
• Implement governance: RLS/OLS, sensitivity labels and deployment pipelines
• Integrate Power BI Copilot and Azure OpenAI into reporting and analytics workflows
• Contribute to internal BI accelerators and AI-assisted analytics POCs

Prerequisites

• 1–2 years of hands-on experience in a Power BI / BI / analytics role
• Working knowledge of Power BI Desktop and Service (reports, datasets, workspaces)
• Basic data modelling concepts (tables, relationships, keys)
• Basic SQL proficiency
• Understanding of reporting and ETL/ELT concepts (batch vs incremental)

Helpful but not mandatory: Exposure to DAX and Power Query (M) · Any Gen AI/LLM concept · Familiarity with a cloud data platform (Azure/Microsoft Fabric)

Course Structure at a Glance

Analytics & BI modules build the reporting foundation. Gen AI modules apply Copilot and Azure OpenAI in a BI/analytics context. Both tracks run in sequence and are equally mandatory.

Detailed Course Modules

Part A — Power BI Analytics & BI Foundation

Module 1: Data Modelling & Semantic Layer Foundations | 8 hrs

Topics Covered: Storage modes (Import, DirectQuery, Direct Lake, Composite) · Star schema design (facts, dimensions, role-playing & slowly changing dimensions) · Relationships (cardinality, cross-filter direction, USERELATIONSHIP) · Aggregations & composite models · Semantic model best practices · Calculation groups (Tabular Editor)

Hands-On Labs: Build a star-schema semantic model from raw source tables · Configure aggregations and a composite model over a large fact table · Implement role-playing dimensions and calculation groups in Tabular Editor

Case Study: Design a governed star schema serving Sales, Finance, and Ops, consolidating departmental reports into one enterprise semantic model.

Module 2: Data Preparation & Transformation with Power Query | 8 hrs

Topics Covered: Power Query M (queries, applied steps, custom functions) · Connectors and the on-premises data gateway · Query folding (diagnostics, performance) · Dataflows Gen2 & reusable transformation layers · Incremental refresh and partitioning · Data quality and profiling

Hands-On Labs: Build a multi-source Power Query pipeline with custom M functions · Configure incremental refresh on a large fact table · Create a reusable Dataflow Gen2 and verify query folding via diagnostics

Case Study: Design a reusable bronze → silver preparation layer, standardizing data prep across 5 report teams using shared dataflows.

Module 3: Advanced DAX & Analytical Calculations | 8 hrs

Topics Covered: DAX evaluation context (row vs filter, context transition) · CALCULATE, FILTER, iterators, variables · Time intelligence (YTD/MTD, period-over-period, custom calendars) · Calculation groups for reusable measure logic · What-if parameters · DAX performance (DAX Studio, VertiPaq Analyzer)

Hands-On Labs: Author time-intelligence and context-transition measures · Build calculation groups for currency/period switching · Profile and optimize a slow measure using DAX Studio

Case Study: Design a measure layer with what-if scenario analysis for an executive KPI model with period comparisons and targets.

Module 4: Governance, Security & Enterprise Deployment | 6 hrs

Topics Covered: Row-level (RLS) and object-level (OLS) security — static & dynamic · Sensitivity labels and Microsoft Purview integration · Workspaces, apps, and deployment pipelines (Dev/Test/Prod) · Git integration for Power BI projects (PBIP) · Capacity/Fabric admin · Certified & promoted datasets, lineage

Hands-On Labs: Implement dynamic RLS using a security table and DAX · Set up a Dev/Test/Prod deployment pipeline with rules · Configure Git integration for a PBIP project

Case Study: Design RLS, sensitivity labels, and lineage for a BFSI org enforcing data governance for regulatory reporting.

Part B — Gen AI for BI & Analysts

Module 5: AI Foundations for BI – Azure OpenAI & Copilot | 6 hrs

Topics Covered: Azure OpenAI vs OpenAI API · Model catalogue relevance to analytics · Prompt design for BI/analytics use cases · Copilot for Power BI architecture and capacity prerequisites (F64+/P SKU) · Token/cost management · Responsible AI in analytics

Hands-On Labs: Deploy a GPT-4o model in Azure OpenAI and call via REST · Write a prompt that generates DAX from a natural-language metric request · Enable Copilot prerequisites and verify capacity

Case Study: Build a prompt reading model metadata and generating a business glossary for auto-documenting a semantic model.

Module 6: Copilot in Power BI – Report & Narrative Generation | 6 hrs

Topics Covered: Copilot report-page generation and summaries · DAX assistance and measure explanation · Smart Narratives and NL summaries · Q&A visual (tuning synonyms, linguistic schema) · Prepping a model so Copilot performs well · Governance and usage monitoring for Copilot

Hands-On Labs: Use Copilot to generate a report page and narrative from a model · Tune Q&A synonyms and linguistic schema for accurate NL answers · Add metadata/descriptions to improve Copilot output quality

Case Study: Design a Copilot/Q&A-ready model with curated metadata for a self-service natural-language analytics layer.

Module 7: Embeddings, NL Q&A & RAG over Analytics Content | 8 hrs

Topics Covered: Embeddings (semantic vs keyword search) · Vector stores (Azure AI Search/pgvector) · Chunking strategies for reports and data dictionaries · RAG architecture grounded on BI content · Surfacing RAG answers into Power BI (custom visual/embedded app) · Evaluating RAG (precision@k, faithfulness)

Hands-On Labs: Generate embeddings for a data-dictionary + report-spec corpus · Build a RAG flow (docs → vector store → Azure OpenAI → grounded answer) · Embed the RAG assistant alongside a Power BI report

Case Study: Design a RAG system handling 500+ docs/metric definitions for an “Ask the metrics” assistant over the BI semantic layer and glossary.

Module 8: AI-Augmented Dashboards, Deployment & Monitoring | 6 hrs

Topics Covered: Surfacing LLM/AI outputs into dashboards and semantic models · AI-enriched columns feeding reports · Embedding Copilot/RAG assistants (Power BI Embedded basics) · Deployment pipelines + Git for the AI-assisted solution · Monitoring (capacity metrics, refresh, Copilot usage) · Cost and quota governance

Hands-On Labs: Surface AI-enriched columns into a dashboard · Deploy the AI-assisted report via pipeline + Git · Set up monitoring for refresh, capacity, and Copilot usage

Case Study: Combine a semantic model + Copilot + RAG + governed deployment as a capstone, productionizing an AI-assisted analytics app.

Tools & Environment

• Power BI Desktop
• Power BI Service / Fabric
• Tabular Editor
• DAX Studio
• Dataflows Gen2
• Azure OpenAI
• Azure AI Search
• VS Code + Git