From the world of BI to building a polished report: 63 short topics, 30 quiz questions and two summary assignments with full solutions — all of it free.
Three minutes of reading that will save you searching: how to navigate, where the questions are, and what's saved. Then the lesson content, the course map, and preparation for practice.
the buttons Previous Fnext Fixed at the top of the screen and always available. You can also use the keyboard, swipe on your phone, or jump to a specific topic from the topic list.
the button Questions and practice at the top of the screen always leads to the first questions page — a self-check and two summary assignments. You can return to it from any topic, anytime.
The topic where you stopped and the topics you’ve already read are saved only in your browser. You can close and come back later, and reset everything from the topics list.
The complete track, from the fundamentals to publishing and managing in the cloud. You can jump to any lesson with a click.
Five summary assignments, all with a full solution for every exercise: the NBA assignment embedded within the lesson (topics 33–62), and the AdventureWorks, space launches, champions league, and retail sales assignments, which open on separate pages. It's worth downloading the data files and installing Power BI Desktop right now, so you don't get stuck partway through. Note: the download and the practice are On the computer only — Power BI Desktop doesn't exist on phone.
13 sheets and 37,475 rows — the data for the capstone assignment: 14 DAX measures, a two-page dashboard, and double conditional formatting. The 28 exercises start at topic 33, each on a separate page with a full solution.
7 tables and 121,253 sales rows: 13 DAX measures (including RANK, WINDOW, OFFSET and INDEX), a full one-page dashboard, and a country traffic-light indicator. 25 exercises on a separate page, each with a full solution.
32 tables, 100,000 rows, and 44 relationships: 14 DAX measures (including RANK, INDEX, and WINDOW), two dashboard pages, and dual conditional formatting. 27 exercises on a separate page, each with a full solution. The file includes a relationships sheet and a QA report sheet.
32 tables, 100,000 rows, and 44 relationships: 14 DAX measures (including RANK, INDEX, and WINDOW), two dashboard pages, and dual conditional formatting. 27 exercises on a separate page, each with a full solution. The file includes a relationships sheet and a QA report sheet.
11 tables and 30,000 sales rows: 14 DAX measures (including RANK, INDEX and WINDOW), two dashboard pages and double conditional formatting. Two Snowflake branches, three fact tables, and grain differences between daily sales and monthly targets. 27 exercises with full solutions.
Business Intelligence is the process and tools that turn raw data into business insights that drive decisions.
Click each stage of the pyramid to read what it says. The higher you go, the value increases.
BI Takes you from the base — the data — to the peak: an informed decision.
Static reports from the IT team — analysts wait weeks for a new report.
The user builds it themselves — Power BI and Tableau changed the rules of the game.
asked in natural language — Copilot, ThoughtSpot, Gemini. Power BI dominates the last two generations.
The time breakdown of a typical analyst — the bars fill in as you reach them.
Worth noting: 70% of the time it's not the tool — it's understanding and data. The tool helps with the other 30%.
Organizations collect mountains of data — but make decisions based on gut feeling. In the age of Big Data, manual analysis in Excel is slow, error-prone and unreliable.
Click on each reason to expand.
Power BI doesn't replace the analyst — It releases it from the boring work of cleaning data, and lets them focus on what really matters: drawing conclusions and business impact.
Power BI is Microsoft's business intelligence platform, which lets you connect, transform, model and present data in interactive dashboards — with sharing and publishing in the cloud.
In 2015 Microsoft merged all three intoPower BI — a standalone tool, fast and accessible.
The beginning of the technology under a code name, based on the Power Pivot and Power Query add-ins.
The product got its name — Power BI — and was integrated as part of Office 365.
In July 2015, Power BI launched as a Standalone Platform separate from Excel.
Integration with the Fabric platform and built-in AI capabilities based on Copilot.
First in the Magic Quadrant for 18 consecutive years — far ahead of the competition.
Three components with distinct roles. Pick a tab to get to know each one.
The desktop environment where the-BI developer spends most of their time: building queries, defining relationships, writing DAX and designing reports.
powerbi.com — this is where reports are published, refresh is scheduled, permissions are managed and sharing between teams happens.
Apps for iPhone, Android and-Windows. — here the end consumer — a manager or a field worker — meets the finished product.
You take messy data from SQL, build a smart model, write DAX and create clear visualizations. The value: a dashboard that's built once and refreshes automatically — instead of a manual Excel report every week.
Consuming the reports, not building them. Looking at the Top Level: did we hit the target? Which branch is losing? The value: Drill-down from a general chart down to city or agent level.
Marketing, warehouse, field sales and HR use the report as a daily tool. The value: Data Democratization — the data is accessible to everyone who needs it.
Manages permissions, security, and data governance. The value: centralized control with RLS — define exactly who sees what in the same report.
What turns Power BI into a platform — and not just a "visualization tool".
A star schema connecting dozens of tables with relationships — cross-system analysis in a single model.
A powerful language for complex real-time measures — YoY, moving averages, and conditional calculations.
Cross-filtering — every element in the report affects the others. One click = filtering everything.
A regional manager sees only their own region — the same report, a dynamic experience per user.
| property | Microsoft Excel | Microsoft Power BI |
|---|---|---|
| Basic structure | Cell-based | Based on tables and columns |
| Processing engine | Linear calculations | VertiPaq — In-Memory Compression |
| Data volume | Up to about a million rows | Hundreds of millions of rows |
| Logic language | Excel formulas | DAX language |
| ETL | Power Query as an add-in | Power Query as the core (M language) |
| Visualization | Static charts | Interactive dashboards |
| distribution | A physical file — a versioning problem | A cloud with managed permissions |
| security | A basic password | RLS — row-level security |
| designation | Personal analysis, financial models | Enterprise BI, Big Data |
The research firm Gartner classifies BI vendors into four quadrants every year, based on two axes: ability to execute (vertical) and completeness of vision (horizontal).
Two ways to install Power BI Desktop, then a first connection to data.
Automatic updates, the easiest installation, no admin permissions needed. Search the store for: Power BI Desktop.
Downloading the exe file from the official site: powerbi.microsoft.com/desktop. Admin permissions are required.
Sales data for 2023 — one table, 1,000 rows, 14 columns. Always understand the data before you build.
Which of the two to drop: The demo below goes through Get Data ← Text/CSV, so for the first exercise download the CSV file. The Excel file contains exactly the same 1,000 rows, but with types already defined — a real date in the Sale_Date, percent in the column Discount and decimal numbers in the money columns. The sheet is called sales_data, so that every DAX line in the lesson works unchanged even after loading from Excel. Anyone who gets stuck on Hebrew encoding or commas in a CSV — should start from Excel.
The sale amount in shekels — the key metric for every analysis
The product category: Electronics, Clothing, Food, Furniture
Region (4 regions) and sales rep (5 reps) — for segmentation
Sale date across 2023, unit quantity, and discount percentage
sales_data.csv and click Open.Delimiter = Comma andFile Origin = UTF-8.Use First Row as Headers active.Sale_Date Recognized as Date and not as text.Sales_Amount andUnit_Price Identified as Decimal Number.Behind the scenes Power Query writes in the M language — but you don't have to write it yourself, it's all done with clicks.
The same interface exists in both Excel and Power BI. Learn once — use two tools.
Define the steps once — every refresh of the report reruns the process on its own.
Power BI Desktop is built around three workspaces — and that's where you start building.
This is where you build the visualizations and design pages
Viewing the data rows in each table
Managing the relationships between the tables
note: sales_data has 14 columns in one flat table — there's no need for a JOIN, so the Model View will stay empty. This is completely fine for initial practice. Once there are several tables (part 3), we'll learn to build relationships and a Star Schema.
Every visual type tells a different story — choose the one that fits the question.
Product_Category in rows, month in columns, SUM(Sales_Amount) in values — in thousands of ₪. Move the mouse (or use Tab) over a cell to highlight it, just like in Power BI.
| category | January | February | March |
|---|---|---|---|
| Clothing | 129 | 91 | 131 |
| Furniture | 88 | 97 | 162 |
| Electronics | 98 | 90 | 97 |
| Food | 88 | 91 | 86 |
| total | 403 | 369 | 476 |
The currently highlighted value: —
SUM(sales_data[Sales_Amount])COUNTROWS(sales_data)DISTINCTCOUNT(sales_data[Sales_Rep])AVERAGE(sales_data[Sales_Amount])| rating | category | transactions | income |
|---|---|---|---|
| 1 | Clothing | 268 | $1.31M |
| 2 | Furniture | 260 | $1.26M |
| 3 | Electronics | 246 | $1.24M |
| 4 | Food | 226 | $1.20M |
| total | 1,000 | $5.0M |
In Power BI: choose Stacked Bar Chart, drag Product_Category to the Y-Axis and SUM(Sales_Amount) to the X-Axis, sort descending, and filter Top N = 3 in the Filters pane.
-- your first measure: Total Revenue = SUM ( sales_data[Sales_Amount] ) -- Safe division (handles division by zero): Avg Revenue = DIVIDE ( [Total Revenue], COUNTROWS ( sales_data ) )
A calculation performed dynamically according to the filter context
Sums Sales_Amount — exactly like SUM in SQL
Safely divides two values — for example average revenue per transaction
Until now we've built visuals from data. Now we'll learn to build the The canvas itself — to add elements that aren't data, and arrange them on the page. Two families of tools do all the work.
everything you add to a page beyond visuals: a text box, buttons, shapes, an image and a sparkline.
What happens when elements overlap: which is above which, how to align them and how to merge them into one unit.
Insert ← group Elements. From here you add a text box, buttons, shapes, an image and a sparkline.Format ← group Arrange. From here: next/back, selection pane, align and group.note: Some buttons appear grayed out until you select an element on the canvas — Group and Align only light up when something is selected how much elements together, and Sparkline only when the selected visual is a table or matrix.
Five tools for adding graphic and text objects to the canvas.
Free text: titles, explanations and comments.
Interactive buttons that perform an action.
Lines, rectangles, arrows, and circles.
Placing an image file — a logo or background.
A tiny line chart inside table cells.
The distinction worth remembering: a visual is built from the data and comes from the Visualizations pane. An element from the Insert group is A design object that you place manually — it doesn't respond to filtering and doesn't show data (except for Sparkline, which lives inside an existing visual).
The first two tools: one adds context, the second makes the report interactive.
An area where you can type any text: a report title, an explanation of a visual, an instruction to the user, or a note. Each text box is styled separately for font, size, color, alignment and background — exactly like in any text editor.
The report name at the top of the page
A short explanation under the visual
* Data is updated to the last quarter
A button is something you can click While viewing the report. From the small arrow a menu opens with ready-made button types, and for each button you set Action (Action) — what happens when the user clicks. That's how the report becomes interactive instead of just a static view.
Navigating to another report page
Resetting all slicers on the page
Back to the previous page
Activating a saved bookmark
Two tools for visually styling the report, beyond the data itself.
Basic geometric shapes — lines, rectangles, arrows and circles. Used for visually dividing areas, colored backgrounds for titles, guiding the eye with an arrow, and frames.
Selecting an image file from the computer and placing it in the report: the company logo at the top of the report, a background image for the page, icons and explanatory diagrams, or a branding banner.
Insert ← Shapes. A shapes gallery opens: rectangles, basic shapes, and arrows.Format shape for full control over the look.Fill color, border color and width, and shape type.
Rotating the shape to the desired angle.
Shadow, transparency and quick formatting.
Turns the shape into a button — clicking performs navigation or filtering.
A point that surprises students: A shape also has an Action section. That means any rectangle or icon you've designed can function as a navigation button — it doesn't have to be a Button object.
When elements overlap, the layer order determines what appears on top of what. In the example below, a blue rectangle covers the chart — try releasing it.
Right now: the shape is above the chart and covers it completely — click Send to back
One layer up. From the small arrow: Bring to front — right to the front, above everything.
One layer down. From the small arrow: Send to back — to the back, below everything.
The confusing difference: forward/backward Move one layer per click, whereas to front/to back Jump all the way to the edge. With two objects you won't feel a difference — with seven, you will.
A list of all the objects on the page. This is where the magic happens: drag to reorder, and click the eye to hide. Try it on the list below.
The top item in the list is drawn above all the others
Another way to move a single layer with each click.
Hiding or showing an element on the page, without deleting it.
Drawing order (who's above whom) vs. keyboard navigation order for report consumers.
A tiny line chart placed inside a cell of a table or matrix, showing a trend over time — without taking up the space of a full visual.
| customer | sales | trend |
|---|---|---|
| Adventure Co | $1.2M | |
| Bike World | $840K | |
| Pro Cycle | $610K |
Why is the button gray for you? Sparkline lights up only when the selected visual is Table or matrix — because the sparkline lives inside the cells, and it has no place in a bar chart or a Card. Select the table first, and only then click.
When elements overlap or are scattered — the Arrange group determines what's on top, aligns, and groups.
Forward / backward
List of all the objects
Align between elements
Merging into one unit
Aligns several elements you've selected together to a common line: left / center / right, top or bottom, andDistribute for spreading at equal intervals.
Merges several elements into one unit: moving, copying and formatting as a unit. Active only when multiple elements are selected, and appears greyed out when a single element is selected.
The practical rule: All the Arrange tools "wake up" only after you select an element — and Align and Group require Two or more. If the button is gray, it's almost always because you selected only one element.
Automatic coloring of visuals based on the values in the data — no manual coloring, with automatic updates when the data changes.
Colors an element — columns, background, text or points — based on the value: low in one color, high in another. The color updates automatically when the data refreshes.
in the panel Format (Visual), next to a property that supports it — for example Color — click thefx. A window opens Color - Categories.
the buttonfx it's the "conditional formatting button" — it appears next to many properties: color, background, title and values. If you saw it — that's a sign you can replace the fixed value with a dynamic rule.
A continuous color between the minimum and maximum by value size.
Each value range gets its own color defined (If… then).
The color comes directly from a field in the data (color code / HEX).
the two common styles, side by side. Switch between them and see how the same data gets colored differently.
A color that changes gradually based on the value's size — a heat-map effect. Choose a color for the lowest value (Lowest value) and the highest (Highest value), or enter a value manually.
You define value ranges, and each range gets its own color. The rule's structure: If value ≥ 50 and < 70 then [Color].
Preview — Gradient style
when what: Gradient is great for relative comparison — who's high and who's low at a glance. Rules fits when there are Business thresholds are defined: below the target in red, within range in yellow, above the target in green. If color needs to be meaningful — choose Rules.
A parameter is a variable the user changes via a slicer. It affects visuals and DAX formulas — showing "what-if" scenarios in real time.
How to get there: tab Modeling ← New parameter ← choose the parameter type. A window opens to configure it.
A numeric parameter with a minimum, maximum, and step size. The user drags a slicer and changes a number — ideal for "what-if" analysis, like a discount percentage or a sales scenario. Try it:
-- the measure that uses the parameter: Revenue after Discount = [Total Revenue] * ( 1 - SELECTEDVALUE ( 'Discount %'[Discount %], 0 ) )
A parameter that lets the user switch which field or measure the visual displays - for example, toggling between sales, quantity, and profit in the same chart. You define which fields go into the list and arrange them. Try it:
Currently showing: sales by category
The difference in one line: Numeric range matters number that enters the calculation. Fields changes Which field the visual shows. The first answers "what if", the second answers "what are we looking at".
The two groups we learned at a glance, followed by five tasks on the canvas.
Open a Power BI report and complete the five tasks on the canvas. Check off each task you finish.
0 out of 5 tasks completed
Six takeaways from the lesson, followed by a short quiz for self-check.
Collection ← organization ← analysis ← presentation. Data ← insights ← decisions.
18 consecutive years in the Gartner Magic Quadrant, 30M+ monthly active users.
Desktop for developers, Service in the cloud, Mobile for consumption — everything synced.
Modeling · DAX · Interactivity · Row-level security (RLS).
Tableau, Looker, Qlik, ThoughtSpot, Oracle Analytics, AWS QuickSight.
ETL (Extract ← Transform ← Load) in Power Query, then model and report.
Click an answer to check it immediately. After you choose, a short explanation will open, and the correct answer will be marked in green. The quiz isn't saved anywhere — it's just for you.
You haven't answered questions yet — 0 out of 30
A full assignment on NBA_PowerBI_Dataset_Star.xlsx: 14 DAX measures you write from scratch, two dashboard pages, and conditional formatting with two techniques. Every number on the dashboard comes from a measure you wrote — not from dragging a column. The 28 exercises are split across separate pages, each with a full solution that opens on click.
What to submit: file .pbix One with the 14 measures under their exact names (including three window functions), two dashboard pages, 4 cards on a tray, two charts on page 1, the logo and image you added, a slicer, a table, a Matrix, a Top 10 and a combo chart on page 2, navigation buttons, and double conditional formatting: Rules On the bar chart andField value On the groups table.
The whole assignment works on the fact table FactPlayerGameStats and about the dimensions DimTeam, DimPlayer, DimGameType, DimSeason, DimConference, DimPosition andDimDate. If the model is wrong, all 14 measures will return incorrect numbers.
Model Make sure eight relationships were created between FactPlayerGameStats To the dimension tables. Power BI auto-detects relationships only when the column name is identical on both sides - and that's the case for all eight here. Don't rely on auto-detection: go through the check table and mark them one by one.DimDate ← Table tools ← Mark as date table ← column Date. Without it the line chart in exercise 2.6 will behave oddly.Cross filter direction = Single, from the dimension to the fact. This is a necessity, not a recommendation: the file has three fact tables that share the same dimensions, and the moment one relationship becomes Both A loop forms between the facts — Power BI will block the relationship or return incorrect numbers.Create a measure named Total Points that summarizes the FactPlayerGameStats[Points]. Format with a thousands separator.
Total Points = SUM ( FactPlayerGameStats[Points] )
why SUM: One numeric column, adding up all 30,000 values into a single number. We built a measure and not a calculated column because we're summing, not classifying — and the measure is "alive", every slicer will re-run it on the relevant bubble.
result: 247,501 — the anchor number for the whole assignment.
Create a measure named Games Played that counts distinct games based on FactPlayerGameStats[GameID]. Remember: every game appears in 20 rows (10 players per team).
Games Played = DISTINCTCOUNT ( FactPlayerGameStats[GameID] )
Why not COUNT: COUNT(GameID) would return 30,000 — it counts rows that have a value, not distinct values. Each game is represented by 20 rows (10 players × 2 teams), so 30,000 ÷ 20 = 1,500.
result: 1,500 games.
Create a measure named Points per Game that calculates the average points per game (both teams combined) — using only the two existing measures, without the division sign / and without AVERAGE. Format to one digit after the decimal point.
Points per Game = DIVIDE ( [Total Points], [Games Played] )
The average of what: AVERAGE(FactPlayerGameStats[Points]) It would return 8.25 - an average per player-game row, not per game. This is exactly why the Lego principle exists: when a measure is built from two existing measures, the denominator is defined explicitly and you know what you divided by. DIVIDE preferable to / because it returns blank instead of an error when the denominator is zero.
result: 165.0 Points per game (both teams combined — about 82.5 per team).
Create a measure named Total Rebounds that summarizes the FactPlayerGameStats[Rebounds]. Format with a thousands separator.
Total Rebounds = SUM ( FactPlayerGameStats[Rebounds] )
result: 132,022 Rebounds (88.0 per game).
Create a measure named Playoff Points that returns only the playoff points. UseCALCULATE with [Total Points] As an expression and a condition on DimGameType[GameTypeName].
Playoff Points = CALCULATE ( [Total Points], DimGameType[GameTypeName] = "Playoffs" )
FactPlayerGameStats ← New measurePlayoff Points = CALCULATE ( ← type [ and choose the measure [Total Points]DimGameType[ ← choose GameTypeName ← = "Playoffs" ← EnterWhat isn't included: inDimGameType Four values, andFinals It's a value separate fromPlayoffs. The measure returns 29,789 and doesn't include the 2,933 finals points. For both together: DimGameType[GameTypeName] IN { "Playoffs", "Finals" } — total 32,722.
What happens here: CALCULATE Adds the condition to the filter bubble and only then runs the [Total Points]. The filter sits on a text column in the dimension table and flows through the relationship (1:*) to the fact table, filtering 30,000 rows — without any JOIN.
result: 29,789 — 12.04% of all league points.
Create a measure named Home Points that returns the points scored by the home team only — a condition on FactPlayerGameStats[IsHomeTeam]. This is a boolean column: the condition is = TRUE() and not a string.
Home Points = CALCULATE ( [Total Points], FactPlayerGameStats[IsHomeTeam] = TRUE () )
What the measure measures: the home teams' share of total points — 50.03%, meaning 82.55 points per game at home versus 82.45 away. A difference of a tenth of a point is effectively zero, so there's no home advantage in this data (the home win percentage also stands at 49.7%). The dataset was generated synthetically with no home bias.
Not to confuse the two measures: "Home advantage" in sports is measured by win percentage, not by point percentage. Even in a real league with a clear home advantage, the point split will stay close to 50% because both teams score in the same game — the difference accumulates in the result, not the volume.
result: 123,823 (50.03%) · outside: 123,678. Sanity check: 123,823 + 123,678 = 247,501.
Create a measure named % of League Points: Divide the [Total Points] in the total points of the entire league — meaning CALCULATE That removes the filter fromDimTeam using ALL. Without a division sign. Format as a percentage, two digits.
% of League Points =
DIVIDE (
[Total Points],
CALCULATE ( [Total Points], ALL ( DimTeam ) )
)
What ALL does: The numerator lives in the row's bubble (one group), and the denominator is released from filtering DimTeam and so it always holds the entire league. In the Total row the numerator equals the denominator — and that's the check that the measure was written correctly.
Result: the leading team (Toronto Raptors) holds 3.91% · The Total row shows 100.00%. 30 groups around 3.33% on average — a very narrow spread.
Create a measure named Team Status that returns text: \"Above average\" if the team scored 8,250 points or more, and otherwise \"Below average\". IF With three arguments: condition, result if true, result if false. The number 8,250 was computed once from the card in 1.1 (total league points divided by 30) and written directly inside the IF.
Team Status = IF ( [Total Points] >= 8250, "Above average", "Below average" )
Where does 8,250 come from: 247,501 ÷ 30 = 8,250.03 — the average points per team. It comes out to 14 above and 16 below rather than 15/15, because the average gets pulled upward by the strong teams — as almost always happens in a real distribution.
result: 14 Teams "above average" · 16 "Below average".
Create a measure named Team Tier that classifies each team into a tier based on [Total Points], using the template SWITCH ( TRUE(), ... ) — it's the one that replaces a chain of nested IFs: >= 8800 ← "top", >= 7700 ← "middle", default ← "bottom".
Team Tier =
SWITCH (
TRUE (),
[Total Points] >= 8800, "Top",
[Total Points] >= 7700, "Middle",
"Bottom"
)
the pattern: SWITCH The regular one compares one expression to a list of values; when you write TRUE() As an expression, each pair becomes "condition, result" and the function returns the result of the first condition that holds. Therefore Order is critical — if >= 7700 were first, all the top groups would be classified "middle" and the top tier would remain empty. The last argument, with no condition before it, is the default.
result: 8 "top" · 14 "middle" · 8 "bottom".
Create a measure named Tier Color that returns a HEX code based on the value of [Team Tier] — in the regular SWITCH pattern, one expression and a list of values, without TRUE(): top ← dark navy, middle ← gold, default ← light navy. This measure isn't shown in any visual — it's only used for the conditional formatting in exercise 3.2.
Tier Color =
SWITCH (
[Team Tier],
"Top", "#132449",
"Middle", "#F5B008",
"#A9B4CC"
)
| The value of [Team Tier] | HEX | the color |
|---|---|---|
| top | #132449 | Dark navy |
| middle | #F5B008 | gold |
| Bottom (default) | #A9B4CC | Light navy |
The two forms of SWITCH side by side: In 1.9 we usedTRUE() because the conditions are range comparisons; here the expression is a text measure and the values are exact matches, so it's not needed. One measure builds on the other, so there's a single source of truth for the thresholds.
The common trap: SWITCH compares strings with an exact match. If in 1.9 you wrote "Elite" and here "Elite " with a trailing space — the match will fail silently, with no error message, and all groups will get the default color. Copy the strings from the first measure, don't retype them.
Result: a text measure that returns a HEX code — not shown in any visual, used only for exercise 3.2.
Create a measure named Team Rank That ranks the groups by [Total Points] using RANK on ALL ( DimTeam[TeamName] ), with ORDERBY In descending order.
Team Rank = RANK ( ALL ( DimTeam[TeamName] ), ORDERBY ( [Total Points], DESC ) )
What ALL does: Removes the row filter and returns all 30 teams to the candidate list — without it, the Toronto row has a single candidate and there's no one to rank against. What's different from RANKX: The default of ORDERBY it ASC, and therefore without DESC The weakest group gets position 1 — the opposite of RANKX. And in the Total row: RANK returns blank, because there's no "current row" to locate in the list.
result: Toronto Raptors = 1 · Brooklyn Nets = 2 · Denver Nuggets = 3 · Charlotte Hornets = 30.
Create a measure named Leader Points that returns, for every row, the points of the first team in the list — wrap it [Total Points] inCALCULATE with INDEX ( 1, ... ), it ALL and the same ORDERBY. Bonus (the Lego principle): a measure % of Leader — DIVIDE of [Total Points] in[Leader Points], formatted as a percentage with one digit.
Leader Points =
CALCULATE (
[Total Points],
INDEX ( 1, ALL ( DimTeam[TeamName] ), ORDERBY ( [Total Points], DESC ) )
)
% of Leader = DIVIDE ( [Total Points], [Leader Points] )
| group | Total Points | Leader Points | % of Leader |
|---|---|---|---|
| Toronto Raptors | 9,680 | 9,680 | 100.0% |
| Brooklyn Nets | 9,666 | 9,680 | 99.9% |
| Utah Jazz | 8,991 | 9,680 | 92.9% |
| Sacramento Kings | 7,239 | 9,680 | 74.8% |
| Charlotte Hornets | 6,578 | 9,680 | 68.0% |
How to explain: RANK answers "where am I", INDEX answers "show me who's in position 1". Wrapping it inCALCULATE It's what makes the measure account for context there instead of here. Once every row has the leader's value, any "distance from the champion" comparison is a single DIVIDE. Ranking tells you position; INDEX gives you proportion.
A fixed money column: 9,680 recurs on every row, and the last row stands at 68.0% of it.
Create a measure named Running Points — running total from the top of the list to the current row: CALCULATE with WINDOW ( 1, ABS, 0, REL, ... ), it ALL and the same ORDERBY.
Running Points =
CALCULATE (
[Total Points],
WINDOW ( 1, ABS, 0, REL,
ALL ( DimTeam[TeamName] ),
ORDERBY ( [Total Points], DESC )
)
)
| # | group | Total Points | Running Points |
|---|---|---|---|
| 1 | Toronto Raptors | 9,680 | 9,680 |
| 2 | Brooklyn Nets | 9,666 | 19,346 |
| 3 | Denver Nuggets | 9,603 | 28,949 |
| 29 | New Orleans Pelicans | 7,185 | 240,923 |
| 30 | Charlotte Hornets | 6,578 | 247,501 |
The logic of the four numbers: 1, ABS = starting at the absolute position 1, the head of the list (a stationary window) · 0, REL = stop 0 steps from me, meaning at my position (a window that moves with me). So 1,ABS → 0,REL It is cumulative. If you switch to1,ABS → 3,ABS You'll get a fixed column with the sum of the top three — one word turns a moving window into a fixed one.
A built-in sanity check: the last row must be exactly the league's total points — 247,501. If not, something in the window is broken.
Create a measure named Total Assists that summarizes the FactPlayerGameStats[Assists]. Format with a thousands separator. This measure serves the combo chart in exercise 2.11.
Total Assists = SUM ( FactPlayerGameStats[Assists] )
result: 91,107 Assists (60.7 per game).
Work with only three hues: navy #132449, gold #F5B008 and neutral gray — everything else is a shade of them. Click an empty area on the canvas ← Format your report page ← Canvas background ← a very light version of the navy ← Transparency = 0%. Add Insert → Text box with the title "NBA — the league's situation room".
Format your report page ← Canvas background ← Color = a very light shade of the navy ← Transparency = 0%Insert ← Text box ← large font, a dark color from the familyOnly three shades: #132449 naive · #F5B008 Gold · neutral gray. Every additional color in the dashboard is a lightness shade of them.
Insert → Shapes → Rectangle ← Stretch a horizontal band under the title ← Format shape → Rounded corners, fill with a light shade from the family, no border ← Format → Arrange → Send to back.
Insert ← Shapes ← Rectangle ← Stretch a band the width of the four cardsFormat shape ← Rounded corners ← fill with a light shade from the family, Border = OffFormat ← Arrange ← Send to back — the shape must sit below the cardsCheck: clicking a card selects the card, not the shape. If the shape is selected — it's still on top.
Search Google Images for an NBA logo with the word PNG (NBA logo PNG) and filter Tools → Color → Transparent, at least 300px wide. Look for a second image of a basketball on a transparent background. Insert → Image ← the logo in the header corner on page 1 at about 60px tall, and the ball on page 2 in the opposite corner and smaller. Format image → Scaling = Fit, and always resize from a corner, not the side.
| What we check | How it should look |
|---|---|
| The file type | PNG with a transparent background. A white square around the logo = you downloaded a JPG |
| resolution | At least 300px wide; a small image that's stretched looks blurry when printed or presented |
| Aspect ratio | Dragging from a corner only. A distorted logo is the first thing that jumps out |
| location | Page 1: logo in the header corner · Page 2: ball in the opposite corner, smaller |
| layers | Format → Arrange: the image is above the canvas background and doesn't cover any visual |
| consistency | The same margin from the edge on both pages, and the logo aligned to the title (Align top) |
Using images: The NBA logo is a registered trademark. It's fine for an academic assignment, but in a report distributed to a client or published — use your own organization's logo, or images from free stock sites like Unsplash and Pexels that permit commercial use.
The simple test: if you hide the title, it should still be immediately clear that this is a report about the NBA.
Build 4 cards above the tray: total points (Total Points), games (Games Played), average pts per game (Points per Game), total rebounds (Total Rebounds). Build one card and fully style it (Callout value, font and color from the family, Rounded corners), duplicate ×3 and swap the field. Align: Ctrl+Click On all four of them ← Group → Distribute horizontally → Align top.
| card | the measure | design | The value to display |
|---|---|---|---|
| Total points | Total Points | Display units = Thousands · Value decimal places = 1 | 247.5K |
| games | Games Played | Display units = None (!) | 1,500 |
| Average points per game | Points per Game | One digit after the decimal point | 165.0 |
| Total rebounds | Total Rebounds | Display units = Thousands · Value decimal places = 1 | 132.0K |
Two display traps: (1) Games Played must Display units = None, otherwise "1.5K" will show instead of 1,500. (2) In both cards with Thousands, set Value decimal places = 1 — by default (Auto), Power BI chooses the number of digits itself, and 132,022 might appear as "132.02K" instead of "132.0K", so the cards won't look uniform.
Four cards, four measures you wrote — 247.5K · 1,500 · 165.0 · 132.0K. No raw field is dragged onto the card.
Clustered column chart ← X-axis = DimTeam[TeamName] ← Y-axis = [Total Points] — the measure, not the column! Sorted descending: ⋯ → Sort axis → Total Points → Sort descending. Add Y-axis → Display units = Thousands andData labels = On.
| # | group | Total Points | % of the league |
|---|---|---|---|
| 1 | Toronto Raptors | 9,680 | 3.91% |
| 2 | Brooklyn Nets | 9,666 | 3.91% |
| 3 | Denver Nuggets | 9,603 | 3.88% |
| 29 | New Orleans Pelicans | 7,185 | 2.90% |
| 30 | Charlotte Hornets | 6,578 | 2.66% |
answer: Toronto Raptors Leading with 9,680 — 47.2% — 47.2% more than-Charlotte Hornets (6,578). A very narrow gap across-30 teams: a balanced league.
Line chart ← X-axis = DimDate[Date] ← Y-axis = [Total Points]. If you see 3 points (years) — click the fork arrow (Expand all down one level) down to Month level. Horizontal gridlines dashed, Y axis from 0.
| month | points | note |
|---|---|---|
| January 2025 | 14,248 | record |
| November 2023 | 13,587 | second |
| December 2025 | 13,581 | third |
The missing months: In July-September there is not a single fact row in the data, so the line disappears entirely instead of gradually declining - these are the off-season months between seasons. It's important to recognize this is a feature of the data, not a bug in the chart.
answer: 27 months with games, a peak in January 2025 with 14,248 points.
A new page (＋ at the bottom) ← name: "Teams and Players Spotlight" ← the same background and the same color family. Slicer ← field DimSeason[SeasonName] ← Slicer settings → Format → Style = Tile ← stretch full width below the header. Tiles: selected = the dark shade, unselected = light.
| season | Total Points | Games Played | The season's top scorer |
|---|---|---|---|
| 2023-24 | 82,436 | 500 | Paolo Mancini (440) |
| 2024-25 | 82,483 | 500 | Luka Galloway (353) |
| 2025-26 | 82,582 | 500 | Paolo Mancini (395) |
| Without selection | 247,501 | 1,500 | Paolo Mancini (1,021) |
A tie case for the lesson: In the 2024-25 season, Shai Whitaker is also at exactly 353 points. Filter Top N with identical values at the boundary can return more rows than expected — in tie situations, add a tiebreaker (e.g. a secondary sort).
Check: the three seasons split almost evenly — 82,436 / 82,483 / 82,582, and 500 games per season.
Table ← DimTeam[TeamName] · [Team Rank] · [Total Points] · [% of League Points] · [% of Leader] · [Running Points] · [Team Status] · [Team Tier]. Descending sort by [Total Points], Totals = Off — window functions return blank in the summary row. Format → Values → Rounded corners, a font from the family.
| rating | group | Total Points | % of the league | % of the leader | Team Status | Team Tier |
|---|---|---|---|---|---|---|
| 1 | Toronto Raptors | 9,680 | 3.91% | 100.0% | Above average | top |
| 8 | Atlanta Hawks | 8,804 | 3.56% | 91.0% | Above average | top |
| 9 | Miami Heat | 8,675 | 3.51% | 89.6% | Above average | middle |
| 15 | Houston Rockets | 8,203 | 3.31% | 84.7% | Below average | middle |
| 22 | Dallas Mavericks | 7,730 | 3.12% | 79.9% | Below average | middle |
| 30 | Charlotte Hornets | 6,578 | 2.66% | 68.0% | Below average | bottom |
The difference between the two measures: Miami Heat is "above average" but "middle", and Houston Rockets is "below average" and still "middle". Team Status Compares to the average calculated from the data (8,250.03), andTeam Tier Compares to fixed thresholds that we set. Two measures, two different business questions.
note: The last two columns are measures that return text, not table columns. In the Total row they'll show the classification of the entire league — correct behavior for a measure, and you can turn off Totals if that's confusing.
Check: 8 "Top" rows, 14 "Middle", 8 "Bottom" · 14 "above average" and 16 "below average".
Clustered bar chart ← Y-axis = DimPlayer[PlayerName] ← X-axis = [Total Points]. In the pane Filters ← PlayerName ← Filter type = Top N ← Show items: Top 10 ← By value = [Total Points] ← Apply filter. Data labels = On, descending sort.
| # | player | Total Points |
|---|---|---|
| 1 | Paolo Mancini | 1,021 |
| 2 | Isaiah Galloway | 873 |
| 3 | Isaiah Abernathy Jr. | 862 |
| 4 | Anthony Umberto | 824 |
| 5 | Keegan Iverson-Cole | 814 |
| 6 | Amen Iverson-Cole | 811 |
| 7 | Shai Whitaker | 810 |
| 8 | Frank Pellegrini Jr. | 780 |
| 9 | Trey Vasquez | 778 |
| 10 | Josh Mbeki | 767 |
answer: Paolo Mancini Leading with 1,021 — a gap of 148 points (17%) from second place.
Matrix ← Rows = DimConference[ConferenceName] · Columns = DimGameType[GameTypeName] · Values = [Total Points]. Style presets = Minimal, highlight the Total row, andCell elements → Data bars = On In a shade from the family.
| conference | Preseason | Regular Season | Playoffs | Finals | total |
|---|---|---|---|---|---|
| Eastern | 6,346 | 101,415 | 15,189 | 1,669 | 124,619 |
| Western | 6,031 | 100,987 | 14,600 | 1,264 | 122,882 |
| Total | 12,377 | 202,402 | 29,789 | 2,933 | 247,501 |
What we see: Hamizrahi leads across all four game types, but by a tiny margin (1.4% overall). The one exception is Finals: 1,669 vs 1,264 — a 32% gap, driven by a small number of championship games where each individual game moves the number a lot. A good lesson that percentages on a small base can be misleading.
Check: the Playoffs column sums to29,789 — exactly the measure from exercise 1.5, and the table total is 247,501.
Line and clustered column chart ← X-axis = DimPosition[PositionName] ← Column y-axis = [Total Points] · Line y-axis = [Total Assists] — two measures, two axes. Sorted descending by [Total Assists], Data labels = On on both series. The columns in navy, the line in gold.
| position | Total Points | % of the points | Total Assists | % of the assists |
|---|---|---|---|---|
| Point Guard | 50,973 | 20.6% | 33,554 | 36.8% |
| Shooting Guard | 52,820 | 21.3% | 19,538 | 21.4% |
| Small Forward | 49,442 | 20.0% | 16,776 | 18.4% |
| Power Forward | 46,879 | 18.9% | 11,800 | 13.0% |
| Center | 47,387 | 19.1% | 9,439 | 10.4% |
| total | 247,501 | 100% | 91,107 | 100% |
Why a combo chart: The two measures are measured in completely different units (tens of thousands of points vs. thousands of assists). In a regular bar chart the assists series would be squashed to the bottom; two axes give each series its own scale. This is exactly the case where the rule "don't use two axes" doesn't apply.
The insight of the entire dashboard: Points are split almost evenly across the five positions (18.9%–21.3%), because every player on the court scores. Assists are a positional metric: the point guard holds 36.8% and the center only 10.4%. Same model, same visual, two metrics — and two completely different stories.
Result: nearly flat columns against a plunging line — the gap between the leading and last position is 12.7% in points, but 3.6 times In assists.
On every page: Insert → Buttons → Navigator → Page navigator ← place at the bottom center ← style in the family's color tones. Go over both pages: the same Rounded corners, same intervals, Align for every row. 100% zoom check: no clipped text and no visual touching the edge of the canvas.
Insert ← Buttons ← Navigator ← Page navigator — Power BI creates a button for each page and marks the current oneFormat ← Align — the eye picks up a 3-pixel misalignmentFinal check: switching between the two pages with one click, without leaving view mode.
Color the teams chart's columns (2.5) according to three rules on [Total Points] — no new measure required. Select the chart ← Format visual → Columns → Colors ← click fx ← Format style = Rules, What field = [Total Points] ← three rules with New rule. Critical: the value type = Number, not Percent.
| law | Condition (type Number) | color |
|---|---|---|
| 1 | >= 0 and < 7700 | Light navy — #A9B4CC |
| 2 | >= 7700 and < 8800 | Gold — #F5B008 |
| 3 | >= 8800 | Dark navy — #132449 |
the pitfall: If the value type remains Percent, Power BI will interpret 7700 as percent and no column will be colored. Change it toNumber before OK.
Result: 8 columns in dark navy, 14 in gold, 8 in light navy — exactly the breakdown of [Team Tier], but with manual rules.
Here you don't write any rule by hand: the color comes from the measure Tier Color you wrote in 1.10. Select the Teams table (2.8) ← Format visual → Cell elements ← in the field Team Tier ← Background color → On ← fx ← Format style = Field value ← What field should we base this on? = [Tier Color] ← OK.
Format visual ← Cell elementsTeam Tier in the list above — the formatting applies to this columnBackground color ← On ← fx ← Format style = Field value ← [Tier Color] ← OKFont color The same way with your own text-color measure, or pick a lighter shade for the top layer| layer | groups | who's inside |
|---|---|---|
| Elite (8,800+) | 8 | Toronto Raptors · Brooklyn Nets · Denver Nuggets · Indiana Pacers · Utah Jazz · Minnesota Timberwolves · Los Angeles Lakers · Atlanta Hawks |
| Middle (7,700–8,800) | 14 | Miami Heat · Philadelphia 76ers · Detroit Pistons · San Antonio Spurs · LA Clippers · Cleveland Cavaliers · Houston Rockets · New York Knicks · Memphis Grizzlies · Oklahoma City Thunder · Phoenix Suns · Milwaukee Bucks · Golden State Warriors · Dallas Mavericks |
| Bottom (up to 7,700) | 8 | Portland Trail Blazers · Boston Celtics · Orlando Magic · Washington Wizards · Chicago Bulls · Sacramento Kings · New Orleans Pelicans · Charlotte Hornets |
Why this is stronger than Rules: The rules in 3.1 live inside a single visual — changing a threshold requires fixing every visual separately. The measure Tier Color It's a single source of truth: one change in the SWITCH code updates everywhere it's used, and you can connect a table, Matrix, chart and card to it.
What does this say about the spread of the points: A bell distribution, not a pyramid — the overwhelming majority in the middle (14 groups) and symmetric edges (8 and 8). The thresholds themselves fall between natural clusters: 8,800 sits in the gap between Atlanta Hawks (8,804) and Miami Heat (8,675), and 7,700 in the gap between Dallas Mavericks (7,730) and Portland Trail Blazers (7,643) — not arbitrary "round" numbers.
Both techniques produce exactly the same split 8 / 14 / 8 — once with manual rules inside a single visual, and once from a single measure that can be connected to any visual in the report.
A consolidated validation table for all 14 measures and every visual in the assignment. Open it only after you're done — it gives away all the answers.
| Measure / component | Verified result |
|---|---|
| Total Points | 247,501 |
| Games Played | 1,500 (COUNT would return 30,000) |
| Points per Game | 165.0 (AVERAGE would return 8.25) |
| Total Rebounds | 132,022 |
| Total Assists | 91,107 (60.7 per game) |
| Playoff Points | 29,789 (12.04%) |
| Home Points | 123,823 (50.03%) · away 123,678 |
| % of League Points | Leader 3.91% · Total 100.00% |
| Team Status (IF) | 14 above · 16 below · average 8,250.03 |
| Team Tier (SWITCH) | 8 top · 14 middle · 8 bottom |
| Team Rank (RANK) | Toronto = 1 · Charlotte = 30 |
| Leader Points / % of Leader (INDEX) | 9,680 in every row · last row 68.0% |
| Running Points (WINDOW) | Last row = 247,501 |
| The leading group | Toronto Raptors — 9,680 (47.2% above the last one) |
| Peak month | January 2025 — 14,248 |
| Leading scorer | Paolo Mancini — 1,021 |
| Matrix — East / West | 124,619 / 122,882 |
| Combo chart — assists PG vs. C | 33,554 (36.8%) vs. 9,439 (10.4%) |
All results were verified against NBA_PowerBI_Dataset_Star.xlsx (30,000 fact rows), and all the relationships in the star schema were checked: 0 orphan keys, exactly 20 rows per game, and the date range within DimDate.
The official Power BI documentation
learn.microsoft.com/power-bi VIDEOAn excellent YouTube channel from the Microsoft team — updates and solutions
youtube.com/@GuyInACube BLOGThe home of Marco Russo and Alberto Ferrari — the legends of DAX
sqlbi.com COMMUNITYThe official forum — thousands of questions and answers from the community
community.fabric.microsoft.comYou can also navigate with the buttons at the top of the screen, keyboard arrows, and on phone with a swipe