Microsoft Power BI Complete Reference

Bring your data to life with the powerful features of Microsoft Power BI

Devin Knight, Brian Knight, Mitchell Pearson, Manuel Quintana, Brett Powell

• Publisher: Packt Publishing
• Year: 2018
• ISBN:
  9781789955637
• Edition: 1
• Topic: Commerce
• Subtopic: Business Intelligence

Design, develop, and master efficient Power BI solutions for impactful business insights

Key Features

• Get to grips with the fundamentals of Microsoft Power BI
• Combine data from multiple sources, create visuals, and publish reports across platforms
• Understand Power BI concepts with real-world use cases

Book Description

Microsoft Power BI Complete Reference Guide gets you started with business intelligence by showing you how to install the Power BI toolset, design effective data models, and build basic dashboards and visualizations that make your data come to life.

In this Learning Path, you will learn to create powerful interactive reports by visualizing your data and learn visualization styles, tips and tricks to bring your data to life. You will be able to administer your organization's Power BI environment to create and share dashboards. You will also be able to streamline deployment by implementing security and regular data refreshes.

Next, you will delve deeper into the nuances of Power BI and handling projects. You will get acquainted with planning a Power BI project, development, and distribution of content, and deployment. You will learn to connect and extract data from various sources to create robust datasets, reports, and dashboards. Additionally, you will learn how to format reports and apply custom visuals, animation and analytics to further refine your data.

By the end of this Learning Path, you will learn to implement the various Power BI tools such as on-premises gateway together along with staging and securely distributing content via apps.

This Learning Path includes content from the following Packt products:

• Microsoft Power BI Quick Start Guide by Devin Knight et al.
• Mastering Microsoft Power BI by Brett Powell

What you will learn

• Connect to data sources using both import and DirectQuery options
• Leverage built-in and custom visuals to design effective reports
• Administer a Power BI cloud tenant for your organization
• Deploy your Power BI Desktop files into the Power BI Report Server
• Build efficient data retrieval and transformation processes

Who this book is for

Microsoft Power BI Complete Reference Guide is for those who want to learn and use the Power BI features to extract maximum information and make intelligent decisions that boost their business. If you have a basic understanding of BI concepts and want to learn how to apply them using Microsoft Power BI, then Learning Path is for you. It consists of real-world examples on Power BI and goes deep into the technical issues, covers additional protocols, and much more.

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 10, Developing DAX Measures and Security Roles, as well as the dynamic date dimension columns described in Chapter 8, 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 distinguish flexibility in report layout and visualization from the report's target users and business questions. Second and third iterations of reports should not, for example, call for fundamentally different measures or new report pages to support different user groups. Report authors and BI teams can work with users and project sponsors to maintain the scope of ...