Book Image

Microsoft Power BI Cookbook

By : Author Test, Brett Powell
Book Image

Microsoft Power BI Cookbook

By: Author Test, Brett Powell

Overview of this book

Microsoft Power BI is a business intelligence and analytics platform consisting of applications and services designed to provide coherent, visual and interactive insights of data. This book will provide thorough, technical examples of using all primary Power BI tools and features as well as demonstrate high impact end-to-end solutions that leverage and integrate these technologies and services. Get familiar with Power BI development tools and services, go deep into the data connectivity and transformation, modeling, visualization and analytical capabilities of Power BI, and see Power BI’s functional programming languages of DAX and M come alive to deliver powerful solutions to address common, challenging scenarios in business intelligence. This book will excite and empower you to get more out of Power BI via detailed recipes, advanced design and development tips, and guidance on enhancing existing Power BI projects.

Related Content you might be interested in

Current Title:

Small book image
Microsoft Power BI Cookbook
Author Test | Brett Powell
Sep, 2017 | 802 pages
Small book image
Mastering Microsoft Power BI
Brett Powell
Mar, 2018 | 638 pages
Small book image
Mastering Microsoft Power BI
Brett Powell | Greg Deckler
Jun, 2022 | 712 pages
Small book image
Microsoft Power BI Quick Start Guide
Bradley Schacht | Devin Knight | Mitchell Pearson | Erin Ostrowsky
Oct, 2020 | 296 pages
Table of Contents (14 chapters)
Preface
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Free Chapter
1
Configuring Power BI Development Tools
Configuring Power BI Development Tools
Introduction
Configuring Power BI Desktop options and settings
Installing the On-Premises Data Gateway
Installing Power BI Publisher for Excel
Installing and Configuring DAX Studio
2
Accessing and Retrieving Data
Accessing and Retrieving Data
Introduction
Viewing and analyzing M functions
Establishing and managing connections to data sources
Building source queries for DirectQuery models
Importing data to Power BI Desktop models
Applying multiple filtering conditions
Choosing columns and column names
Transforming and cleansing source data
Creating custom and conditional columns
Integrating multiple queries
Choosing column data types
Visualizing the M library
3
Building a Power BI Data Model
Building a Power BI Data Model
Introduction
Designing a multi fact data model
Implementing a multi fact data model
Handling one-to-many and many-to-many relationships
Assigning data formatting and categories
Configuring Default Summarization and sorting
Setting the visibility of columns and tables
Embedding business definitions into DAX measures
Enriching a model with analysis expressions
Building analytics into data models with DAX
Integrating math and statistical analysis via DAX
Supporting virtual table relationships
Creating browsable model hierarchies and groups
4
Authoring Power BI Reports
Authoring Power BI Reports
Introduction
Building rich and intuitive Power BI reports
Creating table and matrix visuals
Utilizing graphical visualization types
Enhancing exploration of reports
Integrating card visualizations
Controlling interactive filtering between visuals
Associating slicers with report pages
Applying filters at different scopes
Formatting reports for publication
Designing mobile report layouts
5
Creating Power BI Dashboards
Creating Power BI Dashboards
Introduction
Building a Power BI dashboard
Constructing an enterprise dashboard
Developing dynamic dashboard metrics
Preparing datasets and reports for Q & A natural language queries
Embedding analytical context into visualizations
Exposing what matters - top N and percentage of total visualizations
Visualizing performance relative to targets with KPIs and gauges
Leveraging Power BI reports in Power BI dashboards
Deploying content from Excel and SSRS to Power BI
Adding data alerts and email notifications to dashboards
6
Getting Serious with Date Intelligence
Getting Serious with Date Intelligence
Introduction
Building a complete date dimension table
Prepping the date dimension via the Query Editor
Authoring date intelligence metrics across granularities
Developing advanced date intelligence metrics
Simplifying date intelligence with DAX queries and calculated tables
Adding a metric placeholder dimension
7
Parameterizing Power BI Solutions
Parameterizing Power BI Solutions
Introduction
Creating dynamic and portable Power BI reports
Filtering queries with parameters
Preserving report metadata with Power BI templates
Converting static queries into dynamic functions
Parameterizing your data sources
Generating a list of parameter values via queries
Capturing user selections with parameter tables
Building a forecasting process with What if analysis capabilities
8
Implementing Dynamic User-Based Visibility in Power BI
Implementing Dynamic User-Based Visibility in Power BI
Introduction
Capturing the current user context of Power BI content
Defining RLS roles and filtering expressions
Designing dynamic security models in Power BI
Building dynamic security in DirectQuery data models
Displaying the current filter context in Power BI reports
Avoiding manual user clicks with user-based filtering logic
9
Applying Advanced Analytics and Custom Visuals
Applying Advanced Analytics and Custom Visuals
Introduction
Incorporating advanced analytics into Power BI reports
Enriching Power BI content with custom visuals and quick insights
Creating geospatial mapping visualizations with ArcGIS maps for Power BI
Configuring custom KPI and slicer visuals
Building animation and story telling capabilities
Embedding statistical analyses into your model
Creating and managing Power BI groupings and bins
Detecting and analyzing clusters
Forecasting and visualizing future results
Using R functions and scripts to create visuals within Power BI
10
Developing Solutions for System Monitoring and Administration
Developing Solutions for System Monitoring and Administration
Introduction
Creating a centralized IT monitoring solution with Power BI
Constructing a monitoring visualization and analysis layer
Importing and visualizing dynamic management view (DMV) data of SSAS and Power BI data models
Increasing SQL Server DBA productivity with Power BI
Providing documentation of Power BI and SSAS data models to BI and business teams
Analyzing performance monitor counters of the Microsoft on-premises data gateway and SSAS tabular databases
Analyzing Extended Events trace data with Power BI
Visualizing log file data from SQL Server Agent jobs and from Office 365 audit searches
11
Enhancing and Optimizing Existing Power BI Solutions
Enhancing and Optimizing Existing Power BI Solutions
Introduction
Enhancing the scalability and usability of a data model
Revising DAX measures to improve performance
Pushing query processing back to source systems
Strengthening data import and integration processes
Isolating and documenting DAX expressions
12
Deploying and Distributing Power BI Content
Deploying and Distributing Power BI Content
Introduction
Preparing a content creation and collaboration environment in Power BI
Managing migration of Power BI content between development, testing, and production environments
Sharing Power BI dashboards with colleagues
Configuring Power BI app workspaces
Configuring refresh schedules and DirectQuery connections with the on-premises data gateway
Creating and managing Power BI apps
Building email subscriptions into Power BI deployments
Publishing Power BI reports to the public internet
Enabling the mobile BI experience
13
Integrating Power BI with Other Applications
Integrating Power BI with Other Applications
Introduction
Integrating Excel and SSRS objects into Power BI Solutions
Migrating a Power Pivot for Excel Data Model to Power BI
Accessing and analyzing Power BI datasets from Excel
Building Power BI reports into PowerPoint presentations
Migrating a Power BI Data Model to SSAS Tabular
Accessing MS Azure hosted services such as Azure Analysis Services from Power BI
Using Power BI with Microsoft Flow and PowerApps

Generating a list of parameter values via queries

The parameter values available for selection, such as dates and product subcategories, can also be parameterized via M queries. This data-driven approach to parameters exposes the current or relevant values from data sources and avoids error-prone manual entry, and stale or outdated values.

This recipe includes two examples of query-driven parameter values. One example retrieves the week end dates from the prior two years and another selects the product subcategories of a product category.

How to do it...

Dynamic date parameter query

...
Previous Section
End of Section 8
Next Section