Downloads

Power BI Desktop how-to

Model view, DAX query view, parameters, security, publishing and PBIP projects.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Load LearnSQL data into Power BI Desktop and build a model in Model view.
  • Write measures in DAX query view and lay out report pages.
  • Add parameters and row-level security, then publish and refresh a report.
  • Save a report as a PBIP project and keep it under source control.

Get the LearnSQL data in

Home > Get data > SQL Server. Server learnsql.cpetoday.com,1433 (comma), Database contoso (or another), Data Connectivity mode Import. Sign in with the Database tab (SQL Server login that comes with the course), accept the certificate prompt. Select the tables in the Navigator and click Transform Data to filter and shape before loading. In the contoso database the fact table contoso.FactSales is a ~100,000-row sample; the whole 1,048,575 rows are in contoso.FactSalesFull. Keys in some dimensions are float: change them to Whole number in Power Query so relationships work. Open an existing generated project: extract the zip completely, open the .pbip file in Power BI Desktop, click Refresh, sign in when prompted.

Import: copy the data into the file Database source Model in memory Report visuals refresh instant Fast visuals and full DAX. Data is as new as the last refresh. DirectQuery: leave the data at the source Report visuals Database live source query rows back Always current. Slower, with some DAX limits.
Import keeps a copy in the model. DirectQuery leaves the data at the source and queries it as you click.

Model view

Open Model view (left rail) to see tables and relationships. Drag a key column onto another to create a relationship; double click a line to set cardinality and direction, or Manage relationships. Select a column and set Data category, Sort by column, Format, Summarization, Hidden. Group measures with Display folder (Properties pane). Create a hierarchy: right click a column > Create hierarchy, then drag more columns onto it. Mark the calendar: select the Calendar table > Table tools > Mark as date table.

Report view basics

Pick a visual in the Visualizations pane, drag fields to wells (Category/X-axis, Values/Y-axis, Legend, Small multiples, Tooltips). Sort with the visual's ... menu. Format on the paint-roller tab. Slicers: Dropdown or List or Between styles under Visual > Slicer settings. Sync slicers across pages: View > Sync slicers. Bookmarks, buttons and selection pane give interactivity. Edit interactions (Format > Edit interactions) controls which visuals a visual filters. Drillthrough: a page with fields in Drill through filters; right click a data point > Drill through. Report page tooltips: create a page, Page information > Allow use as tooltip, set the page size to Tooltip; a visual uses it via Format > General > Tooltips > Type: Report page. Page navigation buttons: Insert > Buttons > Navigator, or a button with the Action type Page navigation.

Writing DAX in Desktop

Parameters and dynamic interactivity

Row-level security

Modeling > Manage roles > Create, pick the table, enter a DAX filter, for example static [Region] = "West" or dynamic [Email] = USERPRINCIPALNAME() on a security table related to the model. Test with Modeling > View as. After publishing, assign members to the role in the service (Dataset > Security). Roles filter the table and everything related through single-direction relationships. Object-level security (hide columns or tables) needs TMDL or Tabular Editor: columnPermission Employees = none.

Power Query tips for modeling

Publish and refresh

Home > Publish to a Power BI workspace (needs a Power BI account). Schedule refresh in the service; import models on a cloud workspace refreshing from a server on the internet usually need credentials set in Dataset settings; an on-premises data gateway is for sources inside a private network. The LearnSQL server is reachable from the internet, so a gateway is not needed. Share with a workspace app or a link; set permissions on the workspace and dataset; for Premium/PPU features (incremental refresh, paginated reports) check licensing.

PBIP projects and source control

Typical report-building order

  1. Connect and shape (Power Query). 2. Model view: relationships, keys hidden, Calendar marked. 3. Base measures (Total Sales, Cost, Margin) in a folder. 4. Time intelligence and percent measures. 5. Visuals with a consistent grid (pos on a 1920 x 1080 canvas), cards on top, trends and breakdowns below, slicers on the left. 6. Tooltips, drillthrough, and bookmarks. 7. Performance check. 8. Security and publish.
1 Get data SQL Server, Import Choose tables 2 Transform Filter, type, clean in Power Query 3 Model Relate tables, add a Date table 4 Measures Write DAX formulas Total, margin, YTD 5 Visuals Cards and charts slicers, one page 6 Publish Publish, refresh then share
The usual order for a Power BI report. Each stage builds on the one before.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.