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.
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
- New measure: Home or Table tools > New measure, type the formula in the bar. Where a measure lives (its home table) does not affect results. Format it from the Measure tools tab (currency, percent, decimals).
- New column (calculated column) and New table (Table tools > New table) for DAX tables.
- Quick measure: Home > Quick measure for templates such as year-over-year change; read the DAX it writes, since it is a good learning aid.
- DAX query view: left rail icon. Write
EVALUATEqueries, run them against the open model, useDEFINE MEASURE Sales[Test] = ...to try measures before saving, then "Update model with changes". The Quick queries on a table or column write starter queries. Copilot can help draft DAX in this view. - TMDL view: shows the model as TMDL text, editable, with Apply. Use it for bulk changes and for scripts such as user-defined functions or object-level security.
- Visual calculations (preview in some builds): calculations defined inside a visual using the visual's own rows, such as RUNNINGSUM, MOVINGAVERAGE, PREVIOUS, NEXT, FIRST, LAST. Right click a visual > New visual calculation. They are not in the semantic model.
- Performance analyzer: View > Performance analyzer > Start recording > Refresh visuals; copy a query to DAX query view to tune it.
Parameters and dynamic interactivity
- Numeric range parameter: Modeling > New parameter > Numeric range: creates a table
Parameterusing GENERATESERIES and a measureParameter Value = SELECTEDVALUE(...); add the slicer. - Field parameter: Modeling > New parameter > Fields: choose measures or columns, it creates a calculated table with
NAMEOFentries; use a slicer on it, and put the parameter field in a visual's well to switch measures/axes. - Calculation group: Model view > Calculation group (Home > Calculation group), name the group and the column, add calculation items with
SELECTEDMEASURE()formulas (YTD, PY, YoY %). Calculation groups require "discourage implicit measures" to be turned on, which Desktop does when you create the first group. Format a percent item using the item's Format string expression:"0.0%". - Bookmarks: View > Bookmarks, to capture a visual state; with the selection pane, toggle visuals for layout switching.
- Conditional formatting: visual Format > select the element (cell elements for tables and matrices, columns for bars, data labels) > fx > Format style: Gradient, Rules, or Field value (a color measure). Icons for KPIs and arrows.
- Dynamic title/subtitle: fx next to the Title text, choose a measure that returns text.
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
- Remove columns you do not need, set types, and rename columns to business names before load.
- Merge Queries to flatten snowflake dimensions; Append Queries to stack similar tables.
- Query folding: right click a step > View native query; if greyed out the step is not being pushed to SQL Server. Keep filtering and column selection early.
- Create a Calendar with List.Dates (see patterns topic) or reference an existing DimDate and filter its range.
- Do not use a native SQL statement for the learning server: use the table navigation and Power Query steps so students learn M and folding.
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
- 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.
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.