Microsoft Power BI Cookbook by Powell, Brett -- Read -- Imperial Library of Trantor

Index

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

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

← Prev
Back
Next →

← Prev
Back
Next →