Microsoft Power BI Cookbook电子书

Title Page

Copyright

Microsoft Power BI Cookbook

Credits

Foreword

About the Author

About the Reviewers

www.PacktPub.com

Why subscribe?

Customer Feedback

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Downloading the color images of this book

Errata

Piracy

Questions

Configuring Power BI Development Tools

Introduction

Configuring Power BI Desktop options and settings

Getting ready

How to do it...

Installing and running Power BI Desktop

Configuring Power BI Desktop options

How it works...

There's more...

See also

Power BI's advantages over Excel

Power BI Security and Data Source Privacy

Installing the On-Premises Data Gateway

Getting ready

Hardware and network configuration

How to do it...

Installation of on-premises gateway

How it works...

Gateway recovery key

There's more...

See also...

Installing Power BI Publisher for Excel

How to do it...

Installation of Power BI Publisher for Excel

There's more...

Installing and Configuring DAX Studio

How to do it...

Installation of DAX Studio

Configuration of DAX Studio

How it works...

There's more...

Guy in a Cube video channel

Accessing and Retrieving Data

Introduction

Viewing and analyzing M functions

Getting ready

How to do it...

Formula Bar

Advanced Editor window

How it works...

Query folding

M query structure

Lazy evaluation

There's more...

Partial query folding

Limitations of query folding

See also...

M language references

Establishing and managing connections to data sources

Getting ready

How to do it...

Isolate data sources from individual queries

Query groups

Manage source credentials and privacy levels

How it works...

Data Source settings

Data source privacy settings

There's more...

See also

Building source queries for DirectQuery models

Getting ready

How to do it...

Applying M transformations with DirectQuery models

How it works...

There's more...

DirectQuery project candidates

DirectQuery performance

See also

Importing data to Power BI Desktop models

How to do it...

Denormalize a dimension

Provide automatic sorting

How it works...

There's more...

One GB dataset limit and Power BI Premium

See also

Applying multiple filtering conditions

Getting ready

How to do it...

Query filter example steps

How it works...

There's more...

Filtering via the Query Editor interface

See also

Choosing columns and column names

How to do it...

Identify expensive columns

Select columns

Rename columns

How it works...

Column memory usage

There's more...

Fact table column eliminations

Column orders

See also

Transforming and cleansing source data

Getting ready

How to do it...

Remove duplicates

Update a column through a join

There's more...

See also

Creating custom and conditional columns

How to do it...

Create a dynamic banding attribute

Create a formatted name column

Comparing the current and previous rows

How it works...

Conditional expression syntax

Case sensitivity

Conditional expression evaluation

Query folding of custom columns

There's more...

Add column from example

Conditional columns interface

DAX calculated columns

Error handling and comments

Integrating multiple queries

Getting ready

How to do it...

Consolidate files

Self-joining querying

How it works...

Nested join versus flat join

Append multiple files

There's more...

Combine binaries

Staging queries versus inline queries

See also

Choosing column data types

How to do it...

Remove automatic type detection steps

Align relationship column data types

Add numeric columns from text columns

Use fixed decimal number for precision

How it works...

Automatic data type detection

Numeric data types

Power BI Desktop automatic time intelligence

There's more...

Data type impacts

Date with locale

Percentage data type

See also

Visualizing the M library

How to do it...

How it works...

There's more...

Building a Power BI Data Model

Introduction

Designing a multi fact data model

Getting ready

Setting business expectations

How to do it...

Four-step dimensional design process

Data warehouse and implementation bus matrix

Choose the dataset storage mode - Import or DirectQuery

In-Memory mode

DirectQuery mode

How it works...

DAX formula and storage engine

There's more...

Project ingestion questions

Power BI delivery approaches

See also

Implementing a multi fact data model

How to do it...

SQL view layer

M queries in Power BI Desktop

Create model relationships

Author DAX measures

Configure model metadata

There's more...

Shared views

Handling one-to-many and many-to-many relationships

Getting ready

How to do it...

Single, bidirectional, and CROSSFILTER()

Single direction relationships

Bidirectional relationship

CROSSFILTER() Measure

Many-to-many relationships

Bidirectional cross-filtering for many-to-many

How it works...

Ambiguous relationships

CROSSFILTER()

There's more...

DirectQuery supported

See also

Assigning data formatting and categories

How to do it...

Data formats

Data category

How it works...

There's more...

Model level settings

See also

Configuring Default Summarization and sorting

How to do it...

Sort By Column

DAX Year-Month sorting

DAX Ranking Sort

Default Summarization

How it works...

Default Summarization

There's more...

Quick measures

See also

Setting the visibility of columns and tables

How to do it...

Isolate measures from tables

How it works...

Measure home tables

There's more...

Hiding hierarchy columns

Group visibility

Row level security visibility

Visibility features from SSAS

Embedding business definitions into DAX measures

Getting ready

How to do it...

Sales and cost metrics

Margin and count metrics

Secondary relationships

How it works...

Date relationships

There's more...

Measure definitions

Measure names and additional measures

See also

Enriching a model with analysis expressions

How to do it...

Pricing analysis

Geometric mean at all grains

How it works...

Pricing analysis

Building analytics into data models with DAX

How to do it...

Cross-selling opportunities

Accessories but not bike customers

Bike only customers

Active verus inactive customers

Actual versus budget model and measures

How it works...

Filter Context Functions

There's more...

SUMMARIZECOLUMNS()

Integrating math and statistical analysis via DAX

How to do it...

Correlation coefficient

Goodness-of-Fit test statistic

How it works...

Correlation coefficient syntax

Goodness-of-Fit logic and syntax

Supporting virtual table relationships

How to do it...

Segmentation example

Summary to detail example

Actual versus plan

How it works...

Year and month selected

Virtual relationship functions

There's more...

Multiple dimensions

Alternatives to virtual relationships

See also

Creating browsable model hierarchies and groups

How to do it...

Create hierarchy columns with DAX

Implement a hierarchy

Create and manage a group

How it works...

DAX parent and child functions

Include other grouping option

Model scoped features

There's more...

DAX calculated columns as rare exceptions

Natural hierarchies versus unnatural hierarchies

Grouping dates and numbers

DirectQuery models supported

See also

Authoring Power BI Reports

Introduction

Building rich and intuitive Power BI reports

Getting ready

Stakeholder Matrix

How to do it...

Report planning and design process

Report Design Example

European Sales and Margin Report Page

European country sales and margin report page

How it works...

European sales report design

There's more...

Power BI report design checklist

Custom visuals

Published Power BI datasets as data sources

See also

Creating table and matrix visuals

How to do it...

Table visual exceptions

Identifying blanks in tables

Matrix visual hierarchies

How it works...

Matrix visual navigation

There's more...

URL and mail to email support

Percent of total formatting

Measures on matrix rows

Data bar conditional formatting

Utilizing graphical visualization types

Getting ready

Choosing visual types

How to do it...

Waterfall chart for variance analysis

Line chart with conditional formatting

Shape map visualization

How it works...

Shape map

Enhancing exploration of reports

Getting ready

Drillthrough report page requirements

Enable Cortana integration and Q&A

How to do it...

Create featured Q&A questions

Parameterized Q&A report

Cortana integration

Drillthrough Report Pages

Report themes

How it works...

Report theme JSON files

There's more...

Conversational BI - mobile support for Q&A

See also

Integrating card visualizations

Getting ready

How to do it...

KPI visual

Multi-row card

There's more...

Gauge visualizations

Controlling interactive filtering between visuals

How to do it...

Visual interaction control

How it works...

Current year Measures

Associating slicers with report pages

How to do it...

Configure dimension slicers

Horizontal slicers

Customize a date slicer

Relative date filters

How it works...

Date slicer

There's more...

Text search

Numeric range slicers

Applying filters at different scopes

How to do it...

Report and page level filters

Visual level filter - top N

How it works...

DAX queries from report, page, and visual Filters

There's more...

Advanced report and page level filters

Formatting reports for publication

How to do it...

Visual alignment and distribution

Shapes as backgrounds and groups

There's more...

Snap objects to grid and keyboard shortcuts

Textbox with email link

Format painter

See also

Designing mobile report layouts

Getting ready

Plan for mobile consumption

How to do it...

Phone layout - Europe report page

Phone layout - United Kingdom report page

How it works...

There's more...

Slicers and drill-down on mobile devices

Mobile-optimized dashboards

See also

Creating Power BI Dashboards

Introduction

Building a Power BI dashboard

How to do it...

Dashboard design process

Dashboard development process

Constructing an enterprise dashboard

How to do it...

Dashboard design process

How it works...

Dual KPI custom visual

Supporting tiles

Developing dynamic dashboard metrics

How to do it...

Dynamic date columns

KPI target measures

How it works...

Target measure - trailing 6 months

Preparing datasets and reports for Q & A natural language queries

Getting ready

Determine use cases and feasibility

How to do it...

Prepare a model for Q & A

Model metadata

Model design

Apply synonyms

Analyze Q & a use cases

Apply synonyms

Publish the dataset

Embedding analytical context into visualizations

How to do it...

Design the visual

Create the visual

How it works...

Color saturation rule

Tooltip measures

There's more...

Exposing what matters - top N and percentage of total visualizations

How to do it...

Top 25 resellers with below -3% margin

Last year's top 50 products with below -10% growth

How it works...

Prior year rank measure

Visualizing performance relative to targets with KPIs and gauges

How to do it...

Create the visuals

Grouping KPIs

Publish KPIs to dashboard

How it works...

Current month filter

Time intelligence measures

Leveraging Power BI reports in Power BI dashboards

How to do it...

Define live page requirements

Create and publish to the dashboard

Refine dashboard layout

How it works...

Live page slicers

Deploying content from Excel and SSRS to Power BI

Getting ready

How to do it...

Publish and pin excel objects

Pin SSRS report items

Adding data alerts and email notifications to dashboards

How to do it...

Configure data alert

Automate email notification

How it works...

Getting Serious with Date Intelligence

Introduction

Building a complete date dimension table

Getting ready

How to do it...

Date dimension design

Required date dimension columns

Date dimension planning and design

Add date intelligence columns via SQL

How it works...

Date intelligence columns

Loading the date dimension

There's more...

Role playing date dimensions

Surrogate key date conversion

Prepping the date dimension via the Query Editor

How to do it...

Date dimension M Query

Add the date intelligence column via join

How it works...

Date dimension M query

DirectQuery support

Authoring date intelligence metrics across granularities

Getting ready

How to do it...

Current time period measures

Prior time period measures

Dynamic prior period measure

How it works...

Current and prior time period measures

Developing advanced date intelligence metrics

How to do it...

Count of days without sales

Dynamic Prior Year-to-Date

How it works...

Dynamic prior period intelligence

Simplifying date intelligence with DAX queries and calculated tables

How to do it...

Role playing date dimensions via calculated tables

Date table logic query

How it works...

Date table logic query

Adding a metric placeholder dimension

How to do it...

Metric placeholder dimension query

Measure group table

Parameterizing Power BI Solutions

Introduction

Creating dynamic and portable Power BI reports

Getting ready

How to do it...

Single and multiple URL parameters

Dynamic embedded URLs

There's more...

Dashboards with custom URLs

See also

Filtering queries with parameters

Getting ready

How to do it...

Trailing days query parameter filter

Multi-parameter query filters

How it works...

Query folding of parameter value filters

There's more...

Power BI Service support

Preserving report metadata with Power BI templates

Getting ready

How to do it...

Template parameters

Export template

Converting static queries into dynamic functions

How to do it...

There's more...

Local resource usage

Parameterizing your data sources

Getting ready

How to do it...

SQL Server database

Excel filename and path

Stored procedure input parameters

Generating a list of parameter values via queries

How to do it...

Dynamic date parameter query

Product subcategories parameter query

There's more...

DirectQuery support

Capturing user selections with parameter tables

How to do it...

Sales plan growth scenarios

There's more...

Scenario specific measures

Building a forecasting process with What if analysis capabilities

Getting ready

How to do it...

Forecast variables from Excel

Power BI Desktop forecast model

Source connection and unpivoted forecast tables

Apply the forecast to historical values

Allocate the forecast according to the dimension variable inputs

Create relationships, measures, and forecast visuals

Test and deploy forecasting tool

How it works...

Implementing Dynamic User-Based Visibility in Power BI

Introduction

Capturing the current user context of Power BI content

Getting ready

How to do it...

How it works...

Power BI authentication

There's more...

USERNAME() versus USERPRINCIPALNAME()

See also

Defining RLS roles and filtering expressions

Getting ready

How to do it...

United States online Bike Sales Role

Europe reseller sales - mountain and touring

Deploy security roles to Power BI

How it works...

Filter transfer via relationships

There's more...

Managing security

Dynamic columns and central permissions table

Designing dynamic security models in Power BI

Getting ready

How to do it...

There's more...

Performance impact

Building dynamic security in DirectQuery data models

Getting ready

How to do it...

How it works...

Dynamic security via relationship filter propagation

There's more...

Bidirectional security relationships

Displaying the current filter context in Power BI reports

How to do it...

Dimension values selected

Dimension values remaining

How it works...

FILTERS() and CONCATENATEX()

Avoiding manual user clicks with user-based filtering logic

Getting ready

How to do it...

How it works...

There's more...

Personal filters feature coming to Power BI apps

Applying Advanced Analytics and Custom Visuals

Introduction

Incorporating advanced analytics into Power BI reports

How to do it...

Clustered column chart

Line chart

How it works...

Analytics pane measures

There's more...

Analytics pane limitations

See also

Enriching Power BI content with custom visuals and quick insights

Getting ready

How to do it...

Bullet chart custom visual

Scoped quick insights

How it works...

There's more...

Quick insights in Power BI Desktop

Quick insights on published datasets

Creating geospatial mapping visualizations with ArcGIS maps for Power BI

Getting ready

How to do it...

Single field address

Customer clustering Map

There's more...

ArcGIS map field wells

Conditional formatting logic

See also

Configuring custom KPI and slicer visuals

Getting ready

How to do it...

Dual KPI - headcount and labor expense

Chiclet Slicer - Sales Territory Country

There's more...

Chiclet slicer custom visual

Building animation and story telling capabilities

Getting ready

How to do it...

Scatter chart with play axis

ArcGIS map timeline

Pulse chart custom visual

There's more...

Bookmarks

Play axis custom visual

Storytelling custom visuals

Embedding statistical analyses into your model

Getting ready

How to do it...

Regression table and measures

Residuals table and measures

Regression report

How it works...

Statistical formulas

DAX calculated tables

See also

Creating and managing Power BI groupings and bins

How to do it...

First purchase date grouping

Days since last purchase grouping

Detecting and analyzing clusters

Getting ready

How to do it...

Create clusters

Analyze the clusters

How it works...

RFM - recency, frequency, monetary

Clustering algorithm and limits

There's more...

R clustering custom visuals

Scatter chart-based clustering

Forecasting and visualizing future results

Getting ready

How to do it...

Monthly forecast via date hierarchy

Weekly sales forecast analysis

How it works...

Exponential smoothing

Dynamic week status column

There's more...

Forecast requirements

Using R functions and scripts to create visuals within Power BI

Getting ready

How to do it...

Base graphics histogram

ggplot2 histogram

How it works...

Automatic duplicate removal

Filter context

There's more...

See also

Developing Solutions for System Monitoring and Administration

Introduction

Creating a centralized IT monitoring solution with Power BI

Getting ready

How to do it...

How it works...

Wait Stats and instance configuration data source setup

There's more...

Query Store integration

DirectQuery real-time monitoring datasets

See also

Constructing a monitoring visualization and analysis layer

Getting ready

How to do it...

How it works...

Relative date filtering

There's more...

Top 10 slowest queries via Query Store

See also

Importing and visualizing dynamic management view (DMV) data of SSAS and Power BI data models

How to do it...

How it works...

Memory structures

See also

Increasing SQL Server DBA productivity with Power BI

Getting ready

How to do it...

How it works...

Query Store

See also

Providing documentation of Power BI and SSAS data models to BI and business teams

Getting ready

How to do it...

How it works...

There's more...

Power BI documentation reports via Excel

SQL Server Analysis Services (SSAS) Metadata

Analyzing performance monitor counters of the Microsoft on-premises data gateway and SSAS tabular databases

Getting ready

How to do it...

SSAS tabular memory reporting

On-premises data gateway counters

How it works...

SSAS tabular memory limits

On-premises data gateway workloads

There's more...

High availability and load balancing for the on-premises data gateway

Reduce network latency via Azure ExpressRoute and Azure Analysis Services

See also

Analyzing Extended Events trace data with Power BI

Getting ready

How to do it...

How it works...

Self-service Extended Events analysis

There's more...

SQL Server Profiler versus Extended Events

Additional event session integration

See also

Visualizing log file data from SQL Server Agent jobs and from Office 365 audit searches

Getting ready

How to do it...

Power BI Audit Log Integration

SQL Server Agent log integration

How it works...

PowerShell search for Power BI audit log

SQL Server agent tables

There's more...

Power BI usage reporting

See also

Enhancing and Optimizing Existing Power BI Solutions

Introduction

Enhancing the scalability and usability of a data model

Getting ready

How to do it...

Identify expensive columns and quick wins

Normalize large dimensions

Sort imported fact tables

How it works...

Columnar database

Run-length encoding (RLE) compression via Order By

Segment elimination

There's more...

Minimize loaded and refreshed queries

Revising DAX measures to improve performance

Getting ready

How to do it...

Improper use of FILTER()

Optimizing OR condition measures

How it works...

DAX query engine - formula and storage

There's more...

DAX variables for performance

DAX as a query language

Pushing query processing back to source systems

Getting ready

How to do it...

Query folding analysis process

Query folding redesign

How it works...

Query folding factors

Native SQL queries

There's more...

Parallel loading of tables

Improving folded queries

Strengthening data import and integration processes

How to do it...

Data source consolidation

Error handling, comments, and variable names

Handling missing fields

How it works...

MissingField.UseNull

See also

Isolating and documenting DAX expressions

Getting ready

How to do it...

Reseller Margin % with variables

Variable table filters

How it works...

Reseller Margin % with variables

There's more...

DAX Formatter in DAX Studio

Deploying and Distributing Power BI Content

Introduction

Preparing a content creation and collaboration environment in Power BI

How to do it...

Evaluate and plan for Power BI deployment

Set up a Power BI service deployment

How it works...

Premium capacity nodes - frontend cores and backend cores

There's more...

Scaling up and scaling out with Power BI Premium

See also

Managing migration of Power BI content between development, testing, and production environments

Getting ready

How to do it...

Staged deployment overview

Development environment

Production environment

How it works...

Automated report lifecycle - clone and rebind report APIs

OneDrive for business synchronization

Version restore in OneDrive for business

See also

Sharing Power BI dashboards with colleagues

Getting ready

How to do it...

How it works...

Managing shared dashboards

There's more...

Analyze shared content from Excel

Sharing dashboards from Power BI mobile apps

Configuring Power BI app workspaces

Getting ready

How to do it...

How it works...

App workspaces and apps

App workspaces replace group workspaces

There's more...

Power BI premium capacity admins

See also

Configuring refresh schedules and DirectQuery connections with the on-premises data gateway

Getting ready

How to do it...

Scheduled refresh for import mode dataset

Configure data sources for the on-premises data gateway

Schedule a refresh

DirectQuery dataset

Configure data sources for the on-premises data gateway

Configure the DirectQuery dataset

How it works...

Dataset refreshes

Dashboard and report cache refreshes

There's more...

Refresh limits: Power BI premium versus shared capacity

Trigger refreshes via data refresh APIs in the Power BI Service

See also

Creating and managing Power BI apps

Getting ready

How to do it...

Publishing an app

Distributing and installing the app

How it works...

App workspaces to apps

There's more...

Apps replacing content packs

Building email subscriptions into Power BI deployments

Getting ready

Determine feasibility - recipient, distribution method, and content

How to do it...

Create dashboard and report subscriptions

Manage subscriptions

There's more...

See also

Publishing Power BI reports to the public internet

Getting ready

How to do it...

How it works...

Publish to web report cache

There's more...

Embed in SharePoint online

See also

Enabling the mobile BI experience

How to do it...

Enhance basic mobile exploration and collaboration

Enable advanced mobile BI experiences

How it works...

Responsive visualizations

There's more...

Apple watch synchronization

SSRS 2016 on-premises via Power BI mobile apps

Filters on phone reports

See also

Integrating Power BI with Other Applications

Introduction

Integrating Excel and SSRS objects into Power BI Solutions

Getting ready

How to do it...

SSRS

Excel

There's more...

SSRS and Excel use cases

SSRS

Microsoft Excel

Migrating a Power Pivot for Excel Data Model to Power BI

Getting ready

How to do it...

How it works...

Excel items imported

There's more...

Export or upload to Power BI from Excel 2016

Upload Excel Workbook to Power BI

Export Excel Workbook to Power BI

Accessing and analyzing Power BI datasets from Excel

Getting ready

How to do it...

Cube formulas

DAX query to Power BI

How it works...

Cube Formulas

DAX query data connection

There's more...

Sharing and distribution limitations

New Excel visual types table requirement

Building Power BI reports into PowerPoint presentations

Getting ready

How to do it...

Prepare a report for PowerPoint

Export report to PowerPoint

How it works...

High resolution images and textboxes

There's more...

Embed Power BI tiles in MS Office

See also

Migrating a Power BI Data Model to SSAS Tabular

Getting ready

How to do it...

How it works...

Azure analysis services pricing and performance

There's more...

Direct import to SQL server data tools

See also

Accessing MS Azure hosted services such as Azure Analysis Services from Power BI

Getting ready

How to do it...

How it works...

Report level measures for live connections to SSAS

Client libraries for Azure Analysis Services

There's more...

Power BI premium DirectQuery and SSAS live connection query limits

See also

Using Power BI with Microsoft Flow and PowerApps

Getting ready

How to do it...

Streaming Power BI dataset via MS Flow

How it works...

Microsoft Flow

There's more...

Write capabilities and MS Flow premium

PowerApps Studio and mobile applications

See also