Microsoft Power BI Cookbook: Gain expertise in Power BI with over 90 hands-on recipes, tips, and use cases, 2nd Edition

655 Pages·2021·31.88 MB·English

by  Greg Deckler, Brett Powell

Microsoft Power BI
Cookbook
Second Edition
Gain expertise in Power BI with over 90 hands-on recipes,
tips, and use cases
Greg Deckler
Brett Powell
BIRMINGHAM—MUMBAI
Microsoft Power BI Cookbook
Second Edition
Copyright © 2021 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or
transmitted in any form or by any means, without the prior written permission of the publisher,
except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the
information presented. However, the information contained in this book is sold without
warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers
and distributors, will be held liable for any damages caused or alleged to have been caused
directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies
and products mentioned in this book by the appropriate use of capitals. However, Packt
Publishing cannot guarantee the accuracy of this information.
Producer: Suman Sen
Acquisition Editor – Peer Reviews: Saby Dsilva
Project Editor: Parvathy Nair
Content Development Editor: Alex Patterson
Copy Editor: Safis Editor
Technical Editor: Aditya Sawant
Proofreader: Safis Editor
Indexer: Manju Arasan
Presentation Designer: Ganesh Bhadwalkar
First published: September 2017
Second edition: September 2021
Production reference: 1280921
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-80181-304-4
www.packt.com
Contributors
About the authors
Greg Deckler is a Vice President at Fusion Alliance and has been a professional of
consulting services for over 27 years. Recognized as an expert in Power BI, Greg is a six-time
Microsoft MVP for the Data Platform and an active member of the Power BI Community site
with over 5,000 solutions authored and hundreds of Quick Measure Gallery submissions.
Greg founded the Columbus Azure ML and Power BI User Group in Columbus, OH in 2016 and
continues to hold regularly monthly meetings.
I would like to thank the dynamic and vibrant Power BI community as a
whole and especially all of the Super Users and my User Group members.
Finally, I'd like to thank my son, Rocket, for keeping me supplied with
excellent graphics and videos!
Brett Powell owns Frontline Analytics LLC, a data and analytics consultancy specializing in
the Microsoft data platform. Brett has over ten years of experience in business intelligence
and data warehouse projects as both a developer and technical architect. In addition to
project engagements, Brett shares technical tips and examples via his popular Insights Quest
blog and has authored two commercially successful books – Mastering Microsoft Power BI
and Microsoft Power BI Cookbook.
I'd first like to acknowledge and praise Jesus Christ, my Lord and Savior,
for blessing me with these opportunities and for loving me and guiding me
each day. I'd also like to thank my mother, Cathy, and brother Dustin, for
their endless support and encouragement. Finally, I'd like to thank Packt for
their efforts to produce quality, valuable content.
About the reviewer
David R. Eldersveld is a Technical Specialist at Microsoft currently focused on Power
BI. Prior to joining Microsoft, he was a Data Platform MVP and spent nearly ten years as a
data and analytics consultant with CapstoneBI and BlueGranite. David has contributed to
technical communities as a blogger, speaker, and contributor to Workout Wednesday's Power
BI challenges.
Table of Contents
Preface xi
Chapter 1: Configuring Power BI Tools 1
Technical Requirements 1
Installing and Configuring Power BI Desktop 2
Creating an On-Premises Data Gateway 12
Installing Analyze in Excel from the Power BI Service 17
Installing and Configuring Additional Tools 22
Conclusion 28
Chapter 2: Accessing and Retrieving Data 29
Technical Requirements 30
Viewing and Analyzing M Functions 30
Managing Queries and Data Sources 39
Using DirectQuery 45
Importing Data 50
Applying Multiple Filters 58
Selecting and Renaming Columns 66
Transforming and Cleansing Data 70
Creating Custom Columns 74
Combining and Merging Queries 81
Selecting Column Data Types 87
Visualizing the M library 92
Profiling Source Data 95
Diagnosing Queries 98
Conclusion 104
v
Table of Contents
Chapter 3: Building a Power BI Data Model 105
Technical Requirements 106
Designing a Data Model 106
Implementing a Data Model 112
Creating Relationships 118
Configuring Model Metadata 126
Hiding Columns and Tables 134
Enriching a Model with DAX 140
Supporting Virtual Table Relationships 153
Creating Hierarchies and Groups 160
Designing Column Level Security 167
Leveraging Aggregation Tables 172
Conclusion 176
Chapter 4: Authoring Power BI Reports 177
Technical Requirements 178
Building Rich and Intuitive Reports 178
Filtering at Different Scopes 187
Integrating Card visualizations 191
Using Slicers 196
Controlling Visual Interactions 202
Utilizing Graphical Visualizations 206
Creating Table and Matrix visuals 212
Enhancing Reports 218
Formatting Reports for Publication 223
Designing Mobile Layouts 230
Creating Paginated Reports 234
Conclusion 243
Chapter 5: Working in the Service 245
Technical Requirements 246
Building a Dashboard 246
Preparing for Q&A 254
Adding Alerts and Subscriptions 260
Deploying Content from Excel and SSRS 266
Streaming Datasets 270
Authoring Dataflows 274
Conclusion 280
Chapter 6: Getting Serious with Date Intelligence 281
Technical Requirements 282
Building a Date Dimension Table 282
vi
Table of Contents
Preparing the Date Dimension via the Query Editor 287
Authoring Date Intelligence Measures 293
Developing Advanced Date Intelligence Measures 298
Simplifying Date Intelligence with DAX and Calculated Tables 303
Leveraging Calculation Groups 306
Conclusion 311
Chapter 7: Parameterizing Power BI Solutions 313
Technical requirements 314
Filtering reports dynamically 314
Leveraging query parameters 317
Working with templates 322
Converting static queries to dynamic functions 326
Capturing user selections with parameter tables 331
Forecasting with what-if analysis 338
Conclusion 348
Chapter 8: Implementing Dynamic User-Based
Visibility in Power BI 349
Technical Requirements 350
Capturing Current User Context 350
Defining RLS Roles and Filter Expressions 353
Designing Dynamic Security Models 361
Building Dynamic Security for DirectQuery 365
Displaying the Current Filter Context 369
Avoiding Manual User Clicks 374
Conclusion 380
Chapter 9: Applying Advanced Analytics and Custom Visuals 381
Technical Requirements 382
Incorporating Advanced Analytics 382
Enriching Content with Custom Visuals and Quick Insights 386
Creating Geospatial Mapping with ArcGIS Maps 390
Democratizing Artificial Intelligence 396
Building Animation and Storytelling 401
Embedding Statistical Analyses 408
Grouping and Binning 415
Detecting and Analyzing Clusters 419
Forecasting and Anomaly Detection 424
Using R and Python Visuals 430
Conclusion 437
vii
Table of Contents
Chapter 10: Administering and Monitoring Power BI 439
Technical requirements 440
Creating a centralized IT monitoring solution with Power BI 440
Constructing a monitoring, visualization, and analysis layer 446
Importing and visualizing dynamic management view (DMV) data 450
Increasing DBA productivity 455
Providing documentation 461
Analyzing SSAS tabular databases and gateways 467
Analyzing Extended Events 473
Visualizing log file data 478
Leveraging the Power BI PowerShell Module 484
Conclusion 489
Chapter 11: Enhancing and Optimizing
Existing Power BI Solutions 491
Technical Requirements 492
Enhancing Data Model Scalability and Usability 492
Improving DAX Measure Performance 499
Pushing Query Processing Back to Source Systems 505
Strengthening Data Import and Integration Processes 510
Isolating and Documenting DAX Expressions 514
Improving Data Load Speeds with Incremental Refresh 518
Conclusion 522
Chapter 12: Deploying and Distributing Power BI Content 523
Technical Requirements 524
Preparing for Content Creation and Collaboration 524
Managing Content between Environments 532
Sharing Content with Colleagues 535
Configuring Workspaces 539
Configuring On-Premises Gateway Data Connections 545
Publishing Apps 551
Publishing Reports to the Public Internet 556
Enabling the Mobile Experience 561
Distributing Content with Teams 568
Conclusion 572
Chapter 13: Integrating Power BI with Other Applications 573
Technical Requirements 574
Integrating SSRS and Excel 574
Migrating from Power Pivot for Excel Data to Power BI 582
Accessing and Analyzing Power BI Datasets in Excel 586
Building Power BI Reports into PowerPoint Presentations 592
viii
Table of Contents
Connecting to Azure Analysis Services 596
Integrating with Power Automate and Power Apps 600
Leveraging Dataverse and Dynamics 365 610
Connecting Dynamics 365 Finance and Operations and the Power
Platform 615
Conclusion 621
Why subscribe? 629
Other Book You May Enjoy 625
Index 629
ix