The Story
Follow the narrative to build a mental model of how everything connects.
The Connection
Every Power BI journey begins with a connection. The analyst opens Get Data and faces a wall of 100+ connectors -SQL Server, Excel, SharePoint, web APIs, Dataverse, and more. Each source has its own authentication story: OAuth for cloud services, Windows credentials for on-prem databases, and API keys for web sources. Privacy levels (Public, Organisational, Private) control how these sources can be combined.
The Workshop
Raw data enters the Power Query workshop, where the real transformation begins. Column Quality reveals that 3% of the dates are errors and 12% of postal codes are empty. The analyst removes duplicates, replaces nulls with defaults, splits the full address column, and unpivots the monthly columns into a proper fact table. Every click generates an M step that can be reviewed, reordered, or edited.
The Blueprint
With clean data loaded, the analyst lays out the model blueprint. A central Sales fact table links to Customer, Product, Date, and Store dimensions in a classic star schema. Each relationship is one-to-many from dimension to fact, with single-direction cross-filtering. The Date table is marked as the official date table, unlocking built-in time intelligence.
The Formula
DAX is the language of the semantic model. The analyst writes their first measure: Total Sales = SUM(Sales[Amount]). Then CALCULATE enters the picture, wrapping that measure with filter modifications. Time intelligence follows -YTD sales, same period last year, rolling 3-month averages. Each measure lives in the model, not in a visual, making it reusable across every report page.
The Canvas
The report canvas comes alive with visuals. A clustered bar chart compares regional sales. A line chart shows the 12-month trend. Cards display KPI totals. Conditional formatting highlights underperformers in red. The analyst adds a slicer for year, configures cross-filtering between charts, and applies a custom JSON theme so everything matches the corporate brand.
The Navigation
A flat report is just the beginning. The analyst adds bookmarks to capture different page states -an executive overview, a drill-down into marketing spend, a what-if scenario. Buttons linked to bookmarks create a guided navigation experience. Drillthrough pages let users right-click any product to jump to its detail page. Sync slicers keep the date filter consistent across all pages.
The Copilot
With Copilot enabled on the workspace, the analyst asks it to create a summary page. Copilot builds a layout with key visuals and a narrative that explains the trends in natural language. The narrative visual updates dynamically as slicers change, giving executives plain-English insights without needing to interpret charts.
The Launch
The report is published to a workspace. The analyst sets up a scheduled refresh -every morning at 6 AM through the on-premises gateway. They create an app to package the workspace content into a polished read-only experience for business users. Subscriptions send PDF snapshots to executives who prefer email. Alerts notify the sales director when daily revenue drops below threshold.
The Vault
Security is the final layer. The analyst defines RLS roles in Power BI Desktop -a "Region Manager" role with a DAX filter [Region] = USERPRINCIPALNAME(). In the service, they assign Azure AD security groups to each role. Sensitivity labels from Microsoft Purview mark the financial data as Confidential. Workspace roles ensure only the BI team can edit while the broader organisation views through the app.
The Insight
The story comes full circle when users discover insights they did not expect. The Analyze feature explains why a sudden spike occurred. Grouping and binning reveal hidden segments in continuous data. AI visuals like Key Influencers and Decomposition Tree let business users explore drivers without writing DAX. Anomaly detection flags outliers automatically. The data analyst has built not just a report, but an analytical platform.
