Authoring Power BI Reports

Power BI reports serve as the basic building blocks for dashboards, data exploration, and content collaboration and distribution in Power BI. Power BI Desktop provides abundant data visualization features and options, enabling the construction of highly targeted, user-friendly reports across devices. As each Power BI Desktop report can contain multiple pages with each page including multiple visuals, a single Power BI report can support multiple use cases, audiences, and business needs. For example, a KPI visual can be pinned to a dashboard in the Power BI Service, while a report page can support detailed, domain-specific analysis. These capabilities compete directly with visualization offerings from competitor analytics platforms and can be further extended with custom visuals and report themes.

The selection and configuration of Power BI visualization features in report design are essential to derive value from the data retrieval and modeling...

Technical Requirements

The following are required to complete the recipes in this chapter:

• Power BI Desktop and a Power BI service account.
• Tabular Editor.
• SQL Server with the AdventureWorksDW2019 database installed. This database and instructions for installing are available here: http://bit.ly/2OVQfG7.
• CH4_Start.pbix contains the model used for this chapter and can be downloaded from the following GitHub repository: https://github.com/PacktPublishing/Microsoft-Power-BI-Cookbook-Second-Edition.

Building Rich and Intuitive Reports

Power BI Desktop provides the means to design reports that are both highly customized to specific use cases and requirements and aligned with a corporate BI standard. The design and development process for a report should naturally flow from the data modeling process as the measures, relationships, and dimensions from the model are utilized to visualize and analyze business questions. As the purpose and scope of Power BI reports can range widely, from dashboard visualizations to interactive analytical experiences to role-specific detail reporting, it is essential that report authoring features are aligned closely to these distinct use cases.

In this recipe, a report design planning process is shared to bring clarity to the primary design elements of Power BI reports, such as visualization types. Two finished report pages are described with supporting details included in the How it works section, and additional report design features and practices...

Filtering at Different Scopes

Filters can be configured against Power BI reports at each level of the report architecture, including the entire report, the report page, and the individual visual. As report filters are configured at design time and not exposed on the canvas like slicers, filters provide a powerful, efficient method of customizing elements of reports to specific needs. Report- and page-level filters that align with the user or team accessing the report, or with specific entities to be analyzed, deliver immediate focus and a certain degree of organization. For example, a report page built for one product category can be duplicated for other product category pages, with each page containing a different page-level filter. Visual-level filters deliver greater flexibility, as complex filtering conditions, including measures, can be defined in addition to any report- and page-level filters.

In this recipe, examples are provided of implementing filters at the three different...

Integrating Card visualizations

Card and Multi-row Card visualizations are often positioned at the top and left sections of report pages, given the importance of individual measures and small sets of measures. Although less graphically powerful and interactive than other visuals, cards are also the most common tiles pinned to Power BI dashboards and are also used frequently in phone layouts for mobile consumption. A common practice in report design is to start with a few high-level measures represented as card or KPI visuals and build additional chart and Table visuals around these.

This recipe demonstrates how to use card, Multi-row card, KPI, and Gauge visualizations—as well as text boxes.

Getting ready

To prepare for this recipe, follow these steps:

1. Open the CH4_R2.pbix file and save it as CH4_R3.pbix.
2. Switch to the Scratch page, and use Ctrl-A then Delete to remove any visuals.
3. Check the Filters pane and ensure that only the Europe filter...

Using Slicers

Slicer visuals are the primary means for users to apply filter selections to other visuals of a report page, and thus their implementation greatly affects usability and analytical flexibility. Although user interaction with other visuals also applies cross-filtering to other visuals, slicers provide the fastest and most intuitive method to define specific filtering criteria, such as three specific months and two product categories. Slicer visuals also have unique formatting options for defining the selection behavior, including a Select All option.

An alternative to slicers is the use of the Filter pane. While perhaps not as obvious to end users and requiring specific training, the use of the Filter pane as opposed to slicers does provide the benefit of preserving space on the Canvas. However, while the Filter pane can provide some of the basic usage scenarios common to slicers, slicers provide much greater flexibility in terms of interactions with different visuals...

Controlling Visual Interactions

Power BI report pages are interactive by default, with all visuals, excluding slicers, cross-filtered via the selections applied to one visual. While this dynamic filter context is often helpful in exploring and analyzing across dimensions, there is often also a need to exclude certain visuals from this behavior. For example, a high-priority measure reflected in a card or KPI visual may be configured to ignore any filter selections from slicers and other visuals on the page. Additionally, rather than the default highlighting of cross-filtered visuals, it can be beneficial to exclusively display the related values in other visuals.

In this recipe, we provide examples of configuring interactivity between visuals.

Getting ready

To prepare for this recipe, follow these steps:

1. Open the CH4_R4.pbix file and save the file as CH4_R5.pbix.
2. Switch to the Scratch page and use Ctrl-A and then Delete to remove any visuals.
3. Check...

Utilizing Graphical Visualizations

Data visualization and exploration is central to Power BI, and the visualization types chosen in reports contribute greatly to user comprehension and adoption. Power BI Desktop includes an array of modern visuals, such as the Treemap and the Funnel, but also includes a set of rich formatting options for traditional line, bar/column, combination, and scatter charts. Additionally, five map visuals are available to analyze geographical data, and an entire library of custom visuals is available via the AppSource store, which is integrated into the Home ribbon and Visualizations pane of Power BI Desktop.

A common mistake of inexperienced report developers is the overuse of fancy or complex graphical visuals. Report developers should choose visuals based on their alignment to the business questions within the scope of the report and should always prioritize simplicity and ease-of-use.

This recipe provides examples of utilizing various graphical...

Creating Table and Matrix visuals

Table and Matrix visuals are appropriate when visibility of precise, individual values are needed, or when data is viewed at a detailed level, such as in individual transactions. Table visuals in Power BI conform to the classic "list" report format of columns and rows but support powerful cross-highlighting and formatting options, including conditional formatting. Matrix visuals include Table visual features and correspond to the layout and general functionality of pivot tables in Excel: two-dimensional representations of measures with the ability to drill up and down the row and column axes.

In this recipe, the various capabilities of Table and Matrix visualizations are demonstrated, including conditional formatting, drill-down, and cross-highlighting.

Getting ready

To prepare for this recipe, follow these steps:

1. Open the CH4_R6.pbix file and save the file as CH4_R7.pbix.
2. Switch to the Scratch page and use Ctrl...

Enhancing Reports

There are many report features that can add greater context and enhance the self-service data exploration experience. Three such features are Tooltips, Tooltip pages, and Drill through report pages.

In this recipe, we walk through the using Tooltips, Tooltip pages, and Drill through report pages to enhance our report.

Getting ready

To prepare for this recipe, follow these steps:

1. Open the CH4_R7.pbix file and save the file as CH4_R8.pbix.
2. Switch to the Scratch page and use Ctrl-A then Delete to remove any visuals.
3. Check the Filters pane and ensure that only the Europe filter is active.

How to Enhance Reports

To implement this recipe, use the following steps:

1. Select the Germany page.
2. Select the Shape map visualization.
3. In the Fields well, add the Internet Net Sales, Internet Margin %, and Internet Sales Per Customer measures from the Internet Sales table to the Tooltips field.
4. Hover over...

Formatting Reports for Publication

Power BI Desktop includes features to control and enhance the formatting and layout of reports at a detailed level. Prior to publishing reports to the Power BI service, visuals can be aligned, sized, and evenly spaced to deliver an organized, symmetrical layout. Additionally, supplemental report elements, such as shapes, text boxes, and images, can be added to further organize and enrich report pages with textual and visual aids.

This recipe demonstrates how to control the positioning, alignment, and distribution of report visuals, as well as tips for presenting and working with visuals in groups. Additionally, helpful tips are presented for final clean-up items prior to publishing a report, as well as setting up Q&A after publishing.

Getting ready

To prepare for this recipe, follow these steps:

1. Open the CH4_R8.pbix file and save the file as CH4_R9.pbix.
2. Switch to the Scratch page and use Ctrl-A, then Delete to remove...

Designing Mobile Layouts

Power BI reports can be optimized for consumption on mobile devices via the Mobile layout view in Power BI Desktop. This layout allows users accessing reports through the Power BI mobile applications to more easily view and interact with the most important content of these reports on iOS, Android, or Windows mobile devices. Given the importance of the mobile experience and the unique design considerations for reports with multiple pages, optimizing Power BI reports for mobile access is essential.

In this recipe, the Europe and United Kingdom report pages of the example report provided in the first recipe of this chapter are configured with the Mobile layout. Additional details for optimizing Power BI dashboards are included in the There's more... section.

Getting ready

To prepare for this recipe, follow these steps:

1. Open the CH4_R9.pbix file and save the file as CH4_R10.pbix.
2. Switch to the Scratch page and use Ctrl-A and then...

Creating Paginated Reports

Microsoft has made great strides to ensure that paginated reports (SSRS-style reports) are first-class citizens in the Power BI universe. Power BI Premium supports such reports, as does the Power BI Report Server (PBIRS). The most recent evidence that Microsoft still embraces paginated reports for Power BI is the announcement of a planned report-building experience within Power BI Desktop for the creation of paginated reports and the Paginated report visual released in May 2021.

At this time, however, paginated reports must still be created in a separate tool, Power BI Report Builder. This recipe demonstrates how to download and install Power BI Report Builder and use this tool to build a paginated report.

Getting ready

To prepare for this recipe, follow these steps:

1. Download Power BI Report Builder from this location: https://bit.ly/2XOp1G1.
2. Open the PowerBIReportBuilder.msi file.
3. Complete the installation using the wizard...

Conclusion

Power BI reports serve as the foundation for dashboards, data exploration, and content collaboration and distribution in Power BI. Power BI Desktop provides data visualization features and options in abundance, enabling the construction of highly targeted and user-friendly reports across devices. In this chapter, we developed and described the most fundamental report visualizations and design concepts. Additionally, guidance was provided to enhance and control the user experience when interacting with Power BI reports and consuming them on Windows and mobile devices.