Power BI Roadmap

Introduction to Power BI Core

Power BI is Microsoft's business intelligence product. It connects to data, shapes it, models it and turns it into interactive reports that people read in a browser, a phone or inside other applications. Analysts use it to answer questions such as which regions are missing target and why.

The fundamentals are the same in every project: get data, clean it in Power Query, build a data model of related tables, write measures in DAX, design visuals, publish to the Service and control who sees what. This roadmap follows that order, which is also the order interviewers tend to probe.

Practice. Write down five questions a sales manager might ask that a report could answer.

Power BI Ecosystem Core

The ecosystem has several parts. Power BI Desktop is a free program for Windows in which you build models and reports. The Service is the cloud site where you publish, share, schedule refreshes and manage security. The Mobile App shows reports on phones. Power BI Embedded places reports inside your own applications. Report Builder authors paginated reports. Microsoft Fabric is the wider data platform that now contains Power BI as its reporting layer.

Know which tool does which job. Desktop builds; the Service shares and governs; Embedded and the REST API automate and integrate.

Practice. Sketch the five components and draw arrows for what moves between them.

Installing Power BI Core

Install Desktop from the Microsoft Store or as a download; the Store version updates itself. It runs on Windows only; Mac users work through a virtual machine or the browser-based editor in the Service. A work or school account is needed to publish. Desktop releases monthly, and new preview features can be switched on under options.

After installing, explore the three views on the left: Report for visuals, Table for data and Model for relationships. Save your work as a .pbix file, which holds queries, model and report together. A .pbip project format stores the same content as text files, which suits source control.

Practice. Install Desktop and open a sample file, then switch between the three views.

Dashboards vs Reports Core

A report is a multi-page, interactive set of visuals built on one dataset, where users filter and drill. A dashboard is a single page in the Service made of tiles pinned from reports and other sources; it gives a quick overview and can raise alerts, but offers limited interactivity and no filtering by slicer.

Reports can be built in Desktop; dashboards exist only in the Service. Many teams now use report pages or apps as dashboards. Keep the terms clear in interviews, since people use them loosely.

Practice. Pin two visuals from a published report to a new dashboard and compare how each behaves when clicked.

Licensing Tiers Recommended

Licences decide who can create, share and view. A Free licence lets you build in your own workspace but not share it. Pro adds publishing and sharing among Pro users. Premium Per User adds larger models, more refreshes and advanced features for those users. Capacity licences, now sold as Fabric capacities, give an organisation dedicated compute and let free viewers read content in the right conditions.

Prices and limits change, so check the current Microsoft documentation. In interviews explain the trade-off: Pro suits small teams, capacity suits wide distribution and heavy workloads.

Practice. Choose a licence for a 20-person team and for a 5,000-person rollout and justify each in two lines.

How Data Flows Core

Data moves from sources into queries, which clean it. Loaded queries become tables in the semantic model (called a dataset in older material). Measures calculate over that model. Report visuals ask the model questions, and the Service stores the published model and serves reports from it. Scheduled refresh pulls fresh data from sources, through a gateway if the source is on a private network.

Putting work in the right layer is a skill: reshape data in Power Query or upstream, define relationships and measures in the model, and keep reports to presentation. Logic buried in visuals is hard to reuse.

Practice. Trace one number in a report back to its source file and list each step it passes through.

Data Source Types Core

Power BI has hundreds of connectors. They fall into groups: Files (Excel, CSV, JSON, Parquet, folders), Databases (SQL Server, PostgreSQL, Oracle and others), Clouds (SharePoint, OneDrive, Azure services, Snowflake, BigQuery), Web and Open Data (HTML tables, OData feeds, public datasets) and APIs reached through the Web connector or custom code.

Prefer sources that support query folding and have stable columns. A folder of files with the same layout can be combined into one table. Store credentials and privacy levels correctly, because Power Query uses them to decide how queries may be combined. Pull from a governed warehouse where possible, not a person's spreadsheet.

Practice. Connect to a CSV, an Excel file and a public web table, and load all three.

Connection Modes Core

The storage mode decides where data lives when users open a report. Import Mode copies data into the model's compressed in-memory engine: fastest queries, full DAX, but data is as fresh as the last refresh. DirectQuery sends queries to the source each time: always current, but slower and with DAX and transformation limits. Dual Storage Mode lets a table behave either way, which helps dimension tables in composite models. Direct Lake, in Fabric, reads Delta files in OneLake directly with import-like speed.

Default to Import and move away only for very large data or real-time needs. Composite models mix modes in one model.

Practice. Load one table in Import and one in DirectQuery and compare response times.

Import Mode Core

Import mode loads data through Power Query into the VertiPaq engine, a column store that compresses data heavily by encoding repeated values. A model with tens of millions of rows often fits in a few hundred megabytes if columns have few distinct values.

Strengths are speed and the full DAX language. Limits are model size per licence and refresh time. To keep models small, remove unused columns, avoid high-cardinality text such as free-form comments, split date and time into two columns, and prefer integers for keys. Incremental refresh loads only new partitions.

Practice. Check the model size of a file before and after removing columns you do not use.

DirectQuery Recommended

In DirectQuery mode each visual interaction becomes one or more queries to the source, so source performance is report performance. Use it when data is too large to import, must be near real time or may not leave its source for compliance reasons.

Design with care: keep the source tables narrow and indexed, avoid heavy Power Query steps that cannot fold, limit visuals per page, switch off cross-highlighting where it generates extra queries and use aggregations to answer common questions from an imported summary. Some DAX functions behave differently or are unsupported.

Practice. Connect to a database in DirectQuery and use Performance Analyzer to see the queries sent.

Power Query Core

Power Query is the data preparation tool inside Power BI and Excel. You work through a graphical editor, and every action is recorded as a step in the M language. Queries connect to a source, apply steps and load the result.

The editor has a query list, a preview grid, a formula bar and the Query Settings pane with the Applied Steps list. Each step uses the output of the one before; you can click an earlier step to see data at that point, rename steps, reorder them carefully and delete them. Disable load on staging queries that only feed others, so they do not clutter the model.

Practice. Import a messy file and clean it using only the ribbon, then read the steps it produced.

Applied Steps and the Advanced Editor Core

The Applied Steps pane lists the transformations in order. Each is a line in the M script. Open the Advanced Editor to see and edit the whole query.

let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"), [Delimiter=","]),
    Promoted = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Promoted, {{"Amount", type number}})
in
    Typed

Reading M lets you fix steps the interface cannot, copy logic between queries and spot why a refresh fails. Name steps clearly, and add comments with //.

Practice. Open the Advanced Editor for one of your queries and rename every step meaningfully.

Data Profiling Recommended

Before transforming, look at the data. The View tab offers Column quality (percentage valid, error, empty), Column distribution (distinct and unique counts) and Column profile (statistics and a value histogram). By default profiling uses the first 1,000 rows; switch to the whole dataset when you need accurate counts.

Use it to find nulls, mixed types, duplicate keys, unexpected categories and outliers early. Cardinality information also guides modelling: a column with unique values per row is a key, and a column with a few values is a candidate for a dimension or slicer.

Practice. Profile a dataset on the entire data, not 1,000 rows, and list three quality problems you find.

Data Types & Casting Core

Every column has a type: whole number, decimal, fixed decimal (currency), text, date, time, date/time, date/time/timezone, true/false and binary. Setting the right type matters for sorting, relationships, aggregation, compression and DAX behaviour. Casting changes the type, and a bad cast produces an error value, for instance text "N/A" turned into a number.

Prefer fixed decimal for money to avoid floating point surprises. Keep dates as the date type, not text. Use Using locale when a file's date or number format differs from your machine, for example day-month-year text. Set types once, near the end of the query, to keep the folding chain intact.

Practice. Convert a text column holding dates in day-month-year form into a proper date using a locale.

Changing Text, Numbers and Dates Core

Three families of transformations cover most clean-up. Changing Text: trim and clean stray spaces and non-printing characters, change case, replace values, extract or add prefixes. Changing Numbers: round, apply standard and scientific operations, convert units, and turn text into numbers. Changing Dates: pull out year, month, quarter and week, calculate age or duration, and shift dates.

Do these in Power Query rather than DAX when they define the data itself. A trimmed key matters, because "A12 " and "A12" will not match in a relationship.

Splitting Columns is a related clean-up: break one column into several by delimiter, character count or change of case, or into rows when a cell holds a list. Fix the number of resulting columns, since extra values on a later refresh would add new ones.

Practice. Clean a customer table so names are in proper case, spaces are trimmed and a date of birth becomes an age.

Conditional Columns and Custom Columns Core

A Conditional Column uses an if-then-else dialog to label rows: for instance "High" when amount is above 1,000. A Custom Column lets you write an M expression with any logic.

if [Amount] > 1000 then "High"
else if [Amount] > 100 then "Medium"
else "Low"

M is case sensitive, and column references go in square brackets. Add custom columns when the value belongs with the row at load time. If a value depends on what the user filters, it is a measure, not a column.

Practice. Add a size band column to an orders table with three levels using both methods.

Removing Duplicates and Sorting Data Core

Removing Duplicates keeps the first row for each distinct combination of the selected columns. Choose columns deliberately, since selecting all columns removes only exact full-row copies. Sort before removing only if you rely on order, and know that sorting is not guaranteed to be preserved by later steps.

Sorting Data in Power Query affects the preview, but a report sorts by a column in the model through Sort by column. Remove duplicates from dimension tables to create a clean key column before using it on the one side of a relationship.

Practice. Build a unique list of customers from an order table and confirm the key column has no repeats.

Pivot and Transpose Recommended

Pivot turns values in one column into new column headings, with an aggregation such as sum. Unpivot does the reverse: it turns many columns, such as one column per month, into attribute and value columns, which is the shape analysis tools prefer. Transpose swaps rows and columns.

Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")

Unpivot is the single most useful reshaping step for spreadsheets laid out for people to read. Use "unpivot other columns" so that new months added later are included automatically.

Practice. Unpivot a spreadsheet with twelve month columns and chart sales by month.

Joins and Appending Tables Core

Joins (Merge Queries) combine columns from two tables on matching keys. Kinds: left outer, right outer, full outer, inner, left anti and right anti. Left outer keeps every row of the first table and adds matches from the second; anti joins find rows without a match, useful for data checks.

Appending Tables stacks rows of tables with the same columns, such as monthly files. Check for fan-out: joining on a non-unique key multiplies rows. Merges are expensive; when you can, create relationships in the model instead, which is almost always faster than a merge of large tables.

Practice. Find the customers who have no orders using an anti join, and append two monthly order tables.

Handling Errors Core

Errors appear in cells after bad casts, divisions by zero or missing fields. They break visuals and refreshes. Options: Remove Errors, Replace Errors with a default, or catch them in M with try ... otherwise.

each try Number.From([Qty]) otherwise null

Investigate the cause before hiding it; a replaced error can hide a real data problem. Keep a copy of the rows that failed in a separate query for review. Remember that steps run in order, so an error in an early step flows through all later steps.

Practice. Create a column with deliberate conversion errors and add a query that lists the failing rows.

Parameters and Functions Recommended

A parameter is a named value, such as a server name, file path or start date, that queries can use. Changing a parameter changes the source for every query that refers to it, which makes moving from development to production easy. A function is a reusable query that takes inputs: right-click a query and choose to create a function, then invoke it on each row or file.

(path as text) => Table.PromoteHeaders(Csv.Document(File.Contents(path)))

Use the "Combine files" feature to see this pattern generated automatically for a folder of files.

Practice. Turn a file path into a parameter and switch between two folders.

M Language Basics Core

M, the Power Query formula language, is functional and case sensitive. A query is a let ... in expression with named steps. Values include text, number, logical, date, null, list {1, 2}, record [A = 1] and table. Functions come from the standard library, grouped by type: Table., List., Text., Date. and more.

Lazy evaluation means steps that are not needed are not computed. Indentation is style only. Learn to read a few patterns, such as each for a one-argument function and _ for the current item, because the interface generates them.

Practice. Write a query by hand that builds a list of the numbers one to ten and converts it into a table.

Query Folding Core

Query folding means Power Query translates your steps into the source's native language, usually SQL, so the source does the work and sends back only the result. It is fast and keeps refreshes light. Folding stops at a step the source cannot express, and every later step then runs locally on all rows brought back.

Check by right-clicking a step and seeing whether View Native Query is available. Keep foldable steps (filters, column selection, simple joins, grouping) first, and push custom logic last. Incremental refresh needs folding to work well. Native SQL you write yourself disables folding unless you use specific options.

Practice. Build a query on a database, find the first step that stops folding and move it later.

DAX vs M Language Core

M and DAX look similar but do different jobs. M runs at refresh time, shapes data before it is loaded and works on tables and rows. DAX runs at query time, in the model, reacts to filters and slicers and works with columns and relationships.

A rule of thumb: if the value is fixed per row and does not depend on user selection, do it in M or in the source. If it must respond to filters, such as a margin percentage across whatever region is selected, use a DAX measure. Mixing them up is a common cause of slow, inflexible models.

The Power Query vs. Data Model choice is the same question of placement: shape and clean in Power Query, and keep relationships, measures and security in the model so it stays small. Table Manipulation such as grouping, filling down and promoting headers belongs in Power Query, and Python in Power Query can run a script for a rare step, though it blocks folding.

Practice. List five calculations from a report you know and decide whether each belongs in M, DAX or the source.

Facts and Dimensions Core

A fact table records events or measurements: sales lines, clicks, shipments. It is long, mostly numbers and keys. Dimension tables describe the things events relate to: customer, product, date, store. They are shorter, with descriptive attributes used for filtering and grouping.

Separating them lets a single dimension filter many facts and keeps text out of big tables, which helps compression. Every number you sum belongs on a fact table; every slicer or axis field belongs on a dimension.

Practice. Classify the columns of a flat sales extract into fact and dimension tables.

Star vs Snowflake Schema Core

In a star schema each dimension is one flat table directly related to the fact. In a snowflake, dimensions are normalised into further tables, such as product to subcategory to category. A star is simpler, faster and easier for report authors, so it is the default for Power BI.

Snowflakes save little space in a compressed model and add relationship hops. Flatten them in Power Query by merging the related tables. Keep a snowflake only when a sub-dimension is shared by several dimensions or changes on its own.

Practice. Flatten a three-level product hierarchy into one product dimension table.

Granularity Core

Granularity is the level of detail one row represents: one order line, one order, one customer per day. State it for every fact table, and keep it consistent within the table. Mixing grains, such as order-level totals in a table of line items, causes double counting.

When two facts have different grains, for example daily sales and monthly budget, relate both to shared dimensions at the level they support, such as a date table, and do not force them into one table. Lower grain gives flexibility but larger models; aggregations can offer both.

Practice. Write down the grain of each table in a model you know, and find one that mixes grains.

Schema Design Core

Good schema design in Power BI is a star: a few fact tables surrounded by shared dimensions, with one-to-many relationships flowing from dimension to fact. Add one date table, marked as such, used by every fact. Avoid wide flat tables that repeat attributes, many-to-many chains and bidirectional relationships everywhere.

Create surrogate integer keys if source keys are text or messy. Hide key columns from report view. Test the design by asking whether a typical question needs only one hop from a dimension to a fact.

Practice. Design a star schema on paper for a library lending system with two fact tables.

Relationships and Cardinality Core

A relationship links a column in one table with a column in another. Cardinality describes the matching: one-to-many (the usual), one-to-one, and many-to-many. The "one" side has unique values, and by default filters pass from it down to the rows that match on the "many" side. Only one relationship between two tables can be active; others are inactive and used through DAX.

Check key columns for blanks and mismatched types. Unmatched rows on the many side show up as a blank member in visuals, which signals missing dimension data. Prefer a bridge table over a direct many-to-many relationship when you can.

Practice. Create relationships in a small model and find the blank category caused by an orphan key.

Cross Filter Direction Core

The cross filter direction decides which way filters travel. Single means they pass from a dimension down to its fact table. Both means filters also flow back, which lets a slicer on one dimension be narrowed by another through a fact table.

Bidirectional filtering is tempting but can create ambiguous paths, unexpected results and slow queries. Prefer single direction and use measures such as CROSSFILTER or TREATAS inside DAX for the cases that need it. Cross-filtering in visuals, covered later, is a separate report behaviour from the model's filter direction.

Practice. Turn on bidirectional filtering for one relationship and describe how a slicer behaviour changes.

Role-Playing Dimensions and Composite Keys Recommended

A role-playing dimension is one dimension used in several roles, such as a date table for order date, ship date and due date. Create one relationship per role; one is active and the others inactive, and activate the right one inside a measure with USERELATIONSHIP.

A composite key is a key made of several columns, for example store plus register number. Power BI relationships use one column only, so combine them into a single key column in Power Query or the source, or use a surrogate key. Concatenate with a clear separator to avoid accidental matches.

Practice. Build a date dimension with an active order-date relationship and an inactive ship-date one.

Hierarchies and Data Categorization Recommended

A hierarchy is an ordered set of fields, such as year, quarter, month, day, or country, region, city. Add them to a visual to enable drill-down. Create one in the model by dragging fields onto each other.

Data Categorization tells Power BI what a column means: city, country, latitude, web URL or image URL. This helps maps geocode locations correctly and makes URL columns clickable. Misclassified geography is a common cause of maps that put Paris in Texas, so add country or latitude and longitude where possible.

Practice. Create a geography hierarchy, tag each level, and plot it on a map with drill-down.

Model Organization and Naming Conventions Recommended

A tidy model is easy for others to use. Choose a convention and keep to it: singular or plural table names, no technical prefixes in names users see, readable column names with spaces, measures with clear verbs and units. Group measures in a dedicated table or folders, use display folders and descriptions, and hide keys and helper columns.

Field Visibility controls this: hide fields from report view that exist only for relationships or calculations. Set default formats and summarisation on each column, and set Sort by column for names such as months.

Practice. Review a model with someone else and note every field they did not understand.

Model Objects and Optimization Core

The model holds tables, columns, measures, relationships, hierarchies, roles and calculation groups, all called model objects. Tools like Tabular Editor, DAX Studio and the VertiPaq Analyzer let you inspect and edit them in bulk.

For Model Optimization: remove unused columns and tables, reduce column cardinality, use integer keys, turn off auto date/time and use your own date table, avoid calculated columns that could come from the source, set correct data types, and prefer star schemas. Check size by column to see where space goes. Aggregations pre-summarise large tables so common questions skip the detail rows.

Practice. Measure model size by column and reduce it by a quarter.

DAX Syntax Core

DAX (Data Analysis Expressions) is the formula language of the model. A measure is a name, an equals sign and an expression. Table names with spaces go in single quotes, and column references are written Table[Column]. Measure names are written in brackets alone.

Total Sales = SUM(Sales[Amount])
Margin % = DIVIDE([Total Profit], [Total Sales])

DAX is case insensitive, uses commas or semicolons depending on regional settings, and treats comments after //. Use DIVIDE instead of the slash to handle zero safely. Format code with indentation, since long measures are read far more than written.

Variables in DAX are declared with VAR and returned with RETURN. They are evaluated once, make long measures readable and often faster, and keep the value they had where they were defined, even if CALCULATE changes filters later: VAR Prior = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) RETURN DIVIDE([Total Sales] - Prior, Prior).

Practice. Write three measures: total, average and a safe percentage.

Columns vs Measures Core

A column stores a value in every row and uses memory; it can be used as an axis, a slicer or in a filter. A measure stores only a formula and calculates when a visual asks, so it reacts to filters. Anything you aggregate (sum, average, ratio) should be a measure. Anything you group by or filter by should be a column.

A common beginner error is a calculated column holding a ratio per row, then summing or averaging it, which gives wrong totals. A ratio should be a measure: sum the numerator, sum the denominator, then divide. Quick Measures generate common DAX through a dialog, and are a useful way to learn patterns, though you should read and tidy what they write.

Calculated Tables and Columns are DAX-built objects stored in the model, for example Date = CALENDAR(DATE(2020,1,1), DATE(2030,12,31)) for a date table. They cost memory, so create them upstream when you can.

Practice. Convert a per-row percentage column into a measure and compare the totals.

Aggregation and Logical Functions Core

Aggregation Functions include SUM, AVERAGE, MIN, MAX, COUNT, COUNTROWS, DISTINCTCOUNT and COUNTBLANK. DISTINCTCOUNT is common for customers; COUNTROWS counts rows of a table.

Logical Functions include IF, SWITCH, AND, OR, NOT, IFERROR and ISBLANK. SWITCH with TRUE() makes tidy multi-branch rules.

Band = SWITCH(TRUE(), [Total Sales] > 1000, "High", [Total Sales] > 100, "Medium", "Low")

Remember that a measure returning blank hides that row in many visuals, which is useful, and that adding zero can bring unwanted rows back.

Practice. Write a measure that returns the number of distinct customers and labels the result with SWITCH.

Context and Filtering Core

Evaluation context is the set of conditions under which a DAX expression runs. It comes from two sources: the filters applied to the model and the current row. Every cell in a visual has its own filter context made of the row and column headings, slicers, page and report filters, and filters from other visuals.

This explains why the same measure gives different values in different cells. To debug a surprising number, ask what filters apply to that cell. Filters flow through relationships from dimension to fact, and functions such as ALL, REMOVEFILTERS, ALLEXCEPT and VALUES change them.

Row vs Filter Context is the distinction to master. Row context means the current row of a table, as in a calculated column or an iterator, and it does not filter other tables by itself; related columns need RELATED. Filter context is what the model is filtered to. A measure has no row context unless an iterator such as SUMX creates one.

Practice. Place one measure in a matrix and explain, cell by cell, which filters apply.

CALCULATE Function Core

CALCULATE evaluates an expression after changing the filter context. It is the most important DAX function.

Sales Red = CALCULATE([Total Sales], Product[Colour] = "Red")
Sales All Regions = CALCULATE([Total Sales], REMOVEFILTERS(Region))

Filter arguments replace existing filters on the same column, unless wrapped in KEEPFILTERS. Simple Boolean filters are shorthand for FILTER over ALL of the column. Percentage of total measures divide a value by the same value with filters removed. Use CALCULATE with time intelligence helpers, and avoid FILTER over whole fact tables when a column filter works.

Context Transition happens when CALCULATE, or a measure reference, is evaluated inside a row context: the current row becomes an equivalent filter. That is why SUMX(Customer, [Total Sales]) gives each customer its own sales, and why it can be costly on large tables.

Practice. Write measures for sales for one category, for all categories and as a percentage of all categories.

Iterator Functions Core

Iterators go row by row through a table and then aggregate: SUMX, AVERAGEX, MINX, MAXX, COUNTX, RANKX and others. The first argument is the table, the second an expression evaluated in row context.

Revenue = SUMX(Sales, Sales[Qty] * Sales[UnitPrice])

Use them when a value must be computed per row before summing, such as quantity times price, or when ranking. They are flexible but can be slower than plain aggregations, so iterate over the smallest suitable table. SUMX over a summarised table of customers or months lets you aggregate at a chosen grain.

Practice. Calculate revenue as quantity times price using SUMX and compare with a calculated column approach.

Time Intelligence Core

Time intelligence compares and accumulates over dates. It needs a proper date table with no gaps, marked as a date table and related to your facts. Common functions: TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD, DATESBETWEEN and DATESINPERIOD.

Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Moving averages, rolling twelve-month totals and year-on-year growth are standard interview tasks. Handle fiscal years through the year-end date argument. Avoid the auto date/time feature, which hides a table per date column and bloats the model.

Practice. Build year to date, previous year and growth percentage measures against your own date table.

Window Functions and Advanced DAX Recommended

Newer window functions OFFSET, INDEX and WINDOW return rows relative to a position in an ordered, partitioned table, which makes running totals, moving averages and previous-row comparisons clearer than older patterns. Other Advanced DAX to know: TREATAS to apply a filter from one table to another, GENERATE and CROSSJOIN, SUMMARIZECOLUMNS, ADDCOLUMNS, virtual relationships, parent-child hierarchies using PATH, and dynamic segmentation.

Prev Month = CALCULATE([Total Sales], OFFSET(-1, ALLSELECTED('Date'[Month]), ORDERBY('Date'[Month])))

Learn the basics well first; most reports need nothing beyond CALCULATE, iterators and time intelligence.

Practice. Compute a running total of sales by month using WINDOW or an older pattern and compare.

Calculation Groups, Field Parameters and Visual Calculations Recommended

Calculation Groups apply one set of calculation items, such as current, year to date and prior year, to many measures without writing each one. They are created with external tools such as Tabular Editor or in the model view.

Field Parameters let report users switch which measure or dimension a visual shows, using a slicer. Visual Calculations are DAX written directly on a visual, using functions like RUNNINGSUM and PREVIOUS, without touching the model; they are convenient for visual-only needs such as running totals.

Use calculation groups to cut duplicate measures, and field parameters to give users choice without extra pages.

Practice. Add a field parameter that lets a user swap between sales, profit and quantity on one chart.

DAX Performance and Performance Analyzer Core

Slow reports usually come from the model, the measures or too many visuals. In Desktop, the Performance Analyzer records the time for each visual to load, split into DAX query, visual display and other. Copy the DAX query and run it in DAX Studio to see server timings.

Common fixes: use variables, reduce iterators over large tables, avoid bidirectional filters, replace FILTER over tables with column filters, shrink the model, move logic to Power Query, add aggregations, and reduce visuals per page. Measure before and after each change, so you know what helped.

Practice. Record a slow page in Performance Analyzer and speed up its slowest visual.

Visualization Basics and Design Principles Core

Start with the question and the audience, then choose the simplest visual that answers it. Design Principles: put the most important number top left, use consistent alignment and spacing, limit colours and use them to carry meaning, label clearly, remove clutter and avoid decoration that adds nothing. Compare values along a common baseline; length is read more accurately than area or angle.

Check each page for a headline, a small number of visuals and obvious interaction cues. Test with a real user and ask what they take away in ten seconds.

Practice. Take a cluttered report page and redesign it with half the visuals.

Charts for Comparison, Trends and Parts of a Whole Core

Bar and Column Charts compare categories; sort them and start axes at zero. Line and Area Charts show change over time; area suits cumulative totals. Pie and Donut Charts show shares of a whole, only for a few slices; otherwise use bars. Combination Charts place a column and a line on shared or dual axes, for instance revenue and margin percentage. Scatter and Bubble Charts show the relationship between two measures, with bubble size for a third.

Choose by the question: comparison, trend, composition or relationship. Avoid dual axes unless units truly differ, since they mislead.

Practice. Build one chart of each kind from the same data and write one sentence on what each shows best.

Gauges, Cards, KPIs, Matrix and Table Visuals Core

Gauges, Cards, and KPIs show single numbers. Cards show a value; the KPI visual adds a target and a trend; gauges show progress to a goal but use plenty of space. Matrix and Table Visuals show exact numbers: a table lists rows, and a matrix adds row and column grouping, subtotals and expand or collapse.

Use them when readers need precise values or when a chart would hide detail. Add conditional formatting to guide the eye. New card visuals allow several values and reference labels. Limit the number of columns to what a reader can take in.

Practice. Create a KPI row of three cards and a matrix of sales by region and quarter with subtotals.

Maps Recommended

Maps show geography. The basic map plots bubbles by location; the filled map shades regions; shape maps use custom boundaries; Azure Maps adds richer layers and options. Locations come from names, which Bing geocodes, or from latitude and longitude, which is more reliable.

Set data categories on location columns and give extra context such as country to avoid mistakes. Remember that colour intensity for raw counts mostly shows population, so normalise values per head where fair comparison matters. Check your tenant allows map visuals, since administrators can disable them.

Practice. Plot sales by city with a bubble map and then a normalised filled map and compare the story each tells.

Slicers, Filters and Interactivity Core

Slicers and Filters let users choose what they see. Slicers sit on the page; filters in the filter pane can apply to one visual, one page or the whole report. Use hierarchy, list, dropdown, between and relative date slicers and sync them across pages.

Interactivity covers how visuals react to one another. Cross-filtering and highlighting happen when you click a bar; edit interactions to control which visuals respond. Drill-through and drill-down let users go deeper: drill-down moves down a hierarchy in the same visual, drill-through jumps to a detail page filtered to the selected item.

Practice. Build a summary page with a drill-through detail page and control which visuals respond to clicks.

Bookmarks, Buttons and Navigation, Custom Tooltips Recommended

Bookmarks save a view of a page: filters, visibility and selection. With Buttons and Navigation you build menus, toggle between charts or reset filters; the page navigator button creates a menu automatically. Custom Tooltips use a small report page as a hover tooltip, to show extra detail.

Use them to simplify what users see, not to hide a messy design. Name bookmarks clearly and test on a phone. The Explore feature lets users open a visual and analyse it in a new view without changing the report.

Practice. Add a toggle button that switches a chart between sales and profit, and a tooltip page for one chart.

Formatting, Themes and Conditional Formatting Core

Formatting and Themes: a theme is a JSON file that sets colours, fonts and defaults for every visual, so the whole report looks consistent and follows brand guidelines. Conditional Formatting changes colour, data bars, icons or font by value or by a measure, for example red for below target.

Choose accessible palettes: do not rely on red and green alone, ensure contrast, and add text or icons. Save the theme and reuse it in each report. Apply formatting to the model default for number formats, so every visual shows the same units.

Practice. Create a theme file with your colours and apply conditional formatting to a table by a margin measure.

Advanced Visuals and Small Multiples Recommended

Small Multiples split one chart into a grid of the same chart per category, which makes comparisons easy without many separate visuals. Advanced Visuals use analytics to explain data. The Decomposition Tree breaks a measure down by dimensions you choose, in any order. Key Influencers finds factors associated with a result, such as which customer traits relate to churn. The Q&A Visual lets users type a question in plain language. The Explore Feature lets viewers examine a visual on their own.

These are good for exploration but show correlation, not cause; say so to the audience. Check results against the data before presenting them.

Practice. Use a decomposition tree to find the largest driver of a sales drop and confirm it with a table.

Custom Visuals Optional

Custom visuals extend the built-in set. They come from the marketplace, from your organisation's approved list, or from files you build yourself with the Power BI visuals SDK using TypeScript. Certified visuals have passed Microsoft security checks and work with exports and subscriptions.

Before using one, check whether a standard visual with formatting can do the job; custom visuals can slow reports, break after updates and raise governance questions. Administrators can limit which are allowed. Use organisation-approved ones for anything shared widely.

Practice. Add one approved custom visual to a report and compare it with a standard one that does a similar job.

Accessibility, Layout Frameworks and Mobile Recommended

Accessibility means everyone can use the report: set alt text on visuals, a sensible tab order, a title for each page, sufficient colour contrast, keyboard navigation, and high-contrast themes. Layout Frameworks are consistent grids for placing visuals: a template page with a header, navigation, filters and a content area, reused for every page; background images help align.

For Mobile, build a phone layout in Desktop with the key visuals stacked vertically, and test in the Mobile App. Paginated Reports (RDL), built in Report Builder, are pixel-exact documents for printing and exports, such as invoices, and run on a Premium or Fabric capacity.

Practice. Create a phone layout for your report and add alt text to every visual.

Service, Workspaces and Workspace Roles Core

The Service is the Power BI cloud site. Content lives in Workspaces, shared folders where teams collaborate on reports, models and dataflows. Each person gets a Workspace Role: Admin (full control), Member (publish and share), Contributor (create and edit but not manage access) and Viewer (read only).

Use separate workspaces for development, test and production, and for each team. Do not put everyone as admin. Use security groups for membership, so access follows people when they change role. My workspace is personal and should not hold shared content.

Practice. Create a workspace, add a colleague as Viewer and check what they can and cannot do.

Publishing and Sharing Core

Publishing Reports from Desktop uploads the model and report to a workspace. Re-publishing replaces them. Publishing and Sharing then spreads content in several ways: share a link, grant access to the workspace, or build an App, a packaged set of dashboards and reports with its own audience and navigation, which is the best route for wide distribution.

Sharing and Permissions are separate from workspace roles: report access, model Build permission (lets users create their own reports on it) and Reshare. Use Subscriptions and Alerts so users get a report by email on a schedule, or a notification when a dashboard tile passes a threshold.

Practice. Publish a report, create an app for a small audience and set up an email subscription.

Datasets, Dataflows and Refresh Core

A published model is a dataset (now called a semantic model). Dataflows run Power Query in the cloud and store results for reuse, so several models share one cleaned table. Refresh and Change Detection: schedule refreshes up to a limit per day set by your licence, or trigger them through the API; change detection on a page makes DirectQuery or live reports refresh when a value changes.

Use Incremental Refresh to refresh only recent partitions of a large table, using RangeStart and RangeEnd parameters, which needs query folding to be efficient. Monitor refresh history and set failure notifications.

Practice. Configure incremental refresh on a large table and compare refresh time with a full refresh.

On-premises Data Gateway Core

The On-premises Data Gateway is a small service you run on a machine in your own network. It lets the Service reach data sources that are not on the internet, such as a local SQL Server. It supports scheduled refresh and DirectQuery. A standard gateway serves many users; a personal gateway serves one person and suits testing only.

Install it on a reliable server, not a laptop, with a clustered pair for availability, and map each data source to credentials in the Service. Cloud sources do not need it. Gateway problems are a classic cause of failed refreshes, so know how to read its logs.

Practice. Describe the network path a scheduled refresh takes from the Service to an on-premises database.

Collaboration and Power BI with Excel Recommended

Collaboration in the Service includes comments on reports, sharing links, Teams integration, endorsement with promoted or certified labels and subscriptions. Power BI with Excel works both ways: Analyse in Excel connects PivotTables to a published model, and published Excel workbooks show in the Service. Excel users can reuse governed measures instead of building new numbers.

Agree where the source of truth is, so teams do not export data to spreadsheets and drift apart.

Practice. Connect Excel to a published model with Analyse in Excel and build a PivotTable using its measures.

Row-Level and Object-Level Security Core

Row-Level Security restricts which rows a user sees. Define roles in Desktop with DAX filters on tables, then assign people or groups in the Service.

[Region] = USERPRINCIPALNAME()   // simple rule
Region[Email] = USERPRINCIPALNAME()   // lookup table rule

Dynamic rules use a mapping table. Workspace members with edit rights bypass RLS, so test with View as. Object-Level Security hides entire tables or columns from roles, and is set with external tools. Both apply to the model, not to exports from a source, and DirectQuery can also use the source's security.

Practice. Build a role that limits sales managers to their own region and test it with View as.

Governance, Access Control and Tenant Settings Core

Governance is the set of rules that keep content trustworthy and safe. Access Control uses Microsoft Entra groups, workspace roles and app audiences. Tenant Settings in the admin portal switch features on or off for the organisation or for groups, such as export to Excel, publishing to the web, custom visuals and Copilot.

Sensitivity Labels from Microsoft Purview classify content and follow it when exported. Data Loss Prevention policies detect and restrict sensitive data in models. Certified Datasets mark approved sources of truth. Audit Logs record who viewed, shared and changed what. Define who owns each model and review usage regularly.

Practice. Draft a one-page governance policy covering who certifies datasets and how sensitive data is labelled.

Deployment Pipelines and Workflow Lifecycle Recommended

Deployment Pipelines move content through development, test and production workspaces, comparing differences and applying rules that switch data sources and parameters per stage. The Workflow and Lifecycle around it: develop in Desktop, keep project files in Git, review changes, deploy to test, validate, then promote to production, and roll back by redeploying the previous version.

Fabric Git integration syncs workspaces with a repository. Avoid editing production content directly, and document the owner and the release steps for each report.

Practice. Create a three-stage pipeline and use a rule to change the data source between stages.

Fabric, Power Automate, APIs and Integrations Recommended

Microsoft Fabric is the unified analytics platform with OneLake storage, lakehouses, warehouses, pipelines and Power BI. Direct Lake models read data from OneLake. Power Automate can start flows from a data alert, refresh a model or send a report. The REST API automates tasks: refresh datasets, manage workspaces and export reports. Power BI Embedded places reports in your own apps for customers or staff.

Further Integrations include Teams, SharePoint, Excel, Python and R scripts for visuals and transformation in Desktop, and tools such as Tabular Editor and DAX Studio. Together these fit Power BI into the Data and Infrastructure stack of an organisation, and a language layer such as Copilot lets users ask questions in plain English.

Practice. Use the REST API or a Power Automate flow to trigger a dataset refresh.