The Power BI Fellowship

Intermediate Year 1 – 3

Now you write M and DAX by hand and you start using DAX Studio and Tabular Editor. The goal of this level is that filter context stops being magic. Every measure you write here should be one you can explain line by line.

Advanced Power Query and M

Data: SurveyWide RawOrdersExport ExchangeRates FactSales

Assignments

Unpivot the wide survey

A · Guided 20 min · uses SurveyWide
  1. Load SurveyWide. Select StoreCode, StoreName, RegionKey → Unpivot Other Columns.
  2. Split Attribute on "-" into Month and Year. Build a real date (first of month) from them.
  3. Move Target-2026 into its own query (it is not a month) before unpivoting, then merge it back as a column.
  4. Open Advanced Editor and read the generated M top to bottom. Rename every step so the pipeline reads like a sentence.
Expected result: 36 rows (12 stores × 3 months) with a MonthDate column and a Target column repeated per row.

Write a custom function

A · Guided 30 min · uses RawOrdersExport
  1. Create a blank query with this function: (t as text) as nullable number => let s = Text.Remove(t, {"$"," ","U","S","D"}), n = try Number.From(s) otherwise null in n
  2. Name it fnCleanMoney. Invoke it on Unit Price via Add Column → Invoke Custom Function.
  3. Write a second function fnParseDate that tries three cultures in order: "en-US", "en-GB", then Date.FromText with no culture.
  4. Apply it to Order Dt and verify every row.
Expected result: Zero errors; "N/A" becomes null; "09-01-2026" becomes 9 Jan 2026 or 1 Sep 2026, and you can state which and why (order of cultures tried).

Parameters and a switchable environment

A · Guided 20 min · uses Any
  1. Create a text parameter Environment with values Dev, Test, Prod.
  2. Create a query Config with a table mapping Environment → source path (use three copies of a CSV in three OneDrive folders).
  3. Make the main source step read the path from Config via a lookup on the parameter.
  4. Switch the parameter and refresh. Then publish and change the parameter in the Service (Settings → Parameters).
Expected result: One PBIX, three environments, no code edits.

Look up the last known exchange rate

A · Guided 30 min · uses FactSales, ExchangeRates
  1. Rates only exist on Mondays. For each FactSales row you need the EUR rate from the most recent Monday on or before OrderDate.
  2. Approach A (M): add WeekStart = Date.StartOfWeek([OrderDate], Day.Monday) to FactSales and merge on WeekStart = RateDate and Currency = "EUR".
  3. Approach B (M, harder): buffer ExchangeRates with Table.Buffer, then per row List.Max of dates ≤ OrderDate. Compare refresh time.
  4. Add Net Sales EUR = Net Sales × rate.
Expected result: 99 of the 100 rows get a rate. The one order placed before the first Monday rate (5 Jan 2026) stays null until the business agrees on a fallback. Approach B is noticeably slower even at 100 rows; write down the two timings.

Folding-safe incremental pattern

A · Guided 20 min · uses FactSales via a SQL source if you have one, otherwise any date-filtered CSV
  1. Create RangeStart and RangeEnd Date/Time parameters.
  2. Filter FactSales: OrderDate >= RangeStart and OrderDate < RangeEnd.
  3. Check View Native Query at that step. If it folds, you are ready for incremental refresh; if not, move the filter earlier.
Expected result: You know which of your steps break folding (Table.AddColumn with a custom function usually does).

Interview questions

  • What is the difference between Table.Buffer and List.Buffer and when do they help?
  • Explain "each" in M.
  • How do you handle a merge where the key has different casing or trailing spaces?
  • When does a query stop folding? Give five common breakers.
  • Why does "Enter Data" not scale and what do you replace it with?
  • What is the difference between Table.SelectRows with a date filter and applying the filter on a parameter for incremental refresh?

Assessment

After unpivoting SurveyWide correctly (Target excluded), row count is:

  • 12
  • 36
  • 48
  • 24

try Number.From("N/A") otherwise null returns:

  • 0
  • an error
  • null
  • "N/A"

Which step is most likely to stop query folding on SQL Server?

  • Filter rows
  • Rename column
  • Add index column
  • Remove column

Write an M function that takes a table and a list of column names and returns the table with those columns trimmed, cleaned and capitalized. Apply it to RawOrdersExport.

Table.TransformColumns with List.Transform to build the transform list.

Modeling: many-to-many, bridges and grain

Data: FactSales FactBudget CustomerTargets DimCustomer DimEmployee

Assignments

Budget vs actual at different grains

A · Guided 40 min · uses FactSales, FactBudget
  1. FactBudget is at Month × Region × Category. FactSales is at Day × Product. They cannot join directly.
  2. Create a DimCategory table = DISTINCT(DimProduct[Category]) and relate it to both DimProduct[Category] (1:*) and FactBudget[Category] (1:*).
  3. Add YearMonth to your Dates table, and relate FactBudget[YearMonth] to a new DimMonth = DISTINCT(Dates[YearMonth]) which also filters Dates (1:*).
  4. Relate DimRegion to FactBudget. Matrix: Category rows, YearMonth columns, Net Sales and Sum of BudgetAmount and Variance %.
Expected result: Every Category × Month cell shows both actual and budget. Product-level rows show budget blank (correct: budget is not defined at product grain).

Many-to-many with a bridge

A · Guided 30 min · uses CustomerTargets, DimCustomer
  1. CustomerTargets keys on Segment + LoyaltyTier, which is not unique in either table.
  2. Build a bridge: BridgeSegTier = SUMMARIZE(DimCustomer, DimCustomer[Segment], DimCustomer[LoyaltyTier]) plus a key column Segment & "|" & Tier.
  3. Add the same key column to DimCustomer and CustomerTargets. Relate Bridge 1:* to both, with the Bridge → DimCustomer relationship set to bi-directional (only this one).
  4. Table: Segment, LoyaltyTier, Net Sales, Sum of TargetRevenue.
  5. Then do it again with a direct many-to-many relationship (cardinality *:*) and compare totals and behaviour.
Expected result: Both approaches give the same numbers per row; the *:* version shows a warning icon and behaves differently on the total row.

Parent-child hierarchy

A · Guided 30 min · uses DimEmployee, FactSales
  1. Add Path = PATH(DimEmployee[EmployeeKey], DimEmployee[ManagerKey]).
  2. Add Level1 = LOOKUPVALUE(DimEmployee[EmployeeName], DimEmployee[EmployeeKey], PATHITEM([Path], 1, INTEGER)), and Level2, Level3, Level4.
  3. Build a hierarchy Level1 → Level4 and a matrix of Net Sales by it.
  4. Write Team Sales = CALCULATE([Net Sales], FILTER(ALL(DimEmployee), PATHCONTAINS(DimEmployee[Path], SELECTEDVALUE(DimEmployee[EmployeeKey])))) so a manager row includes their reports.
Expected result: Grace Hollis row = grand total. Dana Whitfield row = West total. Nina Kowalski (Finance) row has sales blank.

Diagnose a bad grain

A · Guided 15 min · uses FactInventory
  1. Load FactInventory and relate it to DimProduct and Dates.
  2. Card: Sum of QuantityOnHand. Explain why the number is meaningless.
  3. Do not fix it yet: that is the semi-additive assignment in Advanced DAX. Just write one sentence in a text box explaining the correct aggregation.
Expected result: The sentence contains the words "last snapshot" or "last date".

Interview questions

  • How do you handle budget at month grain against sales at day grain?
  • Bridge table vs native many-to-many: which do you choose and why?
  • What is a degenerate dimension?
  • Explain the "expanded table" concept in DAX.
  • When is a snapshot fact table the right design?

Assessment

FactBudget and FactSales both have RegionKey. Correct model:

  • Join FactSales to FactBudget on RegionKey
  • DimRegion filters both facts
  • Merge them in Power Query
  • Use a *:* between them

PATH(EmployeeKey, ManagerKey) for Lily Chang (8) returns:

  • "8"
  • "1|2|6|8"
  • "8|6|2|1"
  • "2|6|8"

Which relationship direction is required for the bridge pattern to work?

  • Bridge ← Fact
  • Bridge ↔ Dimension (both)
  • Fact ↔ Bridge
  • No relationships, use TREATAS

Build the bridge for CustomerTargets and report the Q1 target versus Net Sales for Consumer × Gold.

If Net Sales is blank, the bridge → DimCustomer relationship is not bi-directional.

DAX: filter context, time intelligence and iterators

Data: FactSales DimProduct DimCustomer FactBudget

Assignments

CALCULATE modifiers, all of them

A · Guided 40 min · uses FactSales, DimProduct
  1. Write and put side by side in a table by Category with a Region slicer set to West: [Net Sales], CALCULATE([Net Sales], ALL(DimProduct)), CALCULATE([Net Sales], ALLSELECTED(DimProduct)), CALCULATE([Net Sales], ALLEXCEPT(DimProduct, DimProduct[Category])), CALCULATE([Net Sales], REMOVEFILTERS()), CALCULATE([Net Sales], KEEPFILTERS(DimProduct[Category] = "Water")).
  2. Now add a visual-level filter Category ≠ Apparel and watch which columns change.
  3. Write a one-line comment above each measure describing which filters it keeps.
Expected result: ALL ignores the visual filter; ALLSELECTED respects it. KEEPFILTERS gives blank on non-Water rows instead of the Water number.

Time intelligence set

A · Guided 40 min · uses FactSales, Dates
  1. Extend FactSales mentally: it only spans Jan–Mar 2026, so YoY will be blank. That is fine; you are testing the pattern, not the data.
  2. Write: Sales MTD = TOTALMTD([Net Sales], Dates[Date]), Sales QTD, Sales PM = CALCULATE([Net Sales], DATEADD(Dates[Date], -1, MONTH)), MoM % = DIVIDE([Net Sales] - [Sales PM], [Sales PM]), Sales Rolling 30 = CALCULATE([Net Sales], DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -30, DAY)).
  3. Line chart by Date with Net Sales and Sales Rolling 30.
  4. Break it: remove the "mark as date table" flag and observe what happens to Sales PM. Restore it.
Expected result: Feb MoM % is computed. Sales PM for January is blank. Rolling 30 is smooth. Unmarking the date table does not break these measures because you referenced Dates[Date] directly; then delete the relationship and watch them break.

Iterators and row context

A · Guided 30 min · uses FactSales, DimCustomer
  1. Write Avg Sales per Customer = AVERAGEX(VALUES(DimCustomer[CustomerKey]), [Net Sales]) and compare to DIVIDE([Net Sales], [Customers Buying]).
  2. Write Best Product Sales = MAXX(VALUES(DimProduct[ProductName]), [Net Sales]) and Best Product = the name, using TOPN(1, VALUES(DimProduct[ProductName]), [Net Sales]) wrapped in MAXX to return a text.
  3. Write Customer Rank = RANKX(ALL(DimCustomer[CustomerName]), [Net Sales], , DESC, DENSE). Put it in a table by CustomerName. Then make it respect the slicer by swapping ALL for ALLSELECTED.
  4. Write Customers Over 500 = COUNTROWS(FILTER(VALUES(DimCustomer[CustomerKey]), [Net Sales] > 500)).
Expected result: Rank 1 customer is unique. Customers Over 500 total is not the sum across regions (a customer can appear in two regions if reps cross regions).

Variables and debugging

A · Guided 20 min · uses FactSales
  1. Rewrite MoM % with VAR cur, VAR prev, VAR delta and RETURN. Add a temporary RETURN prev to inspect the intermediate value, then restore.
  2. Write a measure that returns a text: "Net " & FORMAT([Net Sales], "$#,##0") & " across " & [Orders] & " orders". Put it in a card.
  3. Install DAX Studio, connect to your open PBIX, and run: EVALUATE SUMMARIZECOLUMNS(DimProduct[Category], "Sales", [Net Sales]). Then turn on Server Timings and note the SE vs FE split.
Expected result: You can state the FE and SE milliseconds for one query.

Budget variance the right way

A · Guided 20 min · uses FactBudget, FactSales
  1. Budget = SUM(FactBudget[BudgetAmount]). Variance = [Net Sales] - [Budget]. Variance % = DIVIDE([Variance], [Budget]).
  2. Attainment Status = SWITCH(TRUE(), ISBLANK([Budget]), "No budget", [Variance %] >= 0, "On target", [Variance %] >= -0.1, "Watch", "Behind").
  3. Use Attainment Status for conditional formatting (field value) on a matrix.
Expected result: Product-level rows show "No budget" because budget is only at category grain.

Interview questions

  • ALL vs ALLSELECTED vs REMOVEFILTERS: one sentence each.
  • Why does a measure inside SUMX need CALCULATE, and why don't you write it?
  • What does DATEADD do when the previous period has no data, and how does that differ from SAMEPERIODLASTYEAR?
  • RANKX gives all 1s. Why?
  • What is the difference between VALUES and DISTINCT?
  • Explain context transition and one case where it hurts performance.

Assessment

CALCULATE([Net Sales], ALLSELECTED(DimProduct)) in a table by Category with a visual filter Category ≠ Apparel and slicer Region = West shows:

  • All-region, all-category total
  • West total excluding Apparel, on every row
  • West total including Apparel
  • The row's own value

TOTALMTD([Net Sales], Dates[Date]) is equivalent to:

  • CALCULATE([Net Sales], DATESMTD(Dates[Date]))
  • CALCULATE([Net Sales], ALL(Dates))
  • SUMX(Dates, [Net Sales])
  • CALCULATE([Net Sales], DATEADD(Dates[Date],-1,MONTH))

A FILTER over FactSales (100 rows) calling [Net Sales] per row performs:

  • 100 storage-engine scans, very fast
  • 100 context transitions, slow at scale
  • One scan
  • It errors

Write a measure "Top 3 Products Sales" that returns Net Sales for the top 3 products by Net Sales within the current filter context, and a second measure "Top 3 Share" = its share of total. Verify Top 3 Share for Camping.

CALCULATE([Net Sales], TOPN(3, VALUES(DimProduct[ProductName]), [Net Sales])).

Interactive reports: bookmarks, drillthrough, tooltips, field parameters

Data: FactSales DimProduct DimCustomer

Assignments

Drillthrough with a back button

A · Guided 25 min · uses Full star
  1. Create page "Customer Detail" with a drillthrough field CustomerName. Add a table of their orders, a card for their Net Sales, and a line chart by date.
  2. Turn on "Keep all filters". Add a back button (auto-created) and style it.
  3. From the summary page, right-click a customer bar → Drillthrough. Verify the Region slicer selection is carried over.
  4. Add a second drillthrough field ProductName to the same page and observe it now requires either.
Expected result: Right-click menu shows "Customer Detail". Back button returns to the source page.

Report page tooltip

A · Guided 20 min · uses Full star
  1. New page, Page information → Allow use as tooltip, size Tooltip.
  2. Add a card Net Sales, a small bar chart of SubCategory, and a text measure "X orders, Y customers".
  3. On the summary page, set the Category bar chart Tooltip → Report page → your page.
Expected result: Hovering over "Water" shows Water-only numbers inside the tooltip.

Field parameters for a self-service chart

A · Guided 20 min · uses Full star
  1. Modeling → New parameter → Fields. Add Net Sales, Orders, Margin %, Total Qty as one parameter "Measure Picker".
  2. Create a second field parameter "Axis Picker" with Category, RegionName, Segment, MonthName.
  3. One clustered bar chart driven by both parameters, two slicers to switch them.
Expected result: Sixteen chart combinations from one visual. Look at the generated DAX for the parameter table and explain the third column.

Bookmark toggle done right

A · Guided 25 min · uses Full star
  1. Two visuals stacked exactly on top of each other: a column chart and a table of the same data.
  2. Two bookmarks "Show chart" / "Show table" that only change Display (Data and Current page unchecked), created with the Selection pane.
  3. Two buttons whose visible state is swapped by the same bookmarks so the active one looks pressed.
  4. Group the four objects and name the group; ungroup and note what happens to the bookmarks.
Expected result: Clicking either button never resets slicers. Bookmarks have Data unchecked.

Dynamic titles and formatting measures

A · Guided 15 min · uses Full star
  1. Title measure = "Net sales for " & IF(ISFILTERED(DimRegion[RegionName]), SELECTEDVALUE(DimRegion[RegionName], "multiple regions"), "all regions") & " – " & FORMAT(MAX(Dates[Date]), "MMM yyyy").
  2. Use it as the fx title on a chart.
  3. Colour measure = IF([Margin %] < 0.4, "#B23A2E", "#1F7A4D") used as fx on a bar colour.
Expected result: Title changes with the slicer. Bars change colour without conditional formatting rules.

Interview questions

  • Bookmark "Data" checkbox: when do you leave it on, when off?
  • Drillthrough vs tooltip vs drill down: when each?
  • What is the hidden third column in a field parameter table used for?
  • How do you conditionally show a visual based on a slicer selection?
  • Why is a 30-visual page slow even though the model is small?

Assessment

"Keep all filters" on drillthrough means:

  • Slicers on the target page are locked
  • Filters from the source page context are carried over
  • The target page ignores filters
  • Filters are saved to a bookmark

A field parameter is:

  • A Power Query parameter
  • A calculated table with NAMEOF references usable in visuals
  • A what-if slider
  • A bookmark group

Which formatting property can be driven by a measure returning a hex colour?

  • Only background of cells
  • Most colour properties with an fx button, including bar colours and titles
  • None
  • Only conditional formatting rules

Build a page where one button toggles between "Actual" and "Budget" in a single matrix without resetting any slicer. Prove it by selecting Region = East, toggling twice, and confirming East stays selected.

Two bookmarks with Data unchecked, or one field parameter slicer styled as buttons.

Row-level security

Data: UserRegionMapping DimRegion FactSales DimEmployee
Why it matters
Security is the one defect a company cannot fix with an apology: a manager seeing another region's payroll is a reportable incident.
Typical production failure
A page filter or a hidden visual is used as "security"; anyone with Build permission or Analyze in Excel sees everything.
When to use it
Whenever different people may see different rows of the same model.
When not to
When groups need entirely different models anyway, or when security must hold for direct SQL access too (secure the source as well).

Assignments

Static roles

A · Guided 20 min · uses DimRegion
  1. Manage roles → new role "West" with DAX filter on DimRegion: [RegionName] = "West". Create South, East, Central the same way.
  2. View as → West. Note that the Region slicer now shows only West.
  3. Publish, add a test user to the West role in the Service, open the report as them.
Expected result: Cards show West totals only. The user cannot see other regions even by editing the report.

Dynamic RLS with a mapping table

A · Guided 40 min · uses UserRegionMapping, DimRegion
  1. Load UserRegionMapping. Relate UserRegionMapping[RegionKey] → DimRegion[RegionKey]? No: the mapping is many rows per user, so relate DimRegion (1) → UserRegionMapping (*) and set the relationship to bi-directional with "Apply security filter in both directions" on.
  2. Role "Dynamic": filter on UserRegionMapping: [UserEmail] = USERPRINCIPALNAME().
  3. Handle the "All" row: change the DimRegion filter instead: VAR u = USERPRINCIPALNAME() RETURN CONTAINS(UserRegionMapping, UserRegionMapping[UserEmail], u, UserRegionMapping[RegionKey], DimRegion[RegionKey]) || CALCULATE(COUNTROWS(FILTER(UserRegionMapping, [UserEmail] = u && [AccessLevel] = "All"))) > 0
  4. View as → Other user → nina.kowalski@northwind.example. Then grace.hollis@.
Expected result: Nina sees West and East only. Grace sees all four. Dana sees West.

RLS by org hierarchy

A · Guided 30 min · uses DimEmployee, FactSales
  1. Role "MyTeam": filter DimEmployee with PATHCONTAINS(DimEmployee[Path], LOOKUPVALUE(DimEmployee[EmployeeKey], DimEmployee[Email], USERPRINCIPALNAME())).
  2. View as dana.whitfield@: she should see herself and everyone under her.
  3. View as lily.chang@: she sees only herself.
Expected result: Dana: 5 employees' sales. Lily: 1.

Test that RLS cannot be bypassed

A · Guided 15 min · uses Full model
  1. As the restricted user, try Analyze in Excel and try building a new report on the model (needs Build). Confirm RLS still applies.
  2. Add a measure CALCULATE([Net Sales], ALL(DimRegion)) and confirm it still returns only the allowed regions.
  3. Publish to a second workspace and note that role membership does not travel with it.
Expected result: ALL does not remove RLS filters; role membership must be reassigned in each workspace.

Interview questions

  • Why can ALL() not bypass RLS?
  • Static vs dynamic RLS: trade-offs.
  • What happens if a user is in two roles?
  • Does RLS apply to workspace Admins, Members and Contributors?
  • How does RLS interact with a bi-directional relationship?

Assessment

A user is in roles West and East. They see:

  • Nothing
  • West only
  • West and East
  • Everything

USERPRINCIPALNAME() in Desktop returns:

  • Blank
  • Your signed-in Desktop account email
  • The workspace owner
  • "admin"

Workspace Member opens an RLS-secured report. They see:

  • Their role's rows
  • Everything
  • Nothing
  • An error

Implement dynamic RLS for UserRegionMapping including the "All" row. Prove Nina sees exactly two regions with a screenshot of View as.

If Nina sees all regions, the "All" check is missing a filter on AccessLevel; if she sees none, the relationship isn't bi-directional for security.

Service: gateways, refresh, workspaces and apps

Data: FactSales

Assignments

Install and use a gateway

A · Guided 40 min · uses A local CSV or SQL Express
  1. Install the on-premises data gateway (standard mode) on your machine. Register it.
  2. Add a data source (File or SQL). Map the report's data source to it in the semantic model settings.
  3. Schedule refresh. Then stop the gateway service and trigger refresh: read the exact error.
  4. Add a second admin to the gateway.
Expected result: Refresh succeeds with gateway online and fails with a gateway-offline message when it is not.

Workspace strategy

A · Guided 20 min · uses Your report
  1. Create workspaces Northwind-Dev, Northwind-Test, Northwind-Prod. Publish to Dev.
  2. Move the semantic model to a separate workspace "Northwind-Models" and make the report connect live to it (Desktop → Get Data → Power BI semantic models). Republish.
  3. Grant Build on the model to a colleague and have them create a report in their own workspace.
Expected result: One model, two reports in different workspaces. Deleting the report does not delete the model.

Refresh diagnostics

A · Guided 20 min · uses Any
  1. Open refresh history and download the refresh log.
  2. Set failure notifications to a colleague.
  3. Configure refresh 8 times a day and note the Pro limit; then read the capacity limit in the settings tooltip.
Expected result: You can name the daily refresh limit for Pro and for capacity.

App with audiences

A · Guided 20 min · uses Your report
  1. Create an App with two audiences: Managers (all pages) and Reps (one page only).
  2. Add a navigation section and a link to an external page.
  3. Publish, then update one visual, republish the report, update the App. Observe the difference.
Expected result: Rep audience cannot see the manager page even by URL.

Interview questions

  • Personal mode vs standard mode gateway.
  • How do you avoid 50 reports each with their own copy of the model?
  • Scheduled refresh keeps failing at 6:00 but works manually. Where do you look?
  • What is the difference between "Update app" and "Republish"?
  • Where do you put a shared date table and shared measures so every team reuses them?

Assessment

A DirectQuery report to on-prem SQL requires:

  • Personal gateway
  • Standard gateway
  • No gateway
  • A dataflow

Live connection to a shared model lets you:

  • Add tables
  • Add report-level measures
  • Edit Power Query
  • Change relationships

Which workspace role is the minimum to publish an App?

  • Viewer
  • Contributor
  • Member
  • Admin

Set up gateway refresh for a local CSV, break it, fix it, and document the three settings you had to touch in the Service.

Gateway mapping, data source credentials, schedule.