<!-- https://learnsql.cpetoday.com/analyze/power-bi-desktop -->
# Power BI Desktop how-to

## 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.

> Diagram: Import copies data into the model at refresh, so visuals are fast. DirectQuery sends a query to the database each time a visual changes, so the data is live but slower.

## 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 `EVALUATE` queries, run them against the open model, use `DEFINE 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 `Parameter` using GENERATESERIES and a measure `Parameter Value = SELECTEDVALUE(...)`; add the slicer.
- **Field parameter**: Modeling > New parameter > Fields: choose measures or columns, it creates a calculated table with `NAMEOF` entries; 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
Save as Power BI project (.pbip) (File > Options > Preview features or the Save as > Power BI Project): the model becomes TMDL text and the report becomes PBIR JSON in folders, friendly to Git. Files named `definition.pbir`, `definition.pbism`, `*.SemanticModel`, `*.Report`. The generator in this MCP writes those folders (create_power_bi_project).

## 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.

> Diagram: Six stages of a Power BI report, in order: get data, transform, model, measures, visuals and publish.
