Power BI Deep Dive
Topic 5 of 12 · In Business Intelligence · September 9, 2026
Get hands-on with Power BI: data modeling, relationships, DAX basics, Power Query, publishing and workspace management for real BI projects.
Overview
Power BI is Microsoft's business intelligence platform and the most widely used BI tool in the world. It connects to hundreds of data sources, transforms data with Power Query, models data with a tabular engine, visualizes insights with interactive dashboards, and publishes to the cloud for collaboration.
This topic gives you a complete hands-on introduction to Power BI as a BI professional. You will learn how to import and transform data with Power Query, build a proper data model with relationships, write DAX measures and calculated columns, design reports, publish to the Power BI Service, manage workspaces and implement row-level security. By the end, you will have built a complete, publishable BI report from scratch.
Before You Start
- Install Power BI Desktop (free) and have AdventureWorksDW or your own fact_sales and dimension tables imported and sitting in Model view
- Reopen the star schema you built for the data warehousing topic as the modelling reference while you set relationships
- Keep the list of eight mini-project measures (Total Revenue, Total Cost, Profit, Profit Margin, Total Orders, Average Order Value, Distinct Customers, Revenue MoM Growth) written down to work against
- Be able to use Get Data to connect to a CSV or SQL source without help before the lesson starts
- Have a blank one-page report ready so the KPI card row at the top is the first thing you build
Learning Objectives
- Connect to and import data from multiple sources in Power BI
- Transform data with Power Query (clean, reshape, merge, append)
- Build a star schema data model with correct relationships
- Understand relationship cardinality and cross-filter direction
- Write basic DAX measures and calculated columns
- Design interactive reports with slicers, filters and drill-through
- Publish reports to Power BI Service and manage workspaces
- Implement row-level security (RLS) for data access control
Prerequisites
Continue from SQL for Business Intelligence for query and data modeling context. Basic familiarity with data concepts (dimensions, facts, KPIs) from earlier topics will help significantly.
Getting Started with Power BI
Power BI consists of three parts:
- Power BI Desktop — the free Windows application for building reports (data model, DAX, visualizations)
- Power BI Service — the cloud platform for publishing, sharing, scheduling refreshes and collaboration
- Power BI Mobile — iOS/Android apps for viewing reports on the go
The workflow is: Desktop (build) → Service (publish and share) → Mobile (consume). You will spend most of your learning time in Desktop, then publish to Service.
Power Query: Import and Transform
Power Query (the "Transform Data" button) is Power BI's ETL engine. It lets you connect to sources, clean and reshape data, and load it into the model — all with a GUI and a formula language called M.
Common transformations:
- Remove columns — delete unnecessary fields to keep the model lean
- Change data types — ensure dates are dates, numbers are numbers
- Filter rows — remove irrelevant data before loading
- Split columns — break "First Last" into first name and last name
- Merge queries — JOIN equivalent: combine two tables on a key
- Append queries — UNION equivalent: stack tables with the same structure
- Pivot / Unpivot — reshape columns ↔ rows
- Group by — aggregate data at a different grain
// Power Query M code example: clean and reshape orders
let
Source = Csv.Document(File.Contents("orders.csv")),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
TypedColumns = Table.TransformColumnTypes(PromotedHeaders, {
{"order_date", type date},
{"revenue", type number},
{"quantity", Int64.Type}
}),
FilteredRows = Table.SelectRows(TypedColumns, each [revenue] > 0),
AddedMonth = Table.AddColumn(FilteredRows, "month",
each Date.Month([order_date]), Int64.Type)
in
AddedMonthData Modeling and Relationships
The Model view in Power BI is where you define how tables relate. A well-built model follows the star schema you learned in data warehousing: one fact table in the center, dimension tables around it.
Relationship properties:
| Property | Options | What It Means |
|---|---|---|
| Cardinality | One-to-One, One-to-Many, Many-to-One, Many-to-Many | How rows in one table relate to rows in another |
| Cross-filter direction | Single, Both | Which direction filters propagate (dim → fact = single; both = bidirectional) |
| Active | Yes / No | Only one active relationship between two tables at a time |
Best practice: always use single cross-filter direction (dim → fact). Both directions cause ambiguity and performance issues. Create role-playing dimensions (e.g. order date vs ship date) with separate inactive relationships and use USERELATIONSHIP in DAX.
-- Role-playing dimension: use inactive relationship in DAX
// Inactive relationship: fact_sales[ship_date_key] → dim_date[date_key]
Shipping Revenue =
CALCULATE(
SUM(fact_sales[revenue]),
USERELATIONSHIP(fact_sales[ship_date_key], dim_date[date_key])
)DAX Basics: Measures and Calculated Columns
DAX (Data Analysis Expressions) is the formula language of Power BI. There are two main types of calculations:
Measures — calculated on the fly at query time, using the filter context of the visual they appear in. Measures are the primary way to write KPIs. They are defined in the model and referenced by visuals.
Calculated columns — computed row by row when data is loaded, stored in the table. Use them sparingly — they increase model size and load time. Use measures instead whenever possible.
Key DAX functions to start with:
| Function | Purpose | Example |
|---|---|---|
SUM | Add values | SUM(fact_sales[revenue]) |
AVERAGE | Mean of values | AVERAGE(fact_sales[revenue]) |
COUNTROWS | Count rows in a table | COUNTROWS(fact_sales) |
DISTINCTCOUNT | Count unique values | DISTINCTCOUNT(fact_sales[customer_key]) |
CALCULATE | Modify filter context | CALCULATE(SUM(revenue), dim_product[category] = "Electronics") |
FILTER | Return a filtered table | FILTER(fact_sales, fact_sales[revenue] > 1000) |
// Basic measures
Total Revenue = SUM(fact_sales[revenue])
Total Orders = COUNTROWS(fact_sales)
Average Order Value =
DIVIDE([Total Revenue], [Total Orders], 0)
// CALCULATE changes the filter context
Electronics Revenue =
CALCULATE(
[Total Revenue],
dim_product[category] = "Electronics"
)
// CALCULATE + FILTER for complex conditions
High Value Revenue =
CALCULATE(
[Total Revenue],
FILTER(fact_sales, fact_sales[revenue] > 1000)
)Designing Reports
The Report view is where you build visuals. Power BI offers many chart types: bar, column, line, pie, map, card, table, matrix, KPI indicator and more.
Report design principles for BI:
- One page = one question — each report page should answer a single business question
- Most important number top-left — managers read left-to-right, top-to-bottom
- Slicers for interactivity — add date, region and category slicers so users can filter
- Drill-through — enable users to click a summary value and see the underlying detail
- Tooltips — add context on hover without cluttering the main visual
- Consistent formatting — same fonts, colors and number formats across all visuals
Publishing and Workspace Management
Publishing sends your Desktop file to the Power BI Service (app.powerbi.com). From there:
- Workspaces — collaborative containers where team members build and manage content. A "Sales BI" workspace might contain the sales report, dataset and dashboard.
- Datasets — the published data model. Reports connect to datasets; multiple reports can share one dataset.
- Dashboards — a single-page canvas (different from a report) that pins visuals from multiple reports for an executive overview.
- Scheduled refresh — configure the gateway and refresh schedule so data stays current without manual re-publishing.
- Sharing — share via workspace access, app (organized collection of reports), or direct link.
Row-Level Security (RLS)
RLS restricts data access at the row level so users only see the data they are authorized to see. For example, a regional sales manager sees only their region's data.
In Power BI, RLS is defined in Desktop (Model view → Manage Roles) using DAX filter expressions:
// RLS role: Regional Manager sees only their region
[region] = LOOKUPVALUE(
dim_user[user_region],
dim_user[user_email],
USERPRINCIPALNAME()
)
// Or simpler: filter dim_store by current user
[store_manager] = USERPRINCIPALNAME()Hands-On Practice
You need Power BI Desktop (free download from Microsoft).
- Download a public dataset (e.g. AdventureWorksDW or a Kaggle sales dataset). Import it into Power BI Desktop.
- In Power Query: clean the data — remove unnecessary columns, fix data types, filter nulls, rename columns to friendly names.
- In Model view: create a star schema with one fact table and at least 3 dimension tables. Set cardinality and cross-filter direction correctly.
- Create 5 measures: Total Revenue, Total Orders, Average Order Value, Distinct Customers, Revenue % of Total.
- Build a one-page report: a KPI card row at the top, a bar chart (revenue by category), a line chart (monthly trend), a table (top 10 products) and a slicer for region.
- Add drill-through from the bar chart to a detail page showing individual orders.
- Publish to Power BI Service (sign up for a free account). Create a workspace, upload your file, and schedule a refresh.
Deeper Worked Examples
Fixing a broken auto-generated data model
Step 1
Step 2
Step 3
Step 4
Step 5
Step 6
Cleaning an inconsistent raw export in Power Query
Step 1
Step 2
Step 3
Step 4
Step 5
Step 6
Common Mistakes
- Using calculated columns instead of measures — measures are computed at query time and do not bloat the model
- Setting cross-filter direction to Both on every relationship — use Single unless you have a specific reason
- Importing all columns from source tables — remove unnecessary columns in Power Query to keep the model lean
- Building calculations in the visual instead of the model — always define KPIs as DAX measures for reuse
- Ignoring relationships and using LOOKUPVALUE everywhere — relationships are faster and more maintainable
- Publishing without testing RLS — test with the View As feature before sharing with users
- Overloading one page with too many visuals — split into focused pages, one question per page
Real-World Usage
Power BI is the BI tool of choice for most Microsoft-centric organizations and increasingly popular across industries. A financial analyst builds an P&L report in Desktop, publishes it to a Finance workspace, and the CFO accesses it via a Power BI app on their phone. An operations manager has a live dashboard refreshing every 15 minutes from a SQL warehouse. A regional director has RLS applied so they only see their territory. The skills in this topic — modeling, DAX, Power Query, publishing, security — are exactly what gets you hired as a BI analyst or Power BI developer.
Mini Project
Business problem: A retail chain with 50 stores needs an executive dashboard showing revenue, profit, store performance and product category trends. The data comes from a SQL warehouse.
Dataset: use the AdventureWorksDW sample database (free download from Microsoft) or any sales dataset with fact_sales, dim_product, dim_store, dim_date and dim_customer tables.
Objective: build a complete, publishable Power BI report with a data model, measures, interactive visuals and RLS.
Task 1
Task 2
Task 3
Task 4
Task 5
Task 6
Task 7
Task 8
| Deliverable | What It Proves |
|---|---|
| Clean Power Query transformations | You can prepare data in Power BI |
| Star schema with correct relationships | You understand data modeling |
| 8 DAX measures | You can write BI-grade calculations |
| 2-page interactive report | You can design effective visualizations |
| RLS implemented and tested | You understand security and access control |
| Published and shared via Service | You can deliver BI to end users |
Key Takeaways
- Power BI = Desktop (build) → Service (publish/share) → Mobile (consume)
- Power Query: clean, transform, merge data before loading into the model
- Star schema model: one fact table, dimensions around it, Single cross-filter direction
- Measures (query-time) are preferred over calculated columns (row-time) for performance
- CALCULATE is the most important DAX function — it modifies the filter context
- Report design: one question per page, KPI cards top-left, slicers for interactivity, drill-through for detail
- Publish to Service, organize in workspaces, schedule refreshes, share via apps
- RLS restricts row-level data access using DAX filter expressions
Key Terms & Glossary
| Term | Meaning |
|---|---|
| Measure | A DAX calculation evaluated at query time under the current filter context of the visual, stored only as a definition and never duplicating data in the model. |
| Calculated column | A DAX column computed row by row when data loads and physically stored, which increases model size and should therefore be used sparingly. |
| Cross-filter direction | Whether filters propagate one way (dimension to fact, recommended) or both ways between two related tables; Both creates ambiguity and slower queries. |
| Cardinality | The type of relationship between two tables: one-to-many, one-to-one or many-to-many, describing how rows relate to one another. |
| Row-level security | DAX-based rules that restrict which rows a given user can see, for example a regional manager seeing only their region's data. |
| Power Query | Power BI's ETL engine using the M language, where import, cleaning, merging and reshaping happen before the data reaches the model. |
Learning Resources
Microsoft Learn — Power BI Guided Learning
The official, structured learning path from Microsoft covering all Power BI fundamentals.
Pragmatic Works — Power BI Training
Free and paid Power BI training videos from industry experts.
DAX Guide — dax.guide
The definitive reference for every DAX function with syntax and examples.
Guy in a Cube — Power BI (YouTube)
The most popular Power BI YouTube channel — tips, tricks and best practices.
Power BI Community
Microsoft's official community forum — search or ask questions about any Power BI topic.
SQLBI — DAX Best Practices
Expert articles on DAX optimization, data modeling and Power BI performance from Marco Russo and Alberto Ferrari.
Chapter Quiz
Chapter Quiz — Power BI Deep Dive
Desktop to Service, Power Query versus DAX, star-schema modeling, single-direction relationships and RLS.
Question 1 of 5
Which is evaluated at query time under the visual's filter context and adds nothing to model size?
Knowledge Check
End-of-Topic Exam
Hint: The mistakes section and the measure-versus-column knowledge check cover this.
Model answer: The 2025 question is best served by a measure, not a calculated column. A calculated column is computed at load, stored physically in the model, inflates the file and slows refresh. The measure Revenue 2025 = CALCULATE([Total Revenue], dim_date[year] = 2025) is evaluated at query time, adds nothing to model size and is reusable in every visual. The wrong choice quietly degrades performance as the table grows, and couples a date rule into stored data that belongs in the model.
Hint: Use the RLS role example and its callout from the topic.
Model answer: Create an RLS role in Model view with a filter such as dim_store[region] equal to LOOKUPVALUE(dim_user[user_region], dim_user[user_email], USERPRINCIPALNAME()) so each manager sees only rows matching their email, then test with View As before publishing. After publishing, assign members to the appropriate workspace or app so only authorised people can reach the report. RLS is the row-level, last-line defence and is not a substitute for workspace permissions, app access, sensitivity labels or wider governance, all of which should be combined with it.
Hint: The 'Keep transformations in Power Query, not DAX' callout plus the mistakes list.
Model answer: Power Query is the ETL stage: type casting, renaming columns, filtering nulls, splitting columns, merging and appending queries belong there, and they then run as reproducible pipeline steps before the model loads. DAX is for context-aware calculation: measures, time intelligence, CALCULATE and ranking belong in the model. Embedding cleaning in DAX, such as casting types inside a measure or filtering raw text per visual, duplicates logic, slows queries and makes models hard to maintain. Marks are gained for two clear examples on each side.