Learn power bi, with practice after every lesson
8 lessons, about 134 minutes of reading, and 24 multiple-choice questions. Each lesson names the mistake that most often costs people the interview, because that is where the hours actually go. Part of the free coding course.
The data model is the whole job
BasicsMost people meet Power BI as a chart tool and spend their first month fighting it. The charts are the easy part. What decides whether a report works is the data model underneath: which tables exist, how they relate, and which direction those relationships filter in. Get that right and the visuals almost build themselves; get it wrong and you will be writing increasingly baroque formulas to work around it.
The shape you want is a star schema: one central fact table containing the events — sales, tickets, applications, one row each — surrounded by dimension tables describing the things involved, such as date, product, customer and region. Facts are long and thin and grow forever; dimensions are short and wide and change slowly. Nearly every reporting problem fits this shape, and Power BI is built assuming it.
The instinct to resist is the flat table. Bringing in one enormous sheet with every column joined together feels simpler and works until you need a slicer that filters two different visuals differently, or a date table with fiscal quarters. Then you discover the model cannot express it, and the fix is rebuilding rather than patching. Doing the star schema first is faster in total, and it is what an interviewer means when they ask whether you have modelled data.
Syntax
// Star schema: one fact, several dimensions, single-direction filters
//
// DimDate ─┐
// DimProduct ─┼──► FactSales (one row per line item)
// DimCustomer ─┘ · order_date -> DimDate[Date]
// · product_id -> DimProduct[ProductID]
// · customer_id -> DimCustomer[CustomerID]
//
// A dedicated date table is mandatory, not optional — it is what makes
// time intelligence (year-to-date, same period last year) possible at all.
DimDate =
ADDCOLUMNS(
CALENDAR(DATE(2023,1,1), DATE(2027,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"MonthNo", MONTH([Date]),
"Quarter", "Q" & QUARTER([Date])
)
// Then: Modeling > Mark as date table > DateKey points
- Star schema — one fact table of events, dimension tables describing them. It is the shape Power BI is designed around.
- Always build a dedicated date table and mark it as such. Time intelligence functions do not work reliably without one, and the errors it causes are obscure.
- Keep relationships single-direction where you can. Bidirectional filters resolve ambiguously in larger models and produce totals nobody can explain.
Practice challenge
You are given one flat Excel sheet with order lines, product names, customer names and dates all in columns. Name the tables you would build instead, mark which is the fact table, and name the one table you must add that is not in the sheet.
Fact: FactSales (one row per order line)
Dimensions: DimProduct, DimCustomer
Must add: DimDate, marked as a date tableFact: ______
Dimensions: ______
Must add: ______Show a hint
- The fact table holds events; dimensions describe the things involved
- Time intelligence will not work reliably without one specific table
Check yourself
1. What is a fact table?
Show answer
B. The central table of events, one row per transaction
2. Why is a dedicated date table required?
Show answer
B. Time intelligence functions need it to work reliably
3. What is the risk of bidirectional relationships?
Show answer
B. Ambiguous filter paths that produce totals nobody can explain
DAX: measures, columns and context
Working levelDAX is the formula language, and the single idea that unlocks it is the difference between a calculated column and a measure. A column is computed once per row when the data refreshes and is stored in the file. A measure is computed on demand, for whatever selection the user has made, and stores nothing. Beginners write columns because they behave like Excel; almost everything should be a measure.
The reason is filter context. A measure is evaluated inside whatever filters currently apply — the year clicked in a slicer, the region of the row it sits on, the visual it appears in. That is why one measure called Total Sales gives the right answer in twelve different visuals at once, while a calculated column would need one variant per combination and would bloat the model.
CALCULATE is the function that matters most, because it is the one that modifies filter context rather than merely reading it. It takes an expression and a set of filters to apply instead of, or in addition to, the current ones. Almost every non-trivial measure — last year's sales, share of total, sales excluding returns — is CALCULATE with a different filter argument, and understanding it is the difference between copying DAX and writing it.
Syntax
// MEASURE — evaluated per filter context, stores nothing. Prefer this.
Total Sales = SUM(FactSales[LineTotal])
// CALCULATE changes the filter context it evaluates in:
Sales LY =
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))
YoY % =
VAR curr = [Total Sales]
VAR prev = [Sales LY]
RETURN DIVIDE(curr - prev, prev) // DIVIDE handles /0, unlike "/"
// ALL removes a filter — this is how you get a share-of-total
Share of Region =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(DimProduct)))
// CALCULATED COLUMN — computed at refresh, stored, fixed. Use sparingly:
// Margin Band = IF(FactSales[Margin] > 0.3, "High", "Low")Key points
- Measures over calculated columns. A measure adapts to every filter context; a column is frozen at refresh time and inflates the file.
- CALCULATE is the function that modifies filter context. Nearly every real measure is CALCULATE with a different filter.
- Use DIVIDE rather than the / operator. It returns blank instead of an error when the denominator is zero, which happens constantly in period comparisons.
Practice challenge
You need total sales that reacts to a year slicer, and a fixed 'High/Low margin' label on each row. Say which of the two should be a measure and which a calculated column, and why the wrong choice fails.
Total sales: measure
Margin label: calculated column
A column is computed once at refresh and cannot respond to a slicer, so total sales as a column would show the same number whatever the user clickedTotal sales: ______
Margin label: ______
Why the wrong choice fails: ______Show a hint
- One is evaluated per filter context, the other is frozen at refresh
- The slicer is the deciding detail
Check yourself
1. What is the key difference between a measure and a calculated column?
Show answer
B. A measure is evaluated on demand within the current filter context; a column is computed once at refresh
2. What does CALCULATE do?
Show answer
B. Modifies the filter context an expression is evaluated in
3. Why use DIVIDE instead of /?
Show answer
B. It returns blank rather than an error when dividing by zero
Making a slow report fast, and keeping rows private
AdvancedNobody reports a slow report; they stop opening it and go back to the spreadsheet. So speed is not a polish task you do at the end, it is the difference between a report being used and being quietly abandoned, and the number to hold yourself to is that a page renders in under five seconds on the worst laptop in the building rather than on yours.
When a report is slow the cause is almost always the model rather than the visuals. A report that takes twenty seconds to load will not be opened twice. The common causes are predictable: too many rows imported when an aggregate would do, calculated columns doing work that belongs in the source, bidirectional relationships forcing expensive filter resolution, and visuals that each fire their own query against a wide table.
The part people neglect until it bites is refresh and row-level security. A dataset that refreshes at 6am is stale by afternoon for an operations team, and that mismatch is worth agreeing before you build. RLS restricts rows by user so a regional manager sees only their region — and it must be tested by viewing as that role, because getting it wrong exposes salary or customer data to people who should not see it.
Syntax
// Row-level security: a DAX filter applied to a role, per user
// Modeling > Manage roles > new role "Regional Manager":
[Region] = LOOKUPVALUE(
DimUser[Region],
DimUser[Email], USERPRINCIPALNAME()
)
// Then ALWAYS: Modeling > View as > Regional Manager, and check the totals
// change. An untested RLS rule that silently does nothing looks identical
// to one that works.
// Performance: aggregate at the source rather than importing detail
// SELECT order_date, region, SUM(line_total) AS sales, COUNT(*) AS orders
// FROM sales GROUP BY order_date, region;
//
// 40 million rows imported to render a monthly trend is the most common
// reason a report is slow, and the fix is upstream, not in the visuals.Key points
- Measure before you optimise. Performance Analyzer names the visual and the millisecond cost, so you fix the one query taking four seconds instead of rebuilding the nine that were already fast.
- Slowness is nearly always the model: too many rows, calculated columns doing source-side work, or bidirectional relationships.
- Test row-level security by viewing as the role. An RLS rule that silently does nothing is indistinguishable from one that works until data is exposed.
Practice challenge
A report takes 25 seconds to load. It imports 40 million order rows to show a monthly trend by region. Give the fix and say where it belongs, then name the one thing you must do after writing a row-level security rule.
Fix: aggregate to month and region before import (GROUP BY in SQL)
Where: upstream in the source query, not in Power BI
After RLS: test it with 'View as' the role — a rule that silently does nothing looks identical to one that worksFix: ______
Where: ______
After writing RLS: ______Show a hint
- The report never displays individual rows, so it never needed them
- The RLS step is the one people skip until data is exposed
Check yourself
1. What most often makes a Power BI report slow?
Show answer
B. The data model — excess rows, calculated columns, bidirectional filters
2. Why must row-level security be tested with 'View as'?
Show answer
B. A rule that silently does nothing looks identical to one that works
3. A page takes twenty seconds to load. What do you do first?
Show answer
B. Run Performance Analyzer to find which query is slow
Deliver a report people actually use
Job-readyMost Power BI reports are opened twice: once when they are delivered and once when someone is asked whether they use them. The technical work is rarely why. The report answers a question nobody was asking, or it answers it in a form that needs interpreting, so people go back to the spreadsheet they trust.
Start from the decision, not the data. "Which stores are behind target with enough of the month left to fix it" is a decision; "a sales dashboard" is not. A report built from a decision has an obvious default view, an obvious sort order, and an obvious thing to do when a number is red. One built from available columns has fourteen slicers and no opinion.
Then design for the skim. One headline number per page, sorted so the thing needing attention is at the top, and a written sentence saying what "good" looks like — a target line, a comparison, or last year. A number with nothing to compare it to is not information. And check adoption a fortnight later: if it is not being opened, ask the user what they did instead, because that answer is the actual requirement.
Syntax
Report brief — one page, agreed before building
DECISION "Which stores need intervention before month end?"
WHO 12 regional managers, on a phone, Monday morning
DEFAULT current month, their region only (RLS), sorted worst first
MEASURES (not calculated columns — these react to the slicer)
Sales MTD = TOTALMTD(SUM(Fact[Amount]), DimDate[Date])
Target MTD = TOTALMTD(SUM(Target[Amount]), DimDate[Date])
Variance % = DIVIDE([Sales MTD] - [Target MTD], [Target MTD])
GOOD LOOKS LIKE Variance % >= 0. Red below -5%.
ACTION red row -> call that store manager
AFTER TWO WEEKS check usage metrics. Not opened? Ask what they
used instead — that is the real requirement.Key points
- Write the decision the report supports in one sentence before building. If you cannot, the requirement is not finished, however clear the data model is.
- Every number needs a comparison — target, last period, or peer. Without one, the reader cannot tell whether to act.
- Check adoption after delivery. An unopened report is a failed report even if every measure is correct.
Practice challenge
You delivered a technically correct sales dashboard three weeks ago. Usage metrics show four opens, all from you. Say what most likely went wrong, the one question you should have asked before building, and what you do now.
Likely wrong: it reports data rather than supporting a decision - no default view, no comparison to make a number meaningful, and probably too many slicers, so the honest response is to export it to Excel.
Question skipped: 'what decision does this support, and who makes it?' - a report brief starts with a decision, not with the available columns.
Now: ask the intended users what they used instead. Whatever that is - a spreadsheet, an email, a different report - is the actual requirement, and rebuilding from it is faster than defending this one.Likely wrong: ______
Question you skipped: ______
What you do now: ______Show a hint
- A correct report nobody opens has still failed - accuracy was never the constraint
- The recovery move is a question to the user, not a change to the model
Check yourself
1. What should the first line of a report brief state?
Show answer
B. The decision the report supports
2. Why does every headline number need a comparison?
Show answer
B. Without one the reader cannot tell whether to act
3. A correct report nobody opens is:
Show answer
B. A failed report — the requirement was misunderstood
Power Query: cleaning before it reaches the model
BasicsPower Query is the stage before the model, and where the shaping belongs. It runs on refresh, records every step you take as a repeatable script, and means the cleaning happens once for everyone rather than being repeated by hand in a spreadsheet each month. The mental model that helps is that you are writing an instruction list, not editing data — each step is applied to whatever arrives next time, so a fix made once keeps working.
Two behaviours cause most of the confusion. Types are inferred from a sample of the first rows, so a column that looks numeric for two hundred rows and contains 'N/A' at row nine thousand fails on refresh, which is why setting types explicitly is worth doing at the point of import. And the order of steps matters: filtering early makes everything downstream faster, while renaming a column early makes every later step depend on the new name, so a source that reverts the name breaks the query.
The concept that decides whether a report is fast or slow is query folding. Where possible, Power Query translates your steps into a query the source database executes — so filtering a hundred million rows down to a thousand happens on the server, and only the thousand travel. Certain operations cannot be folded, and everything after the first unfoldable step is done locally on the full data. Checking whether 'View Native Query' is available on your last step tells you whether folding survived, and moving unfoldable steps to the end is often the single largest speed improvement available.
Syntax
// Power Query M -- every step is recorded and rerun on refresh
let
Source = Sql.Database("server", "sales"),
Orders = Source{[Schema="dbo",Item="orders"]}[Data],
// filter EARLY -- folds to the server, less data travels
Recent = Table.SelectRows(Orders, each [order_date] >= #date(2025,1,1)),
// set types EXPLICITLY -- do not trust sample-based inference
Typed = Table.TransformColumnTypes(Recent, {
{"order_id", Int64.Type},
{"amount", Currency.Type},
{"order_date", type date}
}),
// normalise the three faces of missing
Cleaned = Table.ReplaceValue(Typed, "N/A", null, Replacer.ReplaceValue, {"region"}),
Removed = Table.RemoveColumns(Cleaned, {"internal_note","etl_batch"})
in
Removed
// QUERY FOLDING -- the difference between fast and unusable
// Right-click the LAST step -> "View Native Query"
// available = folded, the server did the work
// greyed out = folding broke; everything after runs locally
//
// Breaks folding: custom M functions, Table.Buffer, some merges,
// adding an index. Put unfoldable steps LAST.
// Do the cleaning here, not in DAX. DAX cannot fix a wrong type.Key points
- Set column types explicitly at import. Type inference samples the first rows, so a stray 'N/A' deep in the file breaks the refresh and not the preview.
- Filter as early as possible and keep unfoldable steps last. Everything after folding breaks is executed locally against the full dataset.
- Check folding with 'View Native Query' on the final step. Greyed out means the server stopped doing the work, and that is usually the whole performance problem.
Practice challenge
A query works all month then fails on refresh. The amount column is normally numeric but one row in the new file contains 'N/A'. Say why type inference caused this, give the fix, and explain how to tell whether query folding survived to the last step.
Why: types are inferred from a sample of the first rows, so the new non-numeric value was never seen when the type was set
Fix: set types explicitly at import with Table.TransformColumnTypes, and normalise 'N/A' to null before typing
How to check: right-click the last step and see whether 'View Native Query' is available
If greyed out: folding broke, so every step after that point runs locally against the full dataset instead of on the serverWhy it broke: ______
Fix: ______
How to check folding: ______
What folding failing means: ______Show a hint
- Inference samples rows, it does not read the whole file
- Do the cleaning here rather than in DAX
Check yourself
1. Why set data types explicitly at import?
Show answer
B. Inference samples early rows, so a later stray value breaks refresh
2. What does query folding mean?
Show answer
B. Power Query translates steps into a query the source database runs
3. 'View Native Query' is greyed out on your last step. What does that tell you?
Show answer
B. Folding broke — later steps run locally on the full dataset
The star schema, and the flat-table trap
Working levelAlmost every difficult problem in Power BI traces back to the shape of the model, and the shape that works is a star: one fact table of measurements surrounded by dimension tables of descriptions, joined one-to-many from dimension to fact. The engine is built for this. Filters flow naturally down from dimensions to the fact, relationships stay single-directional, and DAX behaves the way the documentation says it does, because the documentation assumes this shape.
The instinct that causes trouble is flattening everything into one wide table, because that is what a spreadsheet looks like. It appears simpler and it costs you three things. Text repeats on every row, so compression suffers and the file grows. Slicers built on that column must scan the whole table to list distinct values. And any measure that should count things once — customers, products — starts double-counting, because those attributes now repeat once per transaction rather than existing once in a dimension.
Two specifics are worth getting right early. Every model needs a dedicated date table, marked as a date table, with one continuous row per day covering the full range: time intelligence functions require it, and the auto date hierarchy that Power BI generates silently creates a hidden table per date column and bloats the file. And avoid bidirectional relationships unless you can state exactly why you need one — they make filters flow both ways, which resolves one problem and introduces ambiguity that produces wrong totals in places far from the change.
Syntax
// STAR SCHEMA -- what the engine is built for
//
// dim_date dim_customer
// \ /
// \ / one-to-many,
// fct_sales single direction,
// / \ filters flow DOWN
// dim_product dim_store
//
// fct_sales one row per transaction line: keys + numbers
// dim_* one row per thing: wide, textual, comparatively small
// THE FLAT TABLE TRAP -- one wide table looks simpler
// "Bengaluru" stored 4M times instead of once -> file bloat
// slicer must scan the fact table for distinct values -> slow
// DISTINCTCOUNT(customer) over repeated rows -> wrong totals
// EVERY MODEL NEEDS A REAL DATE TABLE
DimDate =
ADDCOLUMNS(
CALENDAR(DATE(2023,1,1), DATE(2027,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"MonthNo", MONTH([Date]),
"Quarter", "Q" & QUARTER([Date])
)
// then: Table tools -> Mark as date table
// and: File -> Options -> turn OFF Auto date/time
// (it creates a hidden date table per date column)
// BIDIRECTIONAL relationships: only with a stated reason.
// They make filters flow both ways, and the wrong totals
// they cause appear far from the relationship you changed.Key points
- One fact table of numbers, several dimension tables of descriptions, joined one-to-many in a single direction. The engine and DAX both assume this shape.
- A flat wide table repeats text on every row, slows slicers, and breaks distinct counts. It looks simpler and costs more in every dimension that matters.
- Build an explicit date table, mark it as one, and turn off auto date/time — which otherwise creates a hidden date table for every date column in the model.
Practice challenge
A 4-million-row report is one wide table with city, product name and customer name repeated on every row. The file is large, the city slicer is slow, and DISTINCTCOUNT of customers is too high. Explain each of the three symptoms and give the model change that fixes all of them.
Large file: text values repeat on every row instead of once in a dimension, so compression is far worse
Slow slicer: it must scan the 4-million-row fact table to list distinct cities rather than reading a small dimension
Wrong distinct count: customer attributes repeat once per transaction, so counts over the flat table do not match one row per customer
Fix: split into a star schema - a fact table of keys and numbers, with dim_customer, dim_product and dim_date joined one-to-many in a single directionLarge file because: ______
Slow slicer because: ______
Wrong distinct count because: ______
Fix: ______Show a hint
- Every symptom traces back to the same cause
- The engine and DAX both assume one particular shape
Check yourself
1. Which direction do filters normally flow in a star schema?
Show answer
B. Dimension to fact, one-to-many
2. What is a cost of flattening everything into one wide table?
Show answer
B. Repeated text bloats the file and distinct counts double-count
3. Why turn off auto date/time?
Show answer
B. It silently creates a hidden date table per date column and bloats the model
Time intelligence: YTD, prior year and the date table
AdvancedNearly every business question is a comparison over time — this month against last, year to date, rolling twelve months — and DAX has dedicated functions for all of them. Every one of those functions requires a proper date table: continuous, one row per day, covering the full range of your data, and marked as a date table. Without it the functions either error or, much worse, return plausible numbers that are quietly wrong at the boundaries of months and years.
The reason they need it is how they work. TOTALYTD, SAMEPERIODLASTYEAR and DATEADD do not filter your fact table directly; they replace the current date filter with a different set of dates and re-evaluate the measure. That is why gaps matter — a date table missing weekends leaves those days out of every period calculation — and why the table must extend to the end of the final year, or a year-to-date measure in December stops early without complaint.
The distinctions worth learning are between similar-looking functions. DATESYTD resets on 31 December unless you tell it otherwise, so a fiscal year ending in March needs the year-end date supplied. DATEADD shifts by a period and is the general tool; SAMEPERIODLASTYEAR is the specific one-year case and is clearer when that is what you mean. And PARALLELPERIOD returns whole periods where DATEADD returns the shifted equivalent of exactly what is selected — a difference invisible on a full month and very visible in the middle of one.
Syntax
// All of this REQUIRES a marked date table with no gaps.
Total Sales = SUM(fct_sales[amount])
Sales YTD = TOTALYTD([Total Sales], DimDate[Date])
// fiscal year ending 31 March -- otherwise it resets in December
Sales FYTD = TOTALYTD([Total Sales], DimDate[Date], "03-31")
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
// DIVIDE, not "/" -- returns blank instead of an error at zero
Sales Prev Month = CALCULATE([Total Sales], DATEADD(DimDate[Date], -1, MONTH))
Rolling 12M =
CALCULATE([Total Sales],
DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -12, MONTH))
// WHY THEY BREAK
// date table has gaps (weekends missing) -> those days vanish
// from every period calculation
// date table ends mid-year -> YTD silently stops early in December
// not "Marked as date table" -> plausible but wrong at boundaries
//
// DATEADD shifts exactly what is selected
// PARALLELPERIOD returns the WHOLE period
// -> identical on a full month, different mid-monthKey points
- Time intelligence replaces the date filter and re-evaluates, so a date table with gaps or one that ends too early produces wrong numbers rather than errors.
- DATESYTD and TOTALYTD reset on 31 December by default. A fiscal year needs its year-end date passed explicitly or every FYTD figure is wrong.
- Use DIVIDE rather than the division operator. It returns blank on a zero denominator instead of an error, which matters in any period with no prior-year data.
Practice challenge
Write the measures for total sales, year-to-date on a fiscal year ending 31 March, same period last year, and year-on-year percent. Then say why a date table with missing weekend rows produces wrong numbers rather than an error.
Total Sales = SUM(fct_sales[amount])
Sales FYTD = TOTALYTD([Total Sales], DimDate[Date], "03-31")
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Why gaps break it: these functions replace the date filter with a set of dates and re-evaluate. Days absent from the date table are simply absent from every period, so totals are quietly short rather than erroring.Total Sales = ______
Sales FYTD = ______
Sales LY = ______
YoY % = ______
Why gaps break it: ______Show a hint
- Without the year-end argument it resets on 31 December
- DIVIDE returns blank instead of erroring on a zero denominator
Check yourself
1. Why must a date table be continuous?
Show answer
B. Time intelligence re-filters by date; missing days drop out of every period
2. Your fiscal year ends 31 March. What must TOTALYTD be given?
Show answer
B. The year-end date, e.g. "03-31"
3. Why prefer DIVIDE over the / operator?
Show answer
B. It returns blank instead of an error when the denominator is zero
Publishing, refresh and sharing without leaking
Job-readyPublishing is where a report meets governance, and the parts that bite are all operational. A dataset refreshes on a schedule you set, and if the source is on-premises that refresh runs through a gateway, which is a service on a machine that must stay running and whose stored credentials expire. Most 'the report stopped updating' incidents are one of three things: a gateway offline, a credential expired, or a refresh silently failing while the report continues to display the last good data quite happily.
Sharing has several mechanisms and confusing them is how data escapes. A workspace is where the artefacts live and grants access to the people building them. An app is the packaged, read-only version for consumers, and is what most viewers should get. Sharing an individual report grants access to the underlying dataset too, which is the step people take casually and regret — the recipient can often build their own report against all of that data, not merely view the page you sent.
Row-level security is the control for 'everyone sees the same report, filtered to their own rows', and it must be tested rather than assumed. Roles are defined in the model and assigned in the service, and the two halves get out of step: a role defined but assigned to nobody applies to nobody, and it looks identical to one that is working. Test with 'View as role', and confirm the totals change — an RLS rule that filters nothing produces exactly the same report as no rule at all, which is why this failure is discovered by an auditor rather than by a developer.
Syntax
// PUBLISH -> the three things that break refresh
// 1. gateway offline (on-prem sources only)
// 2. stored credentials expired
// 3. refresh failing silently -- the report keeps showing
// the last good data and looks perfectly healthy
// -> turn ON refresh failure notifications. Nothing else tells you.
// SHARING -- these are not the same thing
// Workspace where artefacts live; access for BUILDERS
// App packaged read-only version; what CONSUMERS get
// Share grants access to the DATASET as well -- the
// recipient may build their own reports over all of it
// ROW-LEVEL SECURITY -- define in the model
[Region] = USERPRINCIPALNAME()
// or via a mapping table:
[Region] IN
SELECTCOLUMNS(
FILTER(UserRegion, UserRegion[email] = USERPRINCIPALNAME()),
"r", UserRegion[region])
// ...then ASSIGN the role in the service. Both halves required.
// A role defined but assigned to nobody is indistinguishable
// from one that works -- until an audit.
// TEST IT, DO NOT ASSUME IT
// Modeling -> View as -> pick the role
// CONFIRM THE TOTALS CHANGE.
// A rule filtering nothing looks exactly like no rule at all.
// RLS filters rows. It does NOT hide columns or measure names.Key points
- Refresh fails silently and the report keeps showing stale data. Enable failure notifications, because nothing in the report itself indicates the data is old.
- Sharing a report grants access to its dataset. Publish an app for consumers instead, or people receive far more than the page you meant to send.
- An RLS role must be defined in the model and assigned in the service. Test with 'View as' and verify the totals actually change — a rule that filters nothing looks identical to one that works.
Practice challenge
A regional manager reports seeing every region despite RLS being configured. Give the two halves that must both be done, the exact test that proves it works, and say why this failure is usually found by an auditor rather than a developer. Also name the reason a report can show last week's data with no error.
Half 1: define the role and its filter expression in the model
Half 2: assign that role to the user or group in the service - both are required
Test: Modeling > View as > select the role, and confirm the totals actually change
Why found late: a role that filters nothing produces a report identical to one with no security at all, so nothing looks wrong until someone checks entitlements
Stale data: refresh is failing silently - the report keeps serving the last good data, so enable refresh failure notificationsHalf 1: ______
Half 2: ______
Test: ______
Why found late: ______
Stale data with no error because: ______Show a hint
- Defining a role and assigning it are separate steps in separate places
- Verify the numbers differ, not that the role exists
Check yourself
1. The report shows last week's numbers and no error. Most likely?
Show answer
B. Refresh is failing silently — the report shows the last good data
2. What does sharing a report also grant?
Show answer
B. Access to the underlying dataset
3. How do you verify RLS is working?
Show answer
B. View as the role and confirm the totals change
Common questions
Do I need any background to start Power BI?
No. This track begins at its own beginning and assumes nothing, and the first lesson explains what the thing is before showing you any syntax.
How long does the Power BI track take?
About 134 minutes of reading across 8 lessons, plus the practice challenges and 24 multiple-choice questions, which is where the time actually goes.
Is it free?
Yes, and there is no account. Everything runs in your browser.
More: all 15 tracks · what employers actually ask for · the full syllabus