AI & DIGITAL SKILLS MAGAZINE Learn from beginner to expert
Contact EDITION 2026
ISHRAQA7 — The practical digital learning magazine
ISHRAQA 7 ACADEMY · Excel & Data

Power BI: from data preparation to advanced analysis

Twelve progressive chapters to master Power Query, star-schema modelling, DAX (CALCULATE, time intelligence), visualisation, interactivity, publishing and row-level security, built around a complete sales analysis project.

  • LevelBeginner → Advanced
  • Course programme12 modules
  • Course filesPDF · XLSX · CSV · ZIP · TXT · JSON

Also available in العربية Français

This ISHRAQA7 Academy course takes you from Power BI fundamentals to advanced methods of modelling, DAX calculation, visualisation and governance. Every chapter builds on the same practical project, the business performance analysis of an office equipment distributor, and ends with a worked exercise and three assessment questions.

Learning path

Level Chapters
Beginner 1. Introduction · 2. Power BI Desktop · 3. Data sources · 4. Power Query
Intermediate 5. Modelling · 6. DAX fundamentals · 9. Visualisation
Advanced 7. Intermediate DAX · 8. Advanced DAX · 10. Interactive reports · 11. Publishing and security · 12. Final project

Course files

The files are available in the download section of this page: full manual, exercise workbook, worked answers, DAX guide, model documentation and final project guide as PDF; CSV data and an Excel workbook; Power Query scripts and DAX measures as text; a report theme; and a complete archive of the practical resources.

Level 1 · Beginner — Chapter 1

Introduction to Power BI and Business Intelligence

Before opening the software, you need to know what it is for: turning scattered data into reliable indicators that are shared and understood by decision-makers. This chapter sets out the vocabulary of Business Intelligence and presents the architecture of Power BI.

From data to decision

Business Intelligence covers the methods and tools that collect an organisation’s data, make it reliable and present it in a form that supports decisions. The starting point is almost always the same: exports from business software, Excel files sent by different departments, customer and product lists kept separately. Everyone calculates “their own” revenue, and meetings start with an argument about the right number instead of the actions to take.

A BI tool solves this by setting up a single chain: data is read at the source, cleaned with rules written once, linked together, then calculated by shared formulas. The same indicator gives the same result for everyone.

Essential vocabulary

Term Definition Example in the case study
Data An elementary recorded fact An order line: product, quantity, price
Dimension An axis of analysis that describes the facts Customer, product, region, date
Measure An aggregated calculation over facts Net sales, number of orders
Indicator A measure interpreted against a reference Sales growth versus the previous year
KPI An indicator tied to a target and tracked regularly Monthly target attainment by region

A good KPI is defined in writing (formula, scope, source), tied to a target and actionable: someone knows what to do when it deteriorates.

The architecture of Power BI

  • Power BI Desktop: a free Windows application. This is where you build the .pbix file: connections, Power Query transformations, data model, DAX measures and report pages.
  • Power BI Service (app.powerbi.com): the online platform where reports are published, shared, refreshed and secured. Sharing with colleagues generally requires a Pro licence or Premium/Fabric capacity.
  • Mobile apps: viewing reports on phones and tablets.
  • On-premises data gateway: software installed on a company server that lets the Service refresh sources located on the internal network.

Offers and product names change regularly (integration into Microsoft Fabric, for example). Always check current licensing terms on Microsoft’s website before deploying a solution.

The four stages of a Power BI project

  1. Connect to sources: CSV and Excel files, SQL databases, online services.
  2. Transform data with Power Query: types, cleaning, merging tables. Each step is recorded and replayed at every refresh.
  3. Model: link the tables (facts and dimensions) and write measures in the DAX language.
  4. Visualise and share: build report pages, publish to the Service, manage access and refresh.

The course case study

Throughout the twelve chapters, you work on the ISHRAQA7 Academy practical project: business performance analysis of a distributor of office equipment and supplies. The practice dataset covers 2024 and 2025: 3,307 orders, 8,472 order lines, 120 customers, 30 products, four regions, three sales channels and monthly 2025 targets by region. Amounts are in Moroccan dirhams (MAD), excluding tax.

Level 1 · Beginner — Chapter 2

Getting started with Power BI Desktop

Power BI Desktop brings the whole report cycle into a single window. Knowing its three views and its panes well saves time looking for commands and lets you organise a clean file from the start.

Installation

Power BI Desktop is free. You can install it from the Microsoft Store (automatic updates, recommended) or with the installer downloaded from Microsoft’s website. A new version is released every month, so screens and menu labels may differ slightly from those described here.

Before the first import, open File › Options and settings › Options › Regional settings (under “Current file”). The course files use a dot as decimal separator and ISO dates such as 2025-03-15. If Windows uses a locale with a decimal comma, Power Query may read 12.5 as text or a date: you fix this in Power Query by specifying the en-US locale when changing the type (chapter 4).

The three views

View (left bar) Purpose What you do there
Report Laying out visuals Charts, cards, slicers, pages
Table (Data) Checking the contents of loaded tables Inspect rows, create calculated columns, format
Model Diagram of tables and relationships Create and check relationships, organise the model

The Power Query Editor opens in a separate window with Home › Transform data. This is where data is prepared before it is loaded into the model.

The panes of the Report view

  • Visualizations: choose the visual type, its fields (axes, values, legend), formatting and the Analytics pane (trend lines, constant lines).
  • Data: the list of tables, columns and measures in the model. A calculator icon marks a measure.
  • Filters: filters at visual, page or report level.

Demonstration: a first report in five minutes

  1. Home › Get data › Text/CSV, choose orders.csv in the data folder of the course archive.
  2. In the preview, check the delimiter (comma) and the file origin (65001: Unicode UTF-8), then click Load.
  3. In Report view, click the Card visual and drag order_id into its field: choose the Count aggregation. The visual shows 3,307, the total number of orders, all statuses included.
  4. Add a Clustered column chart: status on the X-axis, count of order_id on the Y-axis.
  5. Save the file as sales-ishraqa7.pbix.

This first report is deliberately basic: it counts every order, including cancelled ones. The full model, built from chapter 3 onwards, will apply the calculation rule defined in chapter 1.

Organising a .pbix file from the start

  • Rename tables with business names: Sales, Customers, Products, Calendar.
  • Group measures in a dedicated table (for example _Measures) and arrange them in display folders.
  • Hide technical columns (join keys) from Report view.
  • Name each page after the question it answers: “Overview”, “Regions”, “Products”.
  • Save a dated copy before any major transformation.

Level 1 · Beginner — Chapter 3

Connecting to data sources

The quality of a report starts with its connection to the sources. This chapter shows how to import CSV and Excel files, connect to a SQL database, parameterise a source folder and check what has actually been loaded.

The case study files

File Content Grain (one row =)
order_lines.csv Order lines: product, quantity, price, discount one order line
orders.csv Order headers: date, customer, sales rep, channel, status, ship date one order
customers.csv, products.csv, sales_reps.csv Descriptions of customers, products and sales reps one customer, product, rep
regions.csv, segments.csv, channels.csv, categories.csv Label tables one code
targets.csv Monthly 2025 targets by region one month × one region

The same content is available in the pbi-data workbook (one sheet per table), to practise connecting to Excel.

Importing a CSV file

  1. Home › Get data › Text/CSV, select order_lines.csv.
  2. In the preview window: File origin = 65001: Unicode (UTF-8), Delimiter = Comma, Data type detection = Based on first 200 rows.
  3. Click Transform data rather than Load: you check the types before loading.
  4. In Power Query, check each column’s type icon: quantity as whole number, unit_price and discount_rate as decimal number, order_id as text.

Importing an Excel workbook

Get data › Excel workbook opens the Navigator. It lists the sheets and tables in the workbook. Always prefer a formatted table (Insert › Table in Excel): its boundaries follow added rows, whereas a sheet may contain titles, totals or blank cells that disrupt the import.

Connecting to a SQL database

For SQL Server: Get data › SQL Server, enter the server and database. For PostgreSQL or MySQL, choose the matching connector (a driver may be required). There are two connectivity modes:

Import DirectQuery
Data Copied and compressed into the file Left in the database, queried by every visual
Performance Very fast once loaded Depends on the database and network
Freshness As of the last refresh Near real time
Features All transformations and DAX functions Some transformations and functions are limited

For the sales analysis in this course, Import is the right choice: moderate volume, daily refresh is enough. The ISHRAQA7 Academy SQL & Databases course provides a PostgreSQL database of the same case study if you want to practise the SQL connection.

Parameterising the source folder

A report pointing to C:\Users\salma\Desktop\sales.csv only works on Salma’s computer. So you create a parameter:

  1. In Power Query: Home › Manage parameters › New parameter. Name: DataFolder, type Text, current value: C:\ISHRAQA7\powerbi\data\ (with the trailing backslash).
  2. Open the Advanced Editor of each query and replace the hard-coded path with DataFolder & "order_lines.csv".
  3. To move the project, you only change the parameter value.
let
    Source = Csv.Document(
        File.Contents(DataFolder & "order_lines.csv"),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Types = Table.TransformColumnTypes(Headers, {
        {"order_id", type text}, {"line_no", Int64.Type}, {"product_id", type text},
        {"quantity", Int64.Type}, {"unit_price", type number}, {"discount_rate", type number}
    }, "en-US")
in
    Types

The last argument "en-US" of Table.TransformColumnTypes sets the culture used to read numbers: the dot is read as the decimal separator whatever the Windows language. The course file pbi-power-query contains every query in full.

Checking the load

  • Turn on View › Column quality, Column distribution and Column profile, and set “Column profiling based on entire data set” in the status bar.
  • Compare the row count with the source: 8,472 rows for order_lines, 3,307 for orders.
  • Check that no column shows errors (red bar in column quality).

Level 1 · Beginner — Chapter 4

Cleaning and transforming with Power Query

Real data is rarely ready to use. Power Query lets you fix the defects of an export once and for all, by recording steps that are replayed at every refresh. This chapter processes a deliberately imperfect extract and introduces the M language.

How applied steps work

Every action in Power Query adds a line to the Applied steps pane. Together they form a script in the M language, replayed from top to bottom at each refresh. You can click a step to see the intermediate result, rename it (right-click › Rename) or delete it. Naming steps with an action verb (“Remove duplicates”, “Fix prices”) makes the script readable by a colleague.

The file to clean

orders_raw_export.csv is a fourth-quarter 2025 extract exported by a legacy system. It contains 1,174 rows and eight known defects:

Defect Example Correction rule
Duplicate rows the same row exported twice Remove exact duplicates
Two date formats 2025-10-03 and 03/10/2025 Read ISO format, otherwise day/month/year
Badly entered city " CASABLANCA " Trim spaces, apply “Capitalize Each Word”
Quantity as text 4 pcs Keep digits only
Negative quantity -2 Confirmed sign error: absolute value
Decimal comma 2450,00 Replace the comma with a dot before converting
Price multiplied by 100 245000.00 instead of 2450.00 If price > 10 × list price, divide by 100
Missing customer empty cell Look up the customer from the order number

Each rule is a business decision: you do not “guess” a correction, you apply a rule validated by the department that knows the data. Here, the sales department confirmed that negative quantities are entry errors and that the orders table is the reference for the customer.

Step-by-step demonstration

  1. Import the file keeping every column as Text (delete the automatic “Changed Type” step): you convert after cleaning.
  2. Home › Remove rows › Remove duplicates on all columns: 1,174 − 28 = 1,146 rows.
  3. Column city: Transform › Format › Trim, then Capitalize Each Word.
  4. Column quantity: Add column › Custom column with the formula below, then remove the old column.
  5. Column unit_price: Replace values “,” with “.”, then type Decimal number using the en-US locale (Data type › Using locale).
  6. Merge queries with Products on product_id (left outer join), expand list_price, then create the corrected price.
  7. Merge with Orders on order_id to retrieve the reference customer when customer_id is empty.
  8. Set the final types and remove the working columns.
// Quantity: digits only, which also drops the minus sign
= Number.Abs(Number.From(Text.Select([quantity], {"0".."9"})))

// Date: ISO format, otherwise day/month/year
= try Date.FromText([order_date], [Format = "yyyy-MM-dd", Culture = "en-US"])
  otherwise Date.FromText([order_date], [Format = "dd/MM/yyyy", Culture = "fr-FR"])

// Price: correct entries multiplied by 100
= if [unit_price] > 10 * [list_price] then [unit_price] / 100 else [unit_price]

// Customer: value from the Orders table when the cell is empty
= if [customer_id] = null or Text.Trim([customer_id]) = "" then [ref_customer_id] else [customer_id]

Note that Text.Select keeps only digits: the minus sign disappears, which applies the absolute value directly. Number.Abs remains useful if the column has already been converted to a number.

Merge and append

  • Merge (Table.NestedJoin) matches two tables on a key, like a VLOOKUP: you add columns. The join kind matters: left outer keeps every row of the first table.
  • Append (Table.Combine) stacks tables with the same structure, such as several monthly exports: you add rows.

For the course model, the Sales fact table is obtained by merging order_lines with orders (date, customer, sales rep, channel, status) and adding the column net_amount = quantity × unit_price × (1 − discount_rate) as well as delivery_days, the gap between ship date and order date.

Checking the result

A transformation is only validated when a numeric check confirms it. On the cleaned extract you should get 1,146 rows, 12 distinct cities and a total net amount of 3,998,296.35 MAD: exactly the total of the matching rows of the order_lines table (non-cancelled orders of the fourth quarter of 2025).

Level 2 · Intermediate — Chapter 5

Modelling data: relationships and the star schema

The data model is the heart of a Power BI report. Well-designed relationships make filters work and keep formulas simple; a badly built model produces wrong numbers without any error message.

Facts and dimensions

A fact table records measurable events: here, each order line with its quantity, price and discount. It is long (many rows) and narrow (mostly keys and numbers). A dimension table describes the context of the facts: who (customer, sales rep), what (product, category), where (region), when (date), how (channel). It is short and contains labels.

The case-study star schema

Salesfacts · order line

CalendarProductsCustomersChannelsSalesRepsTargetsRegions_Measures

Model diagram: the Sales table at the centre, filtered by the dimensions (one-to-many relationships). Regions filters Customers and Targets; Calendar filters Sales and Targets (dashed line).
Relationship “One” side “Many” side
Products → Sales Products[product_id] Sales[product_id]
Customers → Sales Customers[customer_id] Sales[customer_id]
SalesReps → Sales SalesReps[rep_id] Sales[rep_id]
Channels → Sales Channels[channel_code] Sales[channel_code]
Calendar → Sales Calendar[Date] Sales[order_date]
Regions → Customers Regions[region_code] Customers[region_code]
Regions → Targets Regions[region_code] Targets[region_code]
Calendar → Targets Calendar[Date] Targets[month]

The Regions table is a shared dimension: a “Region” slicer filters both sales (through customers) and targets. This is what lets you compare sales and targets in the same visual. Category and segment labels are merged into Products and Customers in Power Query, to avoid two-column dimensions.

Cardinality and cross-filter direction

  • One-to-many (1:*): the normal cardinality between a dimension and a fact table. The column on the “one” side must contain unique values.
  • Single cross-filter direction: the dimension filters the facts, never the reverse. Bidirectional filtering creates ambiguity and slows the model down; keep it for specific, documented cases.
  • Many-to-many: avoid it until you master its effects; a bridge table is usually preferable.

The Calendar table

Time intelligence functions (chapter 8) require a continuous date table: one day per row, no gaps, covering every year in the facts. You create it in DAX: Modeling › New table.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "YearMonth", YEAR ( [Date] ) * 100 + MONTH ( [Date] )
)

Then: Table tools › Mark as date table, column Date. Sort the Month column by MonthNum (Column tools › Sort by column), otherwise months appear in alphabetical order. Finally, turn off Auto date/time (Options › Current file › Data load): it creates a hidden date table for every date column and bloats the model.

Demonstration: building the model

  1. In Model view, drag Products[product_id] onto Sales[product_id]. In the dialog, check: cardinality One to many, cross-filter direction Single, relationship active.
  2. Create the other seven relationships in the table in the same way.
  3. Hide the key columns of the Sales table from Report view (right-click › Hide in report view): you filter by dimensions, not by keys.
  4. Check: a table with Regions[region_name] and the sum of Sales[net_amount] should show four rows and a total equal to the unfiltered sum. A “(Blank)” row reveals a key with no match.

Level 2 · Intermediate — Chapter 6

DAX fundamentals: measures and evaluation context

DAX (Data Analysis Expressions) is Power BI’s calculation language. It looks like Excel formulas, but it calculates over tables and filters, not cells. Understanding context is the key to everything that follows.

Measure or calculated column?

Calculated column Measure
Calculated Row by row, at load time On the fly, according to the visual’s filters
Stored Yes, uses memory No
Typical use Categorising a row (price band, label) Aggregating: total, average, rate, comparison

Rule of thumb: if the result depends on what the user filters (period, region), it is a measure. A calculated column classifies a row and can then be used as an axis or a filter. A transformation that depends only on the row’s columns is best done in Power Query.

The first measures

Create an empty table _Measures (Home › Enter data, one column, no rows), then: Home › New measure.

Net Sales =
CALCULATE (
    SUM ( Sales[net_amount] ),
    Sales[status] = "delivered"
)

Order Count =
CALCULATE (
    DISTINCTCOUNT ( Sales[order_id] ),
    Sales[status] = "delivered"
)

Average Order Value =
DIVIDE ( [Net Sales], [Order Count] )

CALCULATE modifies the filter context (studied in detail in chapter 7): here it adds the condition “delivered order”, in line with the written definition from chapter 1. DIVIDE replaces the / operator: it returns a blank instead of an error when the denominator is zero.

Filter context

A measure has no single value: it is evaluated in each cell of a visual with that cell’s filters. In a “Region × Year” table, the cell “Centre, 2025” calculates [Net Sales] only for the Sales rows linked to the Centre region and to 2025 dates. The column total is recalculated with only the year filter: it is not a sum of the cells.

This mechanism explains a result that is often misunderstood: the total of an [Average Order Value] column is not the sum of the regional averages, but the average of all orders. That is the correct result.

Iterators: SUMX and margin

The cost of a line depends on the product’s unit cost, held in another table. SUMX walks through a table row by row (row context) and sums an expression; RELATED fetches the value from the related dimension.

Cost of Sales =
CALCULATE (
    SUMX ( Sales, Sales[quantity] * RELATED ( Products[unit_cost] ) ),
    Sales[status] = "delivered"
)

Gross Margin = [Net Sales] - [Cost of Sales]

Margin % = DIVIDE ( [Gross Margin], [Net Sales] )

Formatting and organisation

  • Format: Net Sales as a whole number with a thousands separator; Margin % as a percentage with one decimal.
  • Display folders (Model view › Properties › Display folder): “1. Sales”, “2. Margin”, “3. Time”, “4. Targets”.
  • A description for each measure (Description field): it appears as a tooltip in the Data pane.

Control values (year 2025)

Measure Expected value (filter: year 2025)
Net Sales 15,097,590 MAD
Order Count 1,628
Average Order Value 9,274 MAD
Gross Margin 5,775,781 MAD
Margin % 38.3%

These values were calculated independently, with Python, on the course files. If your measure gives a different result, check the status filter, the relationships and the type of the discount_rate column.

Level 3 · Advanced — Chapter 7

Intermediate DAX: CALCULATE, FILTER and iterators

CALCULATE is DAX’s most important function: it modifies the filter context in which a measure is evaluated. This chapter explains exactly what it does, how to combine it with FILTER, ALL and iterators, and how to reason about DAX’s two contexts.

What CALCULATE does

CALCULATE ( expression, filter1, filter2, … ) evaluates the expression in a modified filter context. Each filter argument replaces the existing filter on the column or columns concerned, then the filters are combined (logical AND).

Online Sales =
CALCULATE ( [Net Sales], Channels[channel_code] = "WEB" )

Online Sales Centre =
CALCULATE ( [Net Sales], Channels[channel_code] = "WEB", Regions[region_code] = "C" )

If a slicer already filters the “Partners” channel, [Online Sales] still shows online sales: the CALCULATE filter replaces the slicer’s filter on the same column. To keep the existing filter and intersect it, use KEEPFILTERS:

Online Sales (kept filter) =
CALCULATE ( [Net Sales], KEEPFILTERS ( Channels[channel_code] = "WEB" ) )

Removing filters: shares and contributions

Region Share of Sales =
DIVIDE (
    [Net Sales],
    CALCULATE ( [Net Sales], REMOVEFILTERS ( Regions ) )
)

Share of Selection =
DIVIDE (
    [Net Sales],
    CALCULATE ( [Net Sales], ALLSELECTED ( Products[category_name] ) )
)

REMOVEFILTERS ( Regions ) (equivalent to ALL used as a filter) removes any filter coming from the Regions table: the denominator is the total across regions, keeping the other filters (year, category). ALLSELECTED removes the visual’s filters but keeps the slicers’: the share is calculated relative to the user’s selection.

FILTER: conditions on a measure

A simple filter argument can only refer to columns. For a condition on a measure (“customers who bought more than 100,000 MAD”), you build a filtered table with FILTER:

Customers 100k+ =
COUNTROWS (
    FILTER ( VALUES ( Customers[customer_id] ), [Net Sales] > 100000 )
)

Sales of Customers 100k+ =
CALCULATE (
    [Net Sales],
    FILTER ( VALUES ( Customers[customer_id] ), [Net Sales] > 100000 )
)

FILTER is an iterator: it evaluates the condition for each customer. Applied to a whole fact table (FILTER(Sales, …)), it is slow and often unnecessary: filter a column or a small dimension table instead.

Row context and context transition

Iterators (SUMX, AVERAGEX, FILTER, COUNTROWS(FILTER(…))) create a row context: they walk through a table and evaluate an expression for each row. A row context does not filter the model. But when a measure is called in a row context, DAX performs a context transition: the current row becomes a filter. That is why [Net Sales] inside FILTER(VALUES(Customers[customer_id]), …) correctly calculates the sales of the customer being iterated.

Average Sales per Customer =
AVERAGEX ( VALUES ( Customers[customer_id] ), [Net Sales] )

Best Month (amount) =
MAXX ( VALUES ( Calendar[YearMonth] ), [Net Sales] )

Variables: readability and performance

Gap to Target =
VAR Actual = [Net Sales]
VAR Goal = [Target]
RETURN
    IF ( NOT ISBLANK ( Goal ), Actual - Goal )

VAR calculates a value once and gives it a name. The code reads like an argument and avoids calculating the same measure several times.

Level 3 · Advanced — Chapter 8

Advanced DAX: time intelligence and complex measures

Comparing a period with the previous one, accumulating since the start of the year, smoothing a trend: these are management’s most frequent questions. DAX time intelligence functions answer them, provided you have a proper Calendar table.

Why a marked date table

Time intelligence functions take a date column and return a table of dates that serves as a filter for CALCULATE. When the Calendar table is marked as a date table, a filter on Calendar[Date] automatically removes the filters on the table’s other columns (month, year): this is what lets “the previous year” escape the visual’s “2025” filter.

Year-to-date total

Sales YTD =
TOTALYTD ( [Net Sales], Calendar[Date] )

-- equivalent, more explicit form:
Sales YTD (alt) =
CALCULATE ( [Net Sales], DATESYTD ( Calendar[Date] ) )

In a monthly 2025 chart, the June year-to-date value is 6,503,298 MAD. For a fiscal year that does not start in January, DATESYTD accepts a year-end date: DATESYTD ( Calendar[Date], "06-30" ).

Comparing with the previous year

Sales PY =
CALCULATE ( [Net Sales], SAMEPERIODLASTYEAR ( Calendar[Date] ) )

Growth % =
VAR CY = [Net Sales]
VAR PY = [Sales PY]
RETURN
    IF ( NOT ISBLANK ( CY ) && NOT ISBLANK ( PY ), DIVIDE ( CY - PY, PY ) )

Sales YTD PY =
CALCULATE ( [Sales YTD], SAMEPERIODLASTYEAR ( Calendar[Date] ) )

Control values: net sales 2025 15,097,590 MAD, prior year 13,556,439 MAD, growth 11.4%. For December: 1,243,539 MAD versus 1,137,436 MAD. At 30 June, the 2025 year-to-date (6,503,298 MAD) exceeds 2024’s (5,761,339 MAD) by 12.9%.

The NOT ISBLANK test avoids showing −100% for a future period without sales, or infinite growth when the previous year is empty (2024 has no prior year in the data).

Three-month moving average

3-Month Moving Average =
VAR LastDate = MAX ( Calendar[Date] )
VAR Period = DATESINPERIOD ( Calendar[Date], LastDate, -3, MONTH )
RETURN
    CALCULATE ( AVERAGEX ( VALUES ( Calendar[YearMonth] ), [Net Sales] ), Period )

DATESINPERIOD returns the dates of the three months ending on the last visible date; AVERAGEX over YearMonth averages the three monthly amounts (not the days). In December 2025, the moving average is 1,291,489 MAD. It smooths monthly variations and reveals the trend.

Management measures

Target = SUM ( Targets[target_amount] )

Target Attainment = DIVIDE ( [Net Sales], [Target] )

Target Status =
VAR A = [Target Attainment]
RETURN
    SWITCH ( TRUE (),
        ISBLANK ( A ), BLANK (),
        A >= 1, "Achieved",
        A >= 0.9, "Close",
        "Behind"
    )

Avg Delivery Days =
AVERAGEX (
    CALCULATETABLE ( SUMMARIZE ( Sales, Sales[order_id], Sales[delivery_days] ), Sales[status] = "delivered" ),
    Sales[delivery_days]
)

Cancellation Rate =
DIVIDE (
    CALCULATE ( DISTINCTCOUNT ( Sales[order_id] ), Sales[status] = "cancelled" ),
    DISTINCTCOUNT ( Sales[order_id] )
)

For 2025: target 15,186,000 MAD, attainment 99.4%, average delivery time 2.4 days, cancellation rate 3.6%. Delivery time is calculated per order: SUMMARIZE reduces to one row per order before averaging, otherwise orders with several lines would weigh more.

Calculation groups: going further

When a model multiplies variants (PY, YTD, YTD PY for every measure), calculation groups let you write each variant once. They are created in Power BI Desktop’s Model view (recent versions) or with Tabular Editor. They are not needed for the course project.

Level 2 · Intermediate — Chapter 9

Professional visualisation

A good visual answers a question in a few seconds. This chapter presents the right chart for each question, KPI cards, conditional formatting and the rules of readability and accessibility.

One question, one visual

Question Recommended visual Avoid
How much? Are we on target? Card, multi-row card, gauge with target A chart for a single number
Who sells the most? Sorted horizontal bar chart Pies with more than five slices
How is the business evolving? Line or column chart by month, with prior year Bars not sorted by date
What is the composition? 100% stacked bars, treemap 3D pies
Are two quantities related? Scatter chart Two lines on different, unlabelled scales
What is the detail? Matrix with conditional formatting A 50-column table

KPI cards

A summary page starts with a row of four to six cards: net sales, growth, margin %, target attainment, order count, delivery time. Each card has a clear title and a suitable format (millions with one decimal for amounts, percentage for rates). The built-in KPI visual compares a value with a target and shows the trend along a date axis.

Growth Label =
VAR G = [Growth %]
RETURN
    IF ( ISBLANK ( G ), "No prior-year reference",
        FORMAT ( G, "+0.0%;-0.0%" ) & " vs PY" )

This text measure is used as a dynamic card subtitle (Format › Title › fx button).

Conditional formatting

  • Background or font colour by rules: green if [Target Attainment] ≥ 100%, orange between 90 and 100%, red below.
  • Data bars in a matrix to compare amounts visually.
  • Icons sparingly: a colour code must remain readable for colour-blind users, so pair it with a label (“Achieved”, “Behind”).

Theme and visual identity

A JSON theme defines series colours, fonts and visual styles for the whole report: View › Themes › Browse for themes. The course provides the file pbi-theme-ishraqa7.json (navy, gold, grey). Use a single accent colour for what must catch the eye and keep the rest grey.

Accessibility

  • Sufficient contrast between text and background (a ratio of at least 4.5:1 for body text, following WCAG guidance).
  • Alt text for every visual (Format › General › Alt text), read by screen readers.
  • A consistent tab order (View › Selection pane › Tab order).
  • Explicit titles: “Net sales by region, 2025” rather than “Chart 3”.

Demonstration: the “Overview” page

  1. Import the ISHRAQA7 theme, then insert a navy rectangle as a header band with the page title.
  2. Place five cards: Net Sales, Growth % (dynamic subtitle), Margin %, Target Attainment, Order Count.
  3. Add a combo chart: columns for [Net Sales] by month, line for [Sales PY].
  4. Add a horizontal bar chart of [Net Sales] by category, sorted by descending value.
  5. Add a Region × ([Net Sales], [Target], [Target Attainment], [Target Status]) matrix with conditional colour on attainment.
  6. Fill in the alt text of each visual and check the alignment (Format › Align).

Level 3 · Advanced — Chapter 10

Interactive reports: slicers, interactions and navigation

A report becomes an analysis tool when users can filter, explore and navigate unaided. This chapter covers slicers, interactions between visuals, drill-through, tooltip pages, bookmarks and navigation buttons.

Slicers

A slicer is a visible, clickable filter. For the sales analysis: Year (dropdown, single select), Region (buttons), Channel and Customer segment. View › Sync slicers applies the same slicer across several pages: users keep their selection when changing page.

To avoid an empty report, set the Year slicer to single select and choose a default value (2025) before publishing: the selection saved in the file is what users see when they open it.

Interactions between visuals

By default, clicking a bar filters or highlights the other visuals on the page. Select a visual, then Format › Edit interactions: an icon on each target visual lets you choose Filter, Highlight or None. KPI cards should usually respond (Filter); an annual trend chart can ignore a click on a month (None).

Drill-through

  1. Create a “Customer detail” page and drag Customers[customer_name] into the Drill through well of the Visualizations pane.
  2. Place the detail visuals on this page: sales by month, products bought, orders (matrix), average delivery time.
  3. Power BI automatically adds a Back button. From any visual showing customers, right-click › Drill through › Customer detail opens the page filtered on that customer.

Tooltip pages

A page formatted as a Tooltip (Page settings › Allow use as tooltip, canvas size “Tooltip”) can appear when hovering over another visual: for example, a mini chart of monthly sales for the region under the pointer. Link it in the visual’s Format › Tooltip › Type: Report page.

Bookmarks and buttons

A bookmark saves the state of a page: filters, visual visibility, sort order. Combined with the Selection pane, it lets you switch between two presentations in the same area (chart or table). Buttons (Insert › Buttons) trigger an action: page navigation, bookmark, drill-through, back.

  1. Place a chart and a matrix on top of each other; in the Selection pane, hide the matrix and create the bookmark “Chart view” (with the Data option unticked to keep the user’s filters).
  2. Show the matrix, hide the chart, create the bookmark “Table view”.
  3. Insert two buttons and assign each the matching Bookmark action.
  4. Create a navigation menu (Insert › Buttons › Navigator › Page navigator) on every page.

Field parameters

Modeling › New parameter › Fields creates a selector that changes the measure or axis shown by a visual: the user chooses “Net Sales”, “Gross Margin” or “Order Count” in a slicer, and the chart adapts without multiplying pages.

Testing the user experience

  • Have the report tested by someone who did not build it, without explanation.
  • Check that each page shows the active filters (dynamic title or text card).
  • Provide a “Reset filters” button (a bookmark of the default state).

Level 3 · Advanced — Chapter 11

Publishing, refresh and security

A report is only valuable if it is accessible to the right people, up to date and secure. This chapter presents publishing to the Power BI Service, workspaces, scheduled refresh, sharing and row-level security (RLS).

Publishing

Home › Publish sends the file to a workspace in the Service. Two items are created: the semantic model (formerly “dataset”: tables, relationships, measures) and the report (pages and visuals). Several reports can use the same semantic model: this is good practice to guarantee identical indicators.

Workspaces and roles

Role Can
Admin Everything, including managing members and deleting the workspace
Member Publish, edit, share, manage the app
Contributor Publish and edit content
Viewer View content

To distribute to many readers, you publish an app from the workspace: readers see a stable version while the team keeps working in the workspace. The exact rights and the required licences (Pro, Premium Per User, capacity) depend on the organisation’s subscription: check with your administrator.

Scheduled refresh

  1. In the Service, open the semantic model settings.
  2. Under Data source credentials, enter the access details for the sources.
  3. If the sources are on the internal network (shared folder, SQL server), configure an on-premises data gateway and link it to the model.
  4. Under Scheduled refresh, turn it on, choose the frequency and time zone, and enter a notification address in case of failure.

The DataFolder parameter from chapter 3 can be changed in the published model’s settings: just point it to the shared folder reachable by the gateway.

Row-level security (RLS)

RLS restricts the rows visible to each user. It is defined in Desktop (Modeling › Manage roles) and assigned to people in the Service.

-- Static role "North region", DAX filter on the Regions table:
[region_code] = "N"

-- Dynamic role, Access table (email, region_code) related to Regions:
-- filter on the Access table
[email] = USERPRINCIPALNAME ()

Thanks to the relationships, the filter on Regions propagates to customers, sales and targets. For the dynamic role, the Access → Regions relationship must filter from Access to Regions: create it from Access[region_code] (many) to Regions[region_code] (one), set cross-filter direction to Both and tick Apply security filter in both directions. This is one of the few cases where bidirectional filtering is justified.

Testing: Modeling › View as, choose the role (and, for the dynamic role, another user). The cards must show only the sales of the authorised region: with the “North region” role and the 2025 filter, net sales equal the North region’s sales.

Governance

  • One certified semantic model per domain, reused by reports.
  • Sensitivity labels if the organisation uses them.
  • An “About” page: sources, calculation rules, last refresh date, contact.
  • Version history of the .pbix file (or of the .pbip project saved in a Git repository).

Level 3 · Advanced — Chapter 12

Final project: a decision-support analysis and reporting system

The final project brings together every skill of the course: preparing data, building the model, writing measures, designing a multi-page report, securing it and drawing a written analysis from it. It matches the deliverable expected of a BI analyst in a company.

The sponsor and the question

ISHRAQA7 Academy Power BI project: business performance analysis. The sales management of an office equipment distributor (case study, practice dataset) wants a monthly reporting system that answers four questions: where do sales stand against target? Which regions, categories and customers explain the gaps? Is the margin holding? Is customer service (delivery times, cancellations) under control?

Expected deliverables

Deliverable Minimum content
.pbix file Named Power Query queries, folder parameter, star schema, documented measures table
Page 1 “Overview” Five KPI cards, monthly trend against prior year, region matrix with target attainment
Page 2 “Regions and sales reps” Sales, target, gap and attainment by region and sales rep; map or bars
Page 3 “Products and margin” Sales and margin % by category and product; top 10 products
Page 4 “Customers” Ranking, segments, 100k+ customers, drill-through to “Customer detail”
Page 5 “Service” Average delivery time and cancellation rate by channel and by month
“Quality control” page Row counts per table, “(Blank)” rows, control values
Analysis note One page: quantified findings, explanations, three recommendations

Steps

  1. Data: import the ten tables with the DataFolder parameter; prepare Sales, Products and Customers (chapters 3 and 4).
  2. Model: eight relationships, marked Calendar table, technical columns hidden (chapter 5).
  3. Measures: use the pbi-dax file and check every measure against the control table below (chapters 6 to 8).
  4. Pages: build the five pages and the control page, apply the theme, fill in alt text (chapters 9 and 10).
  5. Security: dynamic role by region, tested (chapter 11).
  6. Analysis: write the note from the report’s figures.

Control values (year 2025)

Measure Expected value
Net Sales 15,097,590 MAD
Sales PY 13,556,439 MAD
Growth % 11.4%
Gross Margin / Margin % 5,775,781 MAD / 38.3%
Order Count / Average Order Value 1,628 / 9,274 MAD
Target / Attainment 15,186,000 MAD / 99.4%
Year-to-date at 30 June 6,503,298 MAD
3-Month Moving Average (December) 1,291,489 MAD
Avg Delivery Days 2.4 days
Cancellation / return rate 3.6% / 2.4%
Customers 100k+ / their sales 73 / 11,861,122 MAD

Writing the analysis note

A good note does not paraphrase the charts: it links quantified findings to explanations and actions. Suggested structure: 1. overall situation (sales, growth, attainment); 2. gaps by region and category, with their contribution; 3. margin and service; 4. three recommendations, each paired with a tracking indicator. State the limits: this is a two-year practice dataset, with no information on competitors’ prices or the potential of each region.

Assessment grid

Criterion Points
Data preparation: named queries, parameter, types, checks 20
Model: star schema, correct relationships, marked Calendar 20
Measures: accurate (control values), readable, documented 25
Report: relevance of visuals, readability, interactivity, accessibility 20
Security and analysis note 15

Final project brief

  • Import the ten tables with the DataFolder parameter and prepare Sales, Products and Customers.
  • Build the star schema (eight relationships) and the marked Calendar table.
  • Create and document the measures of the pbi-dax file, checked against the control values.
  • Build five analysis pages, a “Customer detail” drill-through page and a quality-control page.
  • Set up a dynamic RLS role by region and test it.
  • Write a one-page analysis note with three recommendations.

Check your knowledge

1. Introduction to Power BI and Business Intelligence
1. Which application is used to build the data model and DAX measures?
2. What distinguishes a KPI from a plain measure?
3. What is the on-premises data gateway for?
2. Getting started with Power BI Desktop
1. In which view do you check relationships between tables?
2. Where is data prepared before it is loaded into the model?
3. Why set the locale when importing decimal numbers?
3. Connecting to data sources
1. Which connectivity mode copies the data into the Power BI file?
2. Why prefer a formatted Excel table to a whole sheet?
3. What is the DataFolder parameter for?
4. Cleaning and transforming with Power Query
1. Why remove duplicates before the other corrections?
2. Which operation stacks several monthly exports with the same columns?
3. What does try … otherwise … do in M?
5. Modelling data: relationships and the star schema
1. What is the normal cardinality between a dimension and a fact table?
2. Why does the Regions table filter both Customers and Targets?
3. What condition must a table marked as a date table meet?
6. DAX fundamentals: measures and evaluation context
1. When should you prefer a measure to a calculated column?
2. Why use DIVIDE rather than the / operator?
3. What does RELATED do in SUMX(Sales, Sales[quantity] * RELATED(Products[unit_cost]))?
7. Intermediate DAX: CALCULATE, FILTER and iterators
1. What does CALCULATE([Net Sales], Channels[channel_code] = "WEB") return if a slicer filters the “PAR” channel?
2. Which function removes the visual’s filters while keeping the slicers’?
3. What is context transition?
8. Advanced DAX: time intelligence and complex measures
1. Which function returns the dates of the same period one year earlier?
2. Why iterate over Calendar[YearMonth] in the moving average?
3. Why mark the Calendar table as a date table?
9. Professional visualisation
1. Which visual suits a ranking of sales reps by sales?
2. Why pair a label with conditional colour?
3. Where do you set the colours and fonts of the whole report at once?
10. Interactive reports: slicers, interactions and navigation
1. How do you keep a slicer’s selection when changing page?
2. What is a drill-through page for?
3. What must be unticked so that a toggle bookmark does not reset filters?
11. Publishing, refresh and security
1. Which items does publishing create in the Service?
2. Why does a workspace member see every region despite RLS?
3. Which function returns the signed-in user’s identity in a dynamic role?
12. Final project: a decision-support analysis and reporting system
1. What is the first check before building the project pages?
2. What should the quality-control page contain?
3. What distinguishes an analysis note from a description of charts?

Glossary for this page

The key terms used in this content, each with a short definition.

Power BI
Microsoft’s platform for analysing data and building interactive, shareable reports and dashboards.
DAX
The formula language of Power BI and Excel’s data model, used to write measures such as revenue or growth rate.
Power Query
A tool in Excel and Power BI to import, clean and reshape data through recorded steps replayed on every refresh.
CSV
A plain text file with delimiter-separated values; the most common format for moving data between programs.
key performance indicator
A figure tracked regularly to measure progress toward a specific goal, such as margin or delay rate.
SQL
The language for querying databases: selecting, filtering and grouping rows and joining tables.
dashboard
A single page gathering the key indicators and charts needed to decide quickly.
Python
A readable programming language widely used for data analysis, automation and AI.

Course files

Choose a language, then the format you need.

العربية

FileFormatSizeDownload
الدليل الكامل للدورة PDF 1.9 MB Download — الدليل الكامل للدورة (PDF)
دفتر التمارين PDF 598 KB Download — دفتر التمارين (PDF)
الحلول المشروحة PDF 682 KB Download — الحلول المشروحة (PDF)
دليل مقاييس DAX PDF 190 KB Download — دليل مقاييس DAX (PDF)
توثيق نموذج البيانات PDF 261 KB Download — توثيق نموذج البيانات (PDF)
دليل المشروع النهائي PDF 205 KB Download — دليل المشروع النهائي (PDF)
مصنف البيانات وتحديد النطاق XLSX 404 KB Download — مصنف البيانات وتحديد النطاق (XLSX)
بيانات CSV للحالة الدراسية (11 ملفًا) ZIP 83 KB Download — بيانات CSV للحالة الدراسية (11 ملفًا) (ZIP)
استعلامات Power Query (لغة M) TXT 11 KB Download — استعلامات Power Query (لغة M) (TXT)
مقاييس DAX للنسخ TXT 4 KB Download — مقاييس DAX للنسخ (TXT)
الأرشيف الكامل للموارد التطبيقية ZIP 449 KB Download — الأرشيف الكامل للموارد التطبيقية (ZIP)
Raw extract to clean (chapter 4) Shared CSV 57 KB Download — Raw extract to clean (chapter 4) (CSV)
ISHRAQA7 report theme Shared JSON 1 KB Download — ISHRAQA7 report theme (JSON)

Français

FileFormatSizeDownload
Manuel complet de la formation PDF 1.8 MB Download — Manuel complet de la formation (PDF)
Cahier d’exercices PDF 559 KB Download — Cahier d’exercices (PDF)
Corrigés commentés PDF 647 KB Download — Corrigés commentés (PDF)
Guide des mesures DAX PDF 164 KB Download — Guide des mesures DAX (PDF)
Documentation du modèle de données PDF 234 KB Download — Documentation du modèle de données (PDF)
Guide du projet final PDF 177 KB Download — Guide du projet final (PDF)
Classeur de données et de cadrage XLSX 402 KB Download — Classeur de données et de cadrage (XLSX)
Données CSV du cas pratique (11 fichiers) ZIP 83 KB Download — Données CSV du cas pratique (11 fichiers) (ZIP)
Requêtes Power Query (langage M) TXT 11 KB Download — Requêtes Power Query (langage M) (TXT)
Mesures DAX à copier TXT 5 KB Download — Mesures DAX à copier (TXT)
Archive complète des ressources pratiques ZIP 447 KB Download — Archive complète des ressources pratiques (ZIP)
Raw extract to clean (chapter 4) Shared CSV 57 KB Download — Raw extract to clean (chapter 4) (CSV)
ISHRAQA7 report theme Shared JSON 1 KB Download — ISHRAQA7 report theme (JSON)

English

FileFormatSizeDownload
Complete course manual PDF 1.7 MB Download — Complete course manual (PDF)
Exercise workbook PDF 549 KB Download — Exercise workbook (PDF)
Worked answers PDF 623 KB Download — Worked answers (PDF)
DAX measures guide PDF 159 KB Download — DAX measures guide (PDF)
Data model documentation PDF 230 KB Download — Data model documentation (PDF)
Final project guide PDF 173 KB Download — Final project guide (PDF)
Data and scoping workbook XLSX 402 KB Download — Data and scoping workbook (XLSX)
Case-study CSV data (11 files) ZIP 83 KB Download — Case-study CSV data (11 files) (ZIP)
Power Query queries (M language) TXT 11 KB Download — Power Query queries (M language) (TXT)
DAX measures to copy TXT 4 KB Download — DAX measures to copy (TXT)
Complete practical resources archive ZIP 446 KB Download — Complete practical resources archive (ZIP)
Raw extract to clean (chapter 4) Shared CSV 57 KB Download — Raw extract to clean (chapter 4) (CSV)
ISHRAQA7 report theme Shared JSON 1 KB Download — ISHRAQA7 report theme (JSON)