Mastering Microsoft Power BI

Brett Powell

• Publisher: Packt Publishing
• Year: 2018
• ISBN:
  9781788292283
• Topic: Computer Science
• Subtopic: Data Visualisation
• Index: Computer Science

Design, create and manage robust Power BI solutions to gain meaningful business insights

Key Features

• Master all the dashboarding and reporting features of Microsoft Power BI
• Combine data from multiple sources, create stunning visualizations and publish your reports across multiple platforms
• A comprehensive guide with real-world use cases and examples demonstrating how you can get the best out of Microsoft Power BI

Book Description

This book is intended for business intelligence professionals responsible for the design and development of Power BI content as well as managers, architects and administrators who oversee Power BI projects and deployments. The chapters flow from the planning of a Power BI project through the development and distribution of content to the administration of Power BI for an organization.BI developers will learn how to create sustainable and impactful Power BI datasets, reports, and dashboards. This includes connecting to data sources, shaping and enhancing source data, and developing an analytical data model. Additionally, top report and dashboard design practices are described using features such as Bookmarks and the Power KPI visual.BI managers will learn how Power BI's tools work together such as with the On-premises data gateway and how content can be staged and securely distributed via Apps. Additionally, both the Power BI Report Server and Power BI Premium are reviewed.By the end of this book, you will be confident in creating effective charts, tables, reports or dashboards for any kind of data using the tools and techniques in Microsoft Power BI.

What you will learn

• Build efficient data retrieval and transformation processes with the Power Query M Language
• Design scalable, user-friendly DirectQuery and Import Data Models
• Develop visually rich, immersive, and interactive reports and dashboards
• Maintain version control and stage deployments across development, test, and production environments
• Manage and monitor the Power BI Service and the On-premises data gateway
• Develop a fully on-premise solution with the Power BI Report Server
• Scale up a Power BI solution via Power BI Premium capacity and migration to Azure Analysis Services or SQL Server Analysis Services

Who this book is for

Business Intelligence professionals and existing Power BI users looking to master Power BI for all their data visualization and dashboarding needs will find this book to be useful. While understanding of the basic BI concepts is required, some exposure to Microsoft Power BI will be helpful.

]]>

Creating and Formatting Power BI Reports

In this chapter, we will create Power BI reports based on the dataset developed over the past three chapters and published to the Power BI service. We will review a report-planning and design process as well as all primary report formatting features in the context of visualization best practices. Additionally, we will look at report behavior and functionality features, such as alternative filter scopes, slicers, and conditional formatting options.

This chapter also highlights several of the latest and most powerful Power BI report features, including visual interactions, top N, relative date filters, and What-if parameters. The reports developed in this chapter can be further supplemented with custom visuals and advanced analytics from the following chapter to serve as a supporting analysis layer to Power BI dashboards.

In this chapter, we will review the following topics:

• Report planning
• Live connections to Power BI datasets
• Visualization best practices
• Choosing the visual
• Visual interactions
• Slicers
• What-if parameters
• Report filter scopes
• Relative date filtering
• Conditional formatting
• Mobile-optimized reports

Report planning

Power BI reports can take on a variety of forms and use cases, ranging from executive-level dashboard layouts to highly detailed and focused reports. Prior to designing and developing Power BI reports, some level of planning and documentation is recommended to ensure that the reports are well aligned with the needs of the users and the organization.

Effective report planning can be encapsulated in the following five steps:

1. Identify the users or consumers of this report:
   • Senior managers generally prefer less self-service interactivity and value simple, intuitive visuals, such as KPIs.
   • Analysts often require significant flexibility to filter and interact with more detailed reports. For example, reports used by analysts generally include more slicer visuals and may include table or matrix visuals as well.

Separating reports by user role or group serves to keep reports focused for users and more manageable for BI teams. In many scenarios, an organizational hierarchy provides a natural demarcation such that reports can be designed for specific roles or levels within an organization.

In the project example for the Adventure Works sales team, reports could align with the Sales Territory hierarchy (Sales Territory Group | Sales Territory Country | Sales Territory Region). The vice president of group sales will value high-value corporate-wide metrics and intuitive dashboard reports. A sales analyst in the United States, however, will likely need to break out individual regions and even analyze specific zip codes or individual products.

2. Define the business question(s) that the report should answer or support:
   • Confirm with the business user(s) or project sponsors that this is the appropriate focus and scope of the report:
     • A report architecture diagram described in the next section can support this communication.
     • For example, the user could be advised that a particular business question or metric will be included in a different report but will be featured on the same dashboard and will be easily accessible within the same Power BI app.
   • The most important business question (for example, What were our sales?) will be addressed in the top-left corner of the report canvas, likely with a KPI or card visual.

Similar to separating reports by user role or group, a report should not attempt to resolve widely disparate business questions. A sales report can, for example, provide high-level metrics on other business processes, such as customer service, inventory, or shipping. However, the supporting visuals of a report should almost always be derived from the same business processes and fact tables as the primary business question, such as Internet Sales and Reseller Sales.

3. Confirm that the dataset supports the business questions:
   • The report author should ensure that the dataset includes measures such as year-over-year (YOY) sales and the dimension columns (for example, Product Category) necessary to visualize the business questions.

It's very important that report authors have a solid understanding of the Power BI dataset. This knowledge includes the logic and business definitions of DAX measures, the relationships defined between fact and dimension tables, and any data transformation logic applied to the source data. In many projects, report authors will regularly collaborate with business stakeholders or project sponsors in gathering requirements and demonstrating report iterations. Therefore, the authors will need to explain the values and behaviors of Power BI reports as well as any current limitations in the dataset, such as the years of history supported and any DAX logic or measures not yet created:

• • If a gap exists between the dataset and the measures required for the report, the team can determine whether the dataset should be extended or whether the measure should be created local to the report
  • Only measures can be created within Power BI Live connection reports
  • Any new columns, tables, or modifications to existing tables or columns must be implemented within the source dataset

The set of base measures described in Chapter 4 (Developing DAX Measures and Security Roles), as well as the dynamic date dimension columns described in Chapter 2, Connecting to Sources and Transforming Data with M (for example, Calendar Month Status = 'Prior Calendar Month'), should support the most common needs of reports. If a measure required for a report is considered to be common to other future reports, and if the measure doesn't violate the single corporate definition of an existing measure, the measure should generally be added to the dataset. However, if the report requirement is considered rare or if a measure definition has been approved only for the specific report, then the measure(s) can be created local to the report. For version control and manageability reasons, report authors should not have to implement complex filtering logic or develop many local report measures. Report authors should communicate with dataset designers and the overall team if a significant gap exists or is developing between reports and the dataset.

4. Determine how the report will be accessed and the nature of any user interactivity:
   
   • Reports and dashboards can be optimized for mobile device consumption if this use case is expected
   • Power BI Desktop supports slicer visuals, a What-if parameter, and visual interaction options as standard features:
     • Reports can, therefore, be designed for static consumption or to support rich data exploration

5. Draw a sketch of the report layout:
   • At least for the primary page of the report, document how the area of the report canvas will be allocated

The following sample sketch is created within a PowerPoint presentation file via the standard shape objects:

Sample report layout sketch

• • Per the sample layout, the critical sales and margin measures are located in the top-left corner of the report page:
  • • Slicer (filter) visuals are planned for below these KPI or card visuals and other visuals will add further context
    • Greater space is allocated to the two visuals in the middle of the page given their importance to the report
  • The report layout sketch can be used exclusively for planning purposes or can be set as the background for a report page
    • For example, a PowerPoint slide of the same shapes, background shading, and borders can be saved to a network directory as a PNG file
    • In Power BI Desktop, the PNG file can be imported via the Add Image formatting option under Page Background or via the insert an image icon on the Home tab in Report view
    • Page background images with proper alignment, spacing, and colors can expedite quality report development

Be willing to modify a report layout or even start afresh with a new layout based on user feedback. Unlike dataset development, which can require significant time and expertise (for example, DAX, M, SQL), reports can be developed in a rapid, agile delivery methodology. Report authors can engage directly with users on these iterations and, although recommended practices and corporate standards can be communicated, ultimately the functional value to the user is the top priority. It's important to distingu...