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
.pbixfile: 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
- Connect to sources: CSV and Excel files, SQL databases, online services.
- Transform data with Power Query: types, cleaning, merging tables. Each step is recorded and replayed at every refresh.
- Model: link the tables (facts and dimensions) and write measures in the DAX language.
- 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
- Home › Get data › Text/CSV, choose
orders.csvin thedatafolder of the course archive. - In the preview, check the delimiter (comma) and the file origin (65001: Unicode UTF-8), then click Load.
- In Report view, click the Card visual and drag
order_idinto its field: choose the Count aggregation. The visual shows 3,307, the total number of orders, all statuses included. - Add a Clustered column chart:
statuson the X-axis, count oforder_idon the Y-axis. - 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
- Home › Get data › Text/CSV, select
order_lines.csv. - In the preview window: File origin = 65001: Unicode (UTF-8), Delimiter = Comma, Data type detection = Based on first 200 rows.
- Click Transform data rather than Load: you check the types before loading.
- In Power Query, check each column’s type icon:
quantityas whole number,unit_priceanddiscount_rateas decimal number,order_idas 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:
- In Power Query: Home › Manage parameters › New parameter. Name:
DataFolder, type Text, current value:C:\ISHRAQA7\powerbi\data\(with the trailing backslash). - Open the Advanced Editor of each query and replace the hard-coded path with
DataFolder & "order_lines.csv". - 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 fororders. - 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
- Import the file keeping every column as Text (delete the automatic “Changed Type” step): you convert after cleaning.
- Home › Remove rows › Remove duplicates on all columns: 1,174 − 28 = 1,146 rows.
- Column
city: Transform › Format › Trim, then Capitalize Each Word. - Column
quantity: Add column › Custom column with the formula below, then remove the old column. - Column
unit_price: Replace values “,” with “.”, then type Decimal number using the en-US locale (Data type › Using locale). - Merge queries with
Productsonproduct_id(left outer join), expandlist_price, then create the corrected price. - Merge with
Ordersonorder_idto retrieve the reference customer whencustomer_idis empty. - 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
CalendarProductsCustomersChannelsSalesRepsTargetsRegions_Measures
| 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
- In Model view, drag
Products[product_id]ontoSales[product_id]. In the dialog, check: cardinality One to many, cross-filter direction Single, relationship active. - Create the other seven relationships in the table in the same way.
- Hide the key columns of the Sales table from Report view (right-click › Hide in report view): you filter by dimensions, not by keys.
- Check: a table with
Regions[region_name]and the sum ofSales[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 Salesas 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
- Import the ISHRAQA7 theme, then insert a navy rectangle as a header band with the page title.
- Place five cards: Net Sales, Growth % (dynamic subtitle), Margin %, Target Attainment, Order Count.
- Add a combo chart: columns for
[Net Sales]by month, line for[Sales PY]. - Add a horizontal bar chart of
[Net Sales]by category, sorted by descending value. - Add a Region × (
[Net Sales],[Target],[Target Attainment],[Target Status]) matrix with conditional colour on attainment. - 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
- Create a “Customer detail” page and drag
Customers[customer_name]into the Drill through well of the Visualizations pane. - Place the detail visuals on this page: sales by month, products bought, orders (matrix), average delivery time.
- 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.
- 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).
- Show the matrix, hide the chart, create the bookmark “Table view”.
- Insert two buttons and assign each the matching Bookmark action.
- 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
- In the Service, open the semantic model settings.
- Under Data source credentials, enter the access details for the sources.
- If the sources are on the internal network (shared folder, SQL server), configure an on-premises data gateway and link it to the model.
- 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
- Data: import the ten tables with the
DataFolderparameter; prepareSales,ProductsandCustomers(chapters 3 and 4). - Model: eight relationships, marked Calendar table, technical columns hidden (chapter 5).
- Measures: use the
pbi-daxfile and check every measure against the control table below (chapters 6 to 8). - Pages: build the five pages and the control page, apply the theme, fill in alt text (chapters 9 and 10).
- Security: dynamic role by region, tested (chapter 11).
- 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.
