Developing Solutions with Microsoft Power BI

Introduction and Context

Microsoft Power BI is a business intelligence platform used to connect to data, transform it, develop semantic models, create interactive reports, and distribute insights. Power BI Desktop is primarily an authoring environment: analysts use it to import or connect to data, define relationships and calculations, design report pages, and validate the results before publishing to the Power BI service.

A successful Power BI solution is more than a collection of attractive charts. It combines a reliable data model, clearly defined business measures, purposeful visual design, controlled interaction, and systematic testing. Poor modelling can produce incorrect totals even when the report appears professional; conversely, a technically correct model may fail if users cannot interpret or navigate the report.

This chapter focuses on practical development with Power BI Desktop. It covers semantic modelling, report construction, filters, drill-through, bookmarks, tooltips, interactive visuals, formatting, validation, and testing.

Learning Objectives

By the end of this chapter, you should be able to:

• explain the role of a Power BI semantic model;
• distinguish fact tables, dimension tables, measures, and calculated columns;
• create and validate relationships in Power BI Desktop;
• develop interactive report pages using appropriate visuals;
• apply report-level, page-level, visual-level, slicer, and drill-through filters;
• configure drill-through pages, bookmarks, and report-page tooltips;
• format visuals for clarity, accessibility, and consistent interpretation;
• test calculations, interactions, navigation, and performance; and
• identify governance, privacy, and ethical risks in BI reporting.

1. The Power BI Solution Architecture

A typical Power BI solution has several connected layers:

1. Data sources provide transactional, operational, or external data.
2. Power Query performs extraction, transformation, and loading activities.
3. The semantic model defines tables, relationships, hierarchies, calculated columns, and measures.
4. The report layer presents data through visuals, filters, navigation, and interaction.
5. The Power BI service supports publishing, sharing, refresh, security, and collaboration.

The semantic model is the analytical foundation. It translates source structures into business concepts such as Sales, Product, Customer, Region, Date, Revenue, and Margin. A report visual does not independently understand business meaning; it queries the semantic model according to the fields and filters selected by the report author or user.

A useful design principle is to separate responsibilities. Power Query is generally used for data preparation, the model is used for relationships and reusable business logic, and the report is used for communication and exploration. Mixing these responsibilities makes solutions difficult to maintain.

2. Building a Semantic Model

2.1 Fact and dimension tables

A fact table records business events or measurable transactions. Examples include sales lines, invoices, website visits, inventory movements, or service tickets. It usually contains numeric measures and foreign keys.

A dimension table describes the entities used to analyse facts. Examples include Product, Customer, Employee, Store, Region, and Date. Dimension attributes are used for grouping, filtering, and slicing.

A common design is the star schema:

Date
                   |
Product ---- Sales Fact ---- Customer
                   |
                Store

In a star schema, the central fact table connects to surrounding dimensions through one-to-many relationships. This structure is usually easier to understand and performs more predictably than a highly normalized chain of tables.

2.2 Grain

The grain of a fact table is the level represented by one row. For example, one row may represent one product on one sales invoice, while another table may contain one row per invoice. The grain must be identified before measures are designed.

If Sales contains one row per invoice line, then a measure such as total revenue can sum line revenue safely. However, an invoice-level shipping charge may be repeated on every line. Summing it without adjustment would overstate shipping cost. Grain therefore affects both modelling and calculation validity.

2.3 Relationships

A relationship connects columns in two tables. In a standard star schema, the dimension key is on the “one” side and the fact foreign key is on the “many” side. For example, one Product row may relate to many Sales rows.

Important relationship properties include:

• Cardinality: one-to-one, one-to-many, or many-to-many;
• Cross-filter direction: the direction in which filters travel; and
• Active status: whether the relationship is used automatically in calculations.

Single-direction filtering from dimensions to facts is usually preferred because it reduces ambiguity. Bidirectional relationships can be useful in carefully controlled scenarios but may create unexpected filter paths and performance problems.

Keys should be unique on the one side. If a ProductID appears several times in the Product table, the relationship may be invalid or produce ambiguous results. Missing keys in the dimension table can also result in unmatched fact rows. In practice, a model should include a deliberate strategy for unknown or missing members, such as an “Unknown Product” row.

2.4 Measures, calculated columns, and calculated tables

A measure is a calculation evaluated at query time according to the current filter context. A simple measure might be:

Total Sales = SUM(Sales[SalesAmount])

A calculated column is evaluated row by row when the model is processed. It can be useful for categorization or row-level logic, but it increases model size and is not always appropriate for aggregations.

A calculated table creates a table using an expression. It should be used selectively because imported data or Power Query transformations are often more transparent for data preparation.

Measures are generally preferred for aggregations because they respond to slicers and visual context. For example, the following measures separate reusable business logic:

Total Cost = SUM(Sales[CostAmount])
Gross Profit = [Total Sales] - [Total Cost]
Gross Margin % = DIVIDE([Gross Profit], [Total Sales])

The DIVIDE function is preferable to direct division where the denominator may be zero or blank. The resulting percentage has no unit, although it should normally be formatted as a percentage.

A basic margin relationship is:

Here, Revenue and Cost must use the same currency and filter context. The formula is applicable when revenue is non-zero and when cost is defined consistently with revenue. If Revenue is zero, the percentage is undefined and should be displayed as blank or handled according to an agreed business rule.

2.5 Date tables and time analysis

Time-based analysis should normally use a dedicated Date table containing one row for each date in the required period. It should include attributes such as year, quarter, month number, month name, and financial period. Month names should be sorted by month number rather than alphabetically.

The Date table should be related to the relevant date column in the fact table and marked as a date table where appropriate. If a fact table contains several dates—such as Order Date, Ship Date, and Delivery Date—one relationship may be active while others remain inactive. Measures can explicitly use an inactive relationship when a different business event is required.

3. Loading and Transforming Data

Power Query provides a repeatable sequence of transformations called applied steps. Typical activities include removing unnecessary columns, changing data types, splitting columns, replacing values, merging tables, appending files, and filtering invalid records.

Transformations should be:

• reproducible rather than manually repeated;
• documented through meaningful step names;
• performed as close to the source as practical;
• conservative, so original meaning is not silently changed; and
• validated against source totals.

Data types are particularly important. Dates should be dates, currency should be numeric, and identifiers that contain leading zeroes should generally be text. A product code such as 00127 should not become the number 127 if the leading zero is meaningful.

A useful validation check is to compare row counts, minimum and maximum dates, distinct key counts, and control totals before and after transformation. A transformation that removes duplicates may be correct, but it must be justified by the intended grain.

4. Designing Report Pages

A report page should answer a recognizable analytical question. Examples include:

• How is revenue changing over time?
• Which products are driving margin?
• Which regions are underperforming against target?
• What explains a decline in customer retention?

4.1 Selecting visuals

Visual choice should follow the analytical task rather than personal preference.

Bar charts are often preferable to pie or donut charts when categories are numerous or values are similar. A visual should minimize nonessential decoration and make comparisons easy.

4.2 Layout and hierarchy

A page normally benefits from a clear visual hierarchy:

1. title and scope;
2. key performance indicators;
3. primary trend or comparison;
4. explanatory breakdowns; and
5. detailed supporting information.

Titles should state what is measured and, where necessary, the selected period or unit. A title such as “Sales by Region” is more informative than “Overview.” Dynamic titles can communicate active filter selections, but they should remain readable.

4.3 Formatting principles

Formatting is part of analytical accuracy. Apply consistent number formats, decimal places, currency symbols, and percentage formats. Avoid unnecessary three-dimensional effects, excessive colors, and inconsistent scales.

Use color deliberately. A sequential scale is appropriate for ordered magnitude, while a diverging scale can show values above and below a meaningful reference point. Red and green alone may be inaccessible to users with color-vision deficiencies, so labels, icons, or alternative colors may be required.

Axes should not be truncated when the truncation could exaggerate differences, especially in column charts. If a truncated axis is analytically justified, it should be used cautiously and clearly labelled.

5. Filters and Context

Filters determine which rows contribute to a visual. Power BI supports several levels:

• Visual-level filters affect one visual.
• Page-level filters affect all visuals on a page.
• Report-level filters affect pages across the report.
• Slicers provide visible user controls.
• Drill-through filters pass a selected value to a detail page.

A filter can be applied to all values, selected values, or conditions such as “Top N.” Top N must be interpreted carefully: it ranks within the current filter context and may change when the user selects another period or region.

Filter context is central to DAX. A measure evaluates differently depending on the categories, slicers, and filters active in the visual. For example, Total Sales shown by Region is evaluated separately for each region. This is why measures are dynamic and why a total shown in a visual may not equal a simple average of visible row values.

The filter pane is useful for authoring and inspecting filters. Slicers are better when users need to control selections directly. Too many slicers can make a page confusing; filters should support the report’s purpose rather than expose every available field.

6. Drill-Through Pages and Tooltips

6.1 Drill-through

A drill-through page provides context-specific detail. For example, a user may right-click a product in a summary chart and open a page showing that product’s sales trend, customers, transactions, and margin.

To create a drill-through page:

1. Create a new report page.
2. Add a field, such as Product or Customer, to the drill-through filter well.
3. Add detailed visuals to the page.
4. Include a Back button so users can return to the originating page.
5. Test the page from multiple source visuals.

The drill-through field must be present in the originating context for the expected behaviour. If a page is intended for product analysis, its measures and titles should respond clearly to the selected product.

6.2 Report-page tooltips

A report-page tooltip is a small custom page that appears when a user hovers over a visual data point. It can show supplementary information without occupying permanent space. Examples include prior-period value, margin, rank, or a small trend chart.

Tooltips should supplement, not duplicate, the main visual. They should be legible at small sizes and tested across categories with missing or extreme values. A tooltip that contains too many visuals may be slow or difficult to read.

7. Bookmarks and Navigation

Bookmarks save a report state, including filters, slicer selections, visibility, and sometimes the selected visual. They are useful for:

• switching between alternative views;
• creating show/hide panels;
• presenting guided narratives;
• building reset buttons; and
• supporting navigation between report states.

A bookmark must be configured intentionally. If data selections are saved when they should not be, a button may unexpectedly reset a user’s filters. Authors should decide whether a bookmark captures data, display, and current page, and then test the effect.

Buttons can use actions such as Page navigation, Bookmark, Drill-through, Back, and Web URL. Navigation should be consistent across pages, with visible indicators for the current section. A report should never leave users uncertain about how to return from a detailed view.

8. Interactivity and Visual Relationships

Power BI visuals can cross-filter or cross-highlight one another. Selecting a region in a map may filter a product chart, while selecting a product bar may highlight its contribution in a trend visual.

Use Edit interactions to define whether a visual filters, highlights, or has no effect on another visual. Default interactions are not always appropriate. For example, a KPI card may be intended to remain fixed while a user explores a detail chart.

Interaction design should be predictable. If a selection changes several visuals, users should be able to identify the cause. Excessive cross-filtering can make a report feel unstable, particularly when a user is unaware that a selection remains active.

9. Model and Report Validation

Validation should occur throughout development, not only at the end.

9.1 Data and model validation

Check the following:

• source row counts and transformed row counts;
• duplicate dimension keys;
• unmatched foreign keys;
• blank or invalid dates;
• relationship cardinality and direction;
• totals by a trusted period or business unit;
• measure results against independently calculated samples; and
• consistency of currencies, units, and time zones.

A simple reconciliation can compare Power BI revenue with an approved finance extract. If the difference is not zero, investigate filters, date definitions, cancelled transactions, tax treatment, rounding, and refresh timing rather than assuming the report is wrong or the source is wrong.

9.2 Measure validation

Measures should be tested at different granularities. Test the total, a single category, a period with no activity, and a case containing blanks or zero values. For percentages, test the denominator-zero case. For ratios, confirm that numerator and denominator use compatible filters.

A measure may be numerically valid but semantically wrong. For example, averaging daily revenue may not equal total revenue divided by the number of calendar days if some days have no transactions. The intended definition must be documented.

9.3 Interaction and usability testing

Test every slicer, bookmark, button, drill-through route, tooltip, and page navigation action. Confirm that selections do not persist unexpectedly. Test both mouse and keyboard navigation where applicable, and inspect the report at typical screen sizes.

A practical test record includes the test case, expected result, actual result, status, and defect description. Example:

10. Illustrative Case: Regional Sales Performance Dashboard

The following is a fictional illustrative case.

Background

A distributor operates across four regions and wants a management dashboard for monthly sales, gross margin, product performance, and customer detail. Existing spreadsheets contain sales transactions, products, customers, regional targets, and dates.

Problem

Managers receive inconsistent totals because spreadsheets use different date ranges and product classifications. They also lack a simple way to move from a regional summary to the customers and products causing a variance.

Analysis

The sales transaction table contains one row per invoice line. It includes InvoiceID, OrderDate, ProductID, CustomerID, RegionID, Quantity, SalesAmount, and CostAmount. Product, Customer, Region, Date, and Target tables are dimensions or supporting tables. The team confirms that SalesAmount and CostAmount are recorded in the same currency and that cancelled invoice lines must be excluded according to the approved business rule.

Approach and solution

The analyst creates a star schema with Sales at the centre and relationships to Product, Customer, Region, and Date. A separate Target table is related by Region and Month. Measures include:

Total Sales = SUM(Sales[SalesAmount])
Total Cost = SUM(Sales[CostAmount])
Gross Profit = [Total Sales] - [Total Cost]
Gross Margin % = DIVIDE([Gross Profit], [Total Sales])
Target Sales = SUM(Target[TargetAmount])
Variance to Target = [Total Sales] - [Target Sales]
Variance % = DIVIDE([Variance to Target], [Target Sales])

The overview page contains cards for Total Sales, Gross Profit, Gross Margin %, and Variance to Target. A line chart displays monthly sales, a bar chart compares regions, and a matrix shows product categories. A region slicer controls the page.

A drill-through page uses Region as its drill-through field and contains customer and product detail. A report-page tooltip displays monthly sales and margin when users hover over a region. Bookmarks switch between an executive view and an operational detail view. A reset bookmark restores the default period and all-region selection.

Validation and outcome

The team reconciles monthly sales to the approved finance extract. One discrepancy is found: the report includes cancelled invoice lines. After the transformation rule is corrected, totals reconcile. Testing also identifies that the Target table contains duplicate region-month rows, causing target values to be multiplied. The table is corrected to one row per region-month combination.

Managers can now identify underperforming regions, investigate products, and move from summary to detail without requesting separate spreadsheet analysis. The solution is effective because the visual design is supported by a validated model and explicit business definitions.

Learning points

• Establish grain before designing measures.
• Treat dimensions and targets as governed business data.
• Reconcile totals using an independent source.
• Test unusual cases, including duplicates and periods with no activity.
• Design drill-through and bookmarks around user decisions, not technical features.

11. Challenges, Governance, and Responsible Use

Power BI solutions may expose commercially sensitive, personal, or confidential information. Access should be based on legitimate business need. Row-level security may be required so that users see only the regions, customers, or transactions they are authorized to view.

Authors should minimize personal data, avoid displaying unnecessary identifiers, and follow organizational retention and privacy policies. A report should not imply precision that the source data cannot support. For example, a customer ranking based on incomplete transactions should be labelled or restricted.

Governance also includes naming conventions, workspace permissions, certified datasets, refresh ownership, lineage documentation, and change control. Measures such as “Revenue” and “Active Customer” should have agreed definitions. Otherwise, different reports may use the same label for different calculations.

Performance can be affected by excessive columns, high-cardinality fields, complex DAX, many visuals, bidirectional relationships, and inefficient transformations. Reduce unnecessary data, use a star schema, aggregate where appropriate, and avoid placing too many resource-intensive visuals on one page.

Artificial intelligence features may assist with natural-language queries, summaries, or anomaly detection, but their output must be reviewed. AI-generated explanations can misinterpret filters, causality, or incomplete data. Human oversight remains necessary for financial, personnel, compliance, and other consequential decisions.

12. Contemporary Development Practices

Modern Power BI development increasingly emphasizes reusable semantic models, centralized measures, deployment pipelines, dataflows, incremental refresh, and collaborative version control practices. Organizations may separate development, test, and production environments so that changes are validated before publication.

A mature workflow includes:

1. requirements and metric-definition agreement;
2. source profiling and transformation design;
3. semantic-model development;
4. report prototyping;
5. reconciliation and interaction testing;
6. user acceptance testing;
7. controlled publication; and
8. monitoring of refresh, usage, and defects.

The specific tools and governance arrangements vary, but the principle is stable: a BI report is an information product that requires design, testing, ownership, and maintenance.

Best-Practice Checklist

Before publishing a Power BI report, confirm that:

• the grain of every fact table is documented;
• relationships have correct keys, cardinality, and direction;
• measures have defined business meanings;
• date fields and time intelligence have been tested;
• totals reconcile to a trusted source;
• filters and slicers behave as intended;
• drill-through, tooltips, bookmarks, and buttons work;
• number formats, titles, legends, and units are clear;
• the report is accessible and not dependent on color alone;
• sensitive data is protected;
• performance is acceptable; and
• ownership, refresh frequency, and known limitations are documented.

Chapter Summary

Power BI Desktop supports the complete development of an interactive BI solution, from data preparation and semantic modelling to report design and validation. Reliable solutions begin with a clearly defined fact-table grain, well-structured dimensions, valid relationships, and reusable measures. Star schemas and controlled filter directions generally provide a strong foundation.

Report pages should be designed around analytical questions. Visual selection, formatting, titles, color, and layout affect interpretation as well as appearance. Filters, slicers, drill-through pages, tooltips, bookmarks, and visual interactions allow users to explore information, but these features must be configured and tested deliberately.

Validation includes reconciliation of data, testing of measures at multiple granularities, checking relationships, and verifying navigation and interaction. Governance, privacy, security, accessibility, performance, and human review are essential professional responsibilities. The quality of a Power BI solution is therefore determined by the combination of model accuracy, user-centred design, technical testing, and responsible deployment.

Key Terms

Semantic model: A structured analytical representation of data, relationships, calculations, and business meaning.

Fact table: A table recording measurable business events at a defined grain.

Dimension table: A table containing descriptive attributes used to filter and group facts.

Grain: The level of detail represented by one row in a table.

Relationship: A defined connection between columns in two model tables.

Cardinality: The relationship type, such as one-to-many or many-to-many.

Filter context: The set of filters affecting a calculation or visual.

Measure: A query-time calculation that responds to filter context.

Calculated column: A column computed row by row during model processing.

DAX: Data Analysis Expressions, the formula language used for Power BI calculations.

Power Query: The data connection and transformation component of Power BI.

Star schema: A model with a central fact table connected to descriptive dimension tables.

Slicer: A visible report control that allows users to filter data.

Drill-through: Navigation to a detail page using a selected field value as context.

Tooltip: Supplementary information displayed when a user hovers over a visual element.

Bookmark: A saved report state containing selected display or filter settings.

Cross-filtering: The effect of one visual’s selection on another visual.

Row-level security: Rules that restrict data according to the viewer’s identity or role.

Reconciliation: Comparison of report results with an independent trusted source.

Review Questions

5 Short Answer Questions

1. What is the difference between a fact table and a dimension table?
2. Why is the grain of a fact table important?
3. Distinguish a measure from a calculated column.
4. What is the purpose of a drill-through page?
5. Name three levels at which Power BI filters can be applied.

5 Long Answer Questions

1. Explain how a star schema supports reliable Power BI reporting.
2. Describe a systematic process for validating a semantic model.
3. Discuss how visual choice, formatting, and layout affect analytical interpretation.
4. Explain how bookmarks, buttons, and drill-through pages can support report navigation.
5. Discuss the governance, privacy, and security responsibilities associated with publishing Power BI reports.

3 Analytical / Application Questions

1. A sales model shows unexpectedly high target values when users select a region. Diagnose possible relationship and grain problems and propose tests.
2. Design a report page for a manager investigating declining regional margin. Specify the measures, visuals, filters, and drill-through features you would use.
3. A report reconciles at total-company level but not by month. Develop a structured investigation covering dates, transformations, cancellations, relationships, and measure definitions.

Multiple Choice Questions with Answers

Question 1

Which table normally contains measurable business events in a star schema?

A. Fact table

B. Dimension table

C. Parameter table

D. Navigation table

Correct Answer: A. Fact table

Question 2

Why is a dedicated Date table commonly used in Power BI?

A. To store visual formatting settings

B. To support consistent time analysis and date filtering

C. To replace all fact tables

D. To prevent users from using slicers

Correct Answer: B. To support consistent time analysis and date filtering

Question 3

Which Power BI object is evaluated according to the current filter context?

A. Static text box

B. Calculated column only

C. Measure

D. Image visual

Correct Answer: C. Measure

Question 4

What is the usual relationship direction in a simple star schema?

A. Fact table filters dimensions in every case

B. Dimensions filter the fact table

C. All tables filter all other tables bidirectionally

D. Only calculated tables can filter facts

Correct Answer: B. Dimensions filter the fact table

Question 5

Which feature is designed to open a detail page using a selected product or region?

A. Drill-through

B. Conditional formatting

C. Data label

D. Theme import

Correct Answer: A. Drill-through

Question 6

What is a primary purpose of a report-page tooltip?

A. To permanently replace the semantic model

B. To display supplementary information on hover

C. To delete unwanted rows

D. To define relationship cardinality

Correct Answer: B. To display supplementary information on hover

Question 7

Which test is most directly concerned with whether a report total agrees with an approved finance extract?

A. Reconciliation test

B. Color-contrast test

C. Navigation test

D. Bookmark-state test

Correct Answer: A. Reconciliation test

Question 8

Why can duplicated rows in a target table cause incorrect results?

A. They may multiply target values during aggregation

B. They automatically remove all slicers

C. They prevent every visual from loading

D. They convert dates into text

Correct Answer: A. They may multiply target values during aggregation

Question 9

Which visual is generally most suitable for showing a trend across months?

A. Line chart

B. Donut chart

C. Card

D. Treemap

Correct Answer: A. Line chart

Question 10

What is a key reason to avoid relying only on red and green in report design?

A. They cannot be used in Power BI

B. They may create accessibility problems for some users

C. They always slow down data refresh

D. They prevent drill-through actions

Correct Answer: B. They may create accessibility problems for some users