Aspin Pro Power BI Desktop

2. ed
ISBN: 978-1-4842-3210-1
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 783 Seiten

Deliver eye-catching Business Intelligence with Microsoft Power BI Desktop. This new edition has been updated to cover all the latest features, including combo charts, Cartesian charts, trend lines, use of gauges, and more. Also covered are Top-N features, the ability to bin data into groupings and chart the groupings, and new techniques for detecting and handling outlier data points. You can take data from virtually any source and use it to produce stunning dashboards and compelling reports that will seize your audience's attention. Slice and dice the data with remarkable ease and then add metrics and KPIs to project the insights that create your competitive advantage. Make raw data into clear, accurate, and interactive information with Microsoft's free self-service business intelligence tool.Pro Power BI Desktop shows you how to choose from a wide range of built-in and third-party visualization types so that your message is always enhanced. You'll be able to deliver those results on the PC, tablets, and smartphones, as well as share results via the cloud. This book helps you save time by preparing the underlying data correctly without needing an IT department to prepare it for you. What You'll LearnDeliver attention-grabbing information, turning data into insight
Mash up data from multiple sources into a cleansed and coherent data modelCreate dashboards that help in monitoring key performance indicators of your businessBuild interdependent charts, maps, and tables to deliver visually stunning informationShare business intelligence in the cloud without involving ITDeliver visually stunning and interactive charts, maps, and tablesFind new insights as you chop and tweak your data as never before
Adapt delivery to mobile devices such as phones and tablets
Who This Book Is For
Everyone from CEOs and Business Intelligence developers to power users and IT managers

Adam Aspin is an independent business intelligence consultant based in the United Kingdom. He has worked with SQL Server for more than 20 years. During this time, he has developed several dozen reporting and analytical systems based on the Microsoft BI product suite.A graduate of Oxford University, Adam began his career in publishing before moving into IT. Databases soon became a passion, and his experience in this arena ranges from Access to Oracle and MySQL, with occasional sorties into the world of DB2. He is, however, most at home in the Microsoft universe using SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Integration Services, SharePoint, and Power BI.Business Intelligence has been Adam's principal focus for the last 15 years. He has applied his skills for a range of clients in industry sectors such as finance, utilities, telecommunications, insurance, manufacturing, and banking.Adam is a frequent contributor to SQLServerCentral.com and Simple-Talk. He has written numerous articles for various French IT publications. A fluent French speaker, Adam has worked in France and Switzerland for many years. He speaks regularly at events such as SQLBits, SQL Saturdays, and local SQL Server user groups.Adam is the author of SQL Server Data Integration Recipes (Apress), High Impact Data Visualization with Power View, Power Map, and Power BI (Apress), and Business Intelligence with SQL Server Reporting Services (Apress).

Autoren/Hrsg.

Aspin, Adam

Inhaltsverzeichnis

1;Contents;5
2;About the Author;28
3;About the Technical Reviewer;29
4;Acknowledgments;30
5;Introduction;31
6;Chapter 1: Introduction to Power BI Desktop;33
6.1;The Microsoft Self-Service Business Intelligence Solution;34
6.1.1;Importing Data from Diverse Sources;34
6.1.2;Modeling Your Data;35
6.1.3;Creating Reports and Dashboards;35
6.1.4;Power BI Desktop Files;35
6.2;The Power BI Universe;36
6.3;Installing Power BI Desktop;36
6.3.1;Removing Power BI Desktop;41
6.3.2;Running Power BI Desktop;41
6.4;A First Power BI Desktop Dashboard;42
6.4.1;Loading the Source Data;43
6.5;The Data Load Process;46
6.5.1;The Navigator Window;46
6.5.2;The Navigator Data Preview;46
6.5.3;Modifying Data;47
6.6;The Power BI Desktop Window;47
6.7;Your First Visualizations;49
6.7.1;Displaying Available Fields;49
6.7.2;Adding a Matrix of Sales per Country by Year;50
6.7.3;Adding a Column Chart of Delivery Charge by Model;52
6.7.4;Adding a Map of Labor Cost by Country;54
6.7.5;Adding a Card Showing the Total Cost of Spare Parts;55
6.7.6;Adding a Slicer by Make;56
6.7.7;Arranging the Dashboard;57
6.7.7.1;Moving a Visualization;57
6.7.7.2;Resizing a Visualization;57
6.8;Interactivity in Dashboards;58
6.9;Formatting Reports;60
6.9.1;The Format Pane;61
6.9.2;Borders;61
6.9.3;Background Color;62
6.9.4;Titles;63
6.9.5;Table Gridlines;64
6.9.6;Data Colors;65
6.10;Creating and Modifying Reports;67
6.10.1;Adding Pages;68
6.10.2;Renaming Pages;68
6.10.3;Deleting Pages;68
6.10.4;Moving Pages;69
6.10.5;Duplicating Pages;69
6.10.6;Scrolling Through Collections of Pages;69
6.11;Conclusion;70
7;Chapter 2: Discovering and Loading File-Based Data with Power BI Desktop;71
7.1;The Power BI Desktop Query Editor;72
7.2;Data Sources;72
7.2.1;File Sources;73
7.3;Loading Data;74
7.3.1;CSV Files;74
7.3.1.1;What Is a CSV File?;77
7.3.2;Text Files;78
7.3.3;Text and CSV Options;80
7.3.3.1;File Origin;80
7.3.3.2;Delimiter;80
7.3.3.3;Data Type Detection;80
7.3.4;XML Files;81
7.3.5;Excel Files;82
7.3.6;Importing Excel and Power View Items;83
7.3.7;Microsoft Access Databases;86
7.3.8;JSON Files;87
7.4;Loading Multiple Files from a Directory;88
7.5;Loading the Contents of a Folder;91
7.6;The Navigator Dialog;91
7.6.1;Searching for Datasets;92
7.6.2;Display Options;93
7.6.2.1;Only Selected Items;93
7.6.2.2;Enable Data Previews;93
7.6.3;Refresh;93
7.6.3.1;Source Data Refresh;93
7.6.3.2;Data Preview Refresh;94
7.6.3.3;Select Related Tables;94
7.7;Adding Your Own Data;94
7.8;Conclusion;95
8;Chapter 3: Loading Data from Databases and Data Warehouses;96
8.1;Relational Databases;96
8.2;SQL Server;98
8.2.1;Automatically Loading Related Tables;102
8.2.2;Database Options;102
8.2.2.1;Server Connection;103
8.2.2.2;Searching for Databases, Tables, and Views in Navigator;104
8.2.2.3;Searching for Databases;104
8.2.3;Searching for Tables;105
8.2.3.1;Database Security;106
8.2.3.2;Using a SQL Statement;107
8.2.3.3;Stored Procedures in SQL Server;109
8.3;Oracle Databases;112
8.4;Other Relational Databases;116
8.5;Microsoft SQL Server Analysis Services Data Sources;117
8.5.1;Analysis Services Cube Tools;120
8.5.1.1;Add Items;120
8.5.1.2;Collapse Columns;122
8.6;SSAS Tabular Data Warehouses;122
8.7;Import or Connect Live;124
8.8;ODBC Sources;124
8.9;OLE DB Data Sources;131
8.10;Modifying Connections;135
8.11;Changing Permissions;137
8.12;Refreshing Data from Databases and Data Warehouses;139
8.12.1;Refreshing the Entire Data in the Power BI Desktop In-Memory Model;139
8.12.2;Refreshing an Individual Table;140
8.13;Conclusion;140
9;Chapter 4: DirectQuery and Connect Live;141
9.1;DirectQuery and Connect Live;141
9.2;Microsoft SQL Server Data;142
9.3;SQL Server Analysis Services Dimensional Data;146
9.4;Microsoft SQL Server Analysis Services Tabular Data Sources;149
9.5;DirectQuery with Non-Microsoft Databases;151
9.6;DirectQuery and In-Memory Tables;152
9.7;DirectQuery and Refreshing the Data;153
9.8;Conclusion;153
10;Chapter 5: Loading Data from the Web and the Cloud;154
10.1;Web and Cloud Services;154
10.1.1;Web Pages;155
10.1.2;Online Services;155
10.1.3;Microsoft Azure;155
10.2;Web Pages;156
10.2.1;Advanced Web Options;159
10.2.2;Table View or Web View;160
10.3;Salesforce;161
10.3.1;Loading Data from Salesforce Objects;162
10.3.2;Salesforce Reports;167
10.4;Microsoft Dynamics 365;168
10.5;Google Analytics;171
10.6;OData Feeds;175
10.6.1;OData Options;176
10.7;Azure SQL Database;177
10.8;Azure SQL Data Warehouse;180
10.9;Connecting to SQL Server on an Azure Virtual Machine;182
10.10;Azure Blob Storage;185
10.11;Azure Security;187
10.12;Conclusion;187
11;Chapter 6: Dealing with Datasets;188
11.1;Power BI Desktop Queries;189
11.1.1;Editing Data After a Data Load;189
11.1.2;Transforming Data Before Loading;191
11.2;Query or Load?;192
11.3;The Power BI Desktop Query Editor;193
11.3.1;The Applied Steps List;194
11.3.2;The Power BI Desktop Query Editor Ribbons;194
11.3.2.1;The Home Ribbon;194
11.3.2.2;The Transform Ribbon;196
11.3.2.3;The Add Column Ribbon;198
11.3.2.4;The View Ribbon;200
11.4;Dataset Shaping;200
11.4.1;Renaming Columns;200
11.4.2;Reordering Columns;201
11.4.3;Removing Columns;202
11.4.4;Choosing Columns;202
11.4.5;Merging Columns;204
11.4.6;Going to a Specific Column;206
11.5;Removing Records;207
11.5.1;Keeping Rows;208
11.5.1.1;Removing Rows;209
11.5.1.2;Removing Blank Rows;210
11.5.2;Removing Duplicate Records;210
11.6;Sorting Data;211
11.6.1;Reversing the Row Order;212
11.6.2;Undoing a Sort Operation;212
11.7;Filtering Data;213
11.7.1;Selecting Specific Values;213
11.7.2;Finding Elements in the Filter List;214
11.7.3;Filtering Text Ranges;215
11.7.4;Filtering Numeric Ranges;215
11.7.5;Filtering Date and Time Ranges;216
11.7.6;Filtering Data;218
11.7.7;Applying Advanced Filters;219
11.8;Grouping Records;220
11.8.1;Simple Groups;220
11.8.2;Complex Groups;222
11.9;Saving Changes in the Query Editor;224
11.10;Exiting the Query Editor;224
11.11;Conclusion;225
12;Chapter 7: Data Transformation;226
12.1;Viewing a Full Record;227
12.2;Power BI Desktop Query Editor Context Menus;228
12.3;Using the First Row As Headers;229
12.4;Changing Data Type;229
12.4.1;Detecting Data Types;231
12.4.2;Data Type Indicators;232
12.4.3;Switching Data Types;233
12.4.4;Data Type Using Locale;233
12.5;Replacing Values;234
12.6;Transforming Column Contents;236
12.6.1;Text Transformation;236
12.6.2;Adding a Prefix or a Suffix;237
12.6.3;Removing Leading and Trailing Spaces;238
12.6.4;Removing Non-Printing Characters;238
12.6.5;Number Transformations;239
12.6.5.1;Calculating Numbers;241
12.6.5.2;Date Transformations;242
12.6.5.3;Time Transformations;244
12.6.5.4;Duration;245
12.7;Filling Down Empty Cells;246
12.8;Extracting Part of a Column’s Contents;248
12.8.1;Advanced Extract Options;249
12.8.1.1;Text Before and After Delimiter;249
12.8.1.2;Text Between Delimiters;250
12.9;Duplicating Columns;250
12.10;Splitting Columns;251
12.10.1;Splitting Column by a Delimiter;251
12.10.2;Splitting Columns by Number of Characters;253
12.11;Merging Columns;254
12.12;Custom Columns;255
12.13;Creating Columns from Examples;257
12.14;Adding Conditional Columns;259
12.15;Index Columns;260
12.16;Conclusion;261
13;Chapter 8: Data Mashup;262
13.1;The Power BI Desktop Query Editor View Ribbon;263
13.2;Merging Data;264
13.2.1;Adding Data;264
13.2.2;Aggregating Data During a Merge Operation;267
13.2.3;Types of Join;269
13.2.3.1;Use the Original Column Name As the Prefix;270
13.2.3.2;Search Columns to Expand;270
13.2.4;Joining on Multiple Columns;271
13.2.5;Preparing Datasets for Joins;272
13.2.6;Correct and Incorrect Joins;273
13.2.6.1;The Columns Do Not Map;273
13.2.6.2;The Columns Map, but the Result Is a Massive Table with Duplicate Records;273
13.2.7;Examining Joined Data;273
13.2.8;The Expand and Aggregate Buttons;275
13.3;Appending Data;275
13.3.1;Adding the Contents of One Query to Another;276
13.3.2;Adding Multiple Files from a Source Folder;276
13.3.3;Removing Header Rows After Multiple File Loads;279
13.4;Changing the Data Structure;280
13.4.1;Unpivoting Tables;280
13.4.1.1;Unpivot Options;281
13.4.2;Pivoting Tables;282
13.4.3;Transposing Rows and Columns;283
13.5;Parsing JSON Files;283
13.6;The List Tools Transform Ribbon;285
13.7;Convert a Column to a List;286
13.8;Parsing XML Data from a Column;287
13.9;Parsing JSON Data from a Column;289
13.10;Conclusion;291
14;Chapter 9: Structuring, Managing, and Parameterizing Queries;292
14.1;Managing the Transformation Process;292
14.1.1;Modifying a Step;293
14.1.2;Renaming a Step;294
14.1.3;Deleting a Step or a Series of Steps;294
14.1.4;Modifying an Existing Step;295
14.1.5;Adding a Step;297
14.1.6;Altering Process Step Sequencing;297
14.1.7;An Approach to Sequencing;297
14.1.8;Error Records;298
14.1.9;Removing Errors;298
14.2;Modifying the Code for a Step;298
14.2.1;Modifying a Single Step;299
14.2.2;Modifying a Query in “M”;299
14.3;Modifying Data Source Settings in the Query Editor;300
14.4;Managing Queries;301
14.4.1;Organizing Queries;302
14.4.2;Grouping Queries;302
14.4.2.1;Creating a New Group;302
14.4.2.2;Renaming Groups;303
14.4.2.3;Adding a Query to a Group;303
14.4.3;Duplicating Queries;304
14.4.4;Referencing Queries;304
14.4.5;Documenting Queries;305
14.4.6;Adding a Column As a New Query;305
14.4.7;Enabling Data Load;306
14.4.8;Enabling Report Refresh;307
14.5;Pending Changes;307
14.6;Reusing Data Sources;308
14.6.1;Pinning a Data Source;310
14.7;Parameterizing Queries;310
14.7.1;Creating a Simple Parameter;311
14.7.2;Creating a Set of Parameter Values;312
14.7.3;Creating a Query-Based Parameter;313
14.7.4;Modifying a Parameter;315
14.7.4.1;Modifying the Structure of a Parameter;315
14.7.5;Applying a Parameter when Filtering Records;316
14.7.6;Modifying the Current Value of a Parameter;317
14.7.7;Applying a Parameter in a Search and Replace;318
14.7.8;Applying a Parameter to a Datasource;319
14.7.9;Other Uses for Parameters;321
14.7.10;Applying a Parameter to a SQL Query;321
14.7.11;Query Icons;322
14.8;Power BI Templates;322
14.9;Copying Data from Power BI Desktop Query Editor;323
14.10;Conclusion;323
15;Chapter 10: Creating a Data Model;324
15.1;Data Modeling in the Power BI Desktop Environment;325
15.1.1;The Power BI Desktop Data View;325
15.2;Data Model or Query?;326
15.3;The Power BI Desktop Data View Ribbons;326
15.3.1;The Modeling Ribbon;326
15.4;Managing Power BI Desktop Data;328
15.4.1;Manipulating Tables;328
15.4.1.1;Renaming Tables;328
15.4.1.2;Deleting a Table;329
15.4.1.3;Selecting a Column from the List of Available Column Names;329
15.4.2;Manipulating Columns;330
15.4.2.1;Renaming a Column;330
15.4.2.2;Deleting Columns;331
15.4.2.3;Moving Columns;332
15.4.2.4;Setting Column Widths;332
15.5;Power BI Desktop Data Types;333
15.6;Formatting Power BI Desktop Data;333
15.6.1;Currency Formats;335
15.7;Preparing Data for Dashboards;336
15.7.1;Categorizing Data;336
15.7.2;Applying a Default Summarization;338
15.7.3;Defining Sort By Columns;339
15.7.4;Hiding Tables and Fields from the User;340
15.7.4.1;Hiding Columns and Tables;340
15.7.4.2;Removing Hidden Columns and Tables from View;340
15.8;Sorting Data in Power BI Desktop Tables;341
15.9;Adding Hierarchies;341
15.10;Creating and Modifying Groups;342
15.10.1;Creating a Group;343
15.10.2;Modifying a Group;344
15.11;Deleting a Group;345
15.12;Designing a Power BI Desktop Data Model;345
15.12.1;Data View and Relationship View;346
15.12.2;Relationship View Display Options;347
15.12.2.1;Maximizing a Table;347
15.12.2.2;Minimizing a Table;348
15.13;Creating Relationships;348
15.13.1;Creating Relationships Manually;349
15.13.2;Creating Relationships Automatically;351
15.13.3;Deleting Relationships;352
15.13.4;Managing Relationships;353
15.13.5;Deactivating Relationships;353
15.14;Advanced Relationship Options;354
15.14.1;Cardinality;354
15.14.1.1;Cardinality Issues;355
15.15;Managing Relationships Between Tables;355
15.15.1;Cross Filter Direction;356
15.15.2;Other Relationship Options;357
15.15.2.1;Make this Relationship Active;357
15.15.2.2;Assume Referential Integrity;357
15.15.3;Reimporting Related Tables;357
15.16;Conclusion;358
16;Chapter 11: Extending the Data Model with Calculated Columns;359
16.1;Types of Calculations;360
16.2;Adding New Columns;360
16.2.1;Naming Columns;361
16.3;Concatenating Column Contents;362
16.4;Tweaking Text;364
16.5;Simple Calculations;366
16.5.1;Math Operators;368
16.5.2;Rounding Values;368
16.6;Calculating Across Tables;370
16.6.1;Choosing the Correct Table for Linked Calculations;372
16.7;Cascading Column Calculations;372
16.7.1;Refreshing Data;373
16.8;Using Functions in New Columns;373
16.8.1;Safe Division;373
16.8.2;Counting Reference Elements;375
16.8.3;Statistical Functions;376
16.8.4;Applying a Specific Format to a Calculation;377
16.9;Simple Logic: The IF(?) Function;380
16.9.1;Exception Indicators;380
16.9.2;Creating Alerts;381
16.9.2.1;Comparison Operators;382
16.9.3;Flagging Data;382
16.9.4;Nested IF() Functions;383
16.9.5;Creating Custom Groups Using Multiple Nested IF() Statements;385
16.9.6;Multiline Formulas;386
16.9.7;Complex Logic;386
16.9.7.1;DAX Logical and Information Functions;388
16.9.7.2;Logical Operators;389
16.9.8;Formatting Logical Results;390
16.10;Making Good Use of the Formula Bar;390
16.11;Conclusion;391
17;Chapter 12: Adding Measures to the Data Model;392
17.1;A First Measure: Number of Cars Sold;392
17.2;Basic Aggregations in Measures;394
17.3;Using Multiple Measures;395
17.4;Cross-Table Measures;398
17.5;More Advanced Aggregations;400
17.6;Filter Context;403
17.6.1;Row Context;403
17.6.2;Query Context;403
17.6.3;Filter Context;404
17.7;Filtering Data in Measures;404
17.8;Simple Filters;404
17.8.1;Text Filters;404
17.8.2;Numeric Filters;406
17.9;More Complex Filters;407
17.9.1;Multiple Criteria in Filters;408
17.9.2;Using Multiple Filters;409
17.10;Calculating Percentages of Totals;410
17.10.1;A Simple Percentage;410
17.10.2;Removing Multiple Filter Elements;412
17.10.3;Visual Totals;413
17.10.4;The ALLEXCEPT() Function;414
17.11;Filtering on Measures;415
17.12;Displaying Rank;417
17.13;A Few Comments and Notes on Using Measures;418
17.14;Calculation Options;418
17.15;Conclusion;418
18;Chapter 13: Analyzing Data over Time;420
18.1;Simple Date Calculations;420
18.1.1;Date and Time Formatting;423
18.1.2;Calculating the Age of Cars Sold;425
18.2;Adding Time Intelligence to a Data Model;426
18.3;Creating and Applying a Date Table;427
18.3.1;Creating the Date Table;427
18.3.2;Adding Sort By Columns to the Date Table;430
18.3.3;Date Table Techniques;431
18.3.4;Adding the Date Table to the Data Model;432
18.4;Calculating the Difference Between Two Dates;433
18.5;Applying Time Intelligence;436
18.5.1;YearToDate, QuarterToDate, and MonthToDate Calculations;436
18.5.2;Analyzing Data As a Ratio over Time;438
18.5.3;Comparing a Metric with the Result from a Range of Dates;441
18.6;Comparisons with Previous Time Periods;443
18.6.1;Calculating Sales for the Previous Year;443
18.7;Comparison with a Parallel Period in Time;445
18.7.1;Comparing Data from Previous Years;445
18.7.2;Comparing with the Same Date Period from a Different Quarter, Month, or Year;448
18.8;Rolling Aggregations over a Period of Time;450
18.9;Conclusion;451
19;Chapter 14: Table Visuals;452
19.1;Power BI Desktop Dashboards;453
19.1.1;Switching to Report View;453
19.2;Working with Tables;454
19.2.1;Creating a Basic Table;454
19.2.2;Deleting a Table;456
19.2.3;Copying a Table;457
19.3;Changing the Table Size and Position;457
19.3.1;Resizing a Table;458
19.3.2;Moving a Table;458
19.4;Changing Column Order;458
19.5;Renaming Fields;459
19.6;Removing Columns from a Table;459
19.7;Table Granularity;460
19.8;Types of Data;461
19.9;Enhancing Tables;462
19.9.1;Row Totals;462
19.9.2;Formatting Numbers in Reports;464
19.9.3;Font Sizes in Tables;464
19.9.4;Changing Column Widths;465
19.9.4.1;Inhibiting Automatic Adjustment of Column Width and Enabling Word Wrap;465
19.9.5;Sorting by Column;466
19.10;Formatting Tables;468
19.11;Table Style;468
19.12;Adding and Formatting Titles;469
19.13;Modifying the Table Background;471
19.14;Table Borders;472
19.15;Row Formatting;473
19.16;Table Grid;474
19.17;Column Headers;476
19.18;Column Formatting;476
19.19;Formatting Totals;477
19.20;Conditional Formatting;478
19.20.1;Color Scales;478
19.20.2;Data Bars;480
19.20.3;Conditional Text and Background;482
19.21;Conclusion;483
20;Chapter 15: Matrix and Card Visuals;484
20.1;Creating a Matrix;484
20.1.1;Creating a Row Matrix;484
20.1.2;Adding or Removing Subtotals in a Matrix;488
20.1.3;Column Matrix;489
20.2;Expanding and Drilling Down and Up;491
20.2.1;The Data/Drill Ribbon;492
20.2.1.1;The Matrix Navigation Icons;493
20.2.1.2;The Context Menu for Matrix Items;493
20.2.2;Displaying Data at the Previous Level;493
20.2.3;Displaying Data for a Sublevel;494
20.2.4;Drilling Down at Row Level;494
20.2.5;Drill Down Using Click-Through;495
20.2.6;Drilling Down at Column Level;496
20.3;Visualize Source Data;497
20.4;Viewing Records;498
20.5;Including and Excluding Matrix Elements;499
20.6;Displaying Multiple Values As Rows;500
20.7;Formatting a Matrix;502
20.7.1;Stepped Layout;502
20.7.1.1;Subtotals;504
20.7.1.2;Placing Subtotals;505
20.7.1.3;Custom Subtotal Settings per Level of Matrix;506
20.8;Sorting Data in Matrices;507
20.9;Cards;508
20.9.1;Formatting Cards;509
20.10;Multirow Cards;511
20.10.1;Formatting Multirow Cards;513
20.10.2;Sorting Multirow Cards;514
20.11;Switching Between Table Types;515
20.12;Conclusion;516
21;Chapter 16: Charts in Power BI Desktop;517
21.1;A First Chart;518
21.1.1;Creating a First Chart;518
21.1.2;Converting a Table into a Chart;520
21.1.3;Deleting a Chart;521
21.1.4;Basic Chart Modification;521
21.2;Basic Chart Types;522
21.2.1;Column Charts;522
21.2.2;Line Charts;523
21.2.3;Pie Charts;523
21.3;Essential Chart Adjustments;524
21.3.1;Resizing Charts;525
21.3.2;Repositioning Charts;526
21.3.3;Sorting Chart Elements;526
21.4;Donut Charts;528
21.5;Funnel Charts;528
21.6;Multiple Data Values in Charts;529
21.7;100% Stacked Column and Bar Charts;533
21.8;Scatter Charts;534
21.9;Bubble Charts;536
21.10;Waterfall Charts;539
21.11;Ribbon Charts;539
21.12;Dual-Axis Charts;541
21.12.1;Line and Clustered Column Chart;541
21.12.2;Line and Stacked Column Chart;542
21.13;Data Details;543
21.14;Drilling into and Expanding Chart Data Hierarchies;544
21.14.1;Drill Down;545
21.14.2;Expand All Down One Level;547
21.14.3;Go to the Next Level;548
21.15;Including and Excluding Data Points;551
21.16;Conclusion;552
22;Chapter 17: Formatting Charts in Power BI Desktop;553
22.1;Multiple Chart Formatting;553
22.2;Chart Legends;553
22.2.1;Legend Display;554
22.2.1.1;Legend Position;554
22.2.1.2;Legend Title;555
22.2.2;Chart Title;556
22.2.3;Chart Data Labels;557
22.2.4;Chart Background;558
22.2.5;Data Colors;559
22.2.6;Plot Area;559
22.2.7;Axis Modification;560
22.2.7.1;Modifying the X Axis;560
22.2.7.2;Modifying the Y Axis;561
22.2.8;Chart Borders;564
22.2.9;General Attributes;564
22.2.10;Chart Aspect Ratio;564
22.3;Tooltips;565
22.4;Specific Chart Formatting;565
22.4.1;Line, Area, and Stacked Area Charts;566
22.4.2;Pie and Donut Charts;567
22.4.3;Ribbon Charts;568
22.4.4;Funnel Charts;569
22.4.5;Scatter and Bubble Charts;570
22.5;Bubble Chart Play Axis;572
22.6;Chart Analytics;574
22.7;Scatter Chart Symmetry Shading and Ratio Line;576
22.7.1;Symmetry Shading;576
22.7.2;Ratio Line;577
22.8;Conclusion;578
23;Chapter 18: Other Types of Visuals;579
23.1;Tree Maps;579
23.1.1;Drill Down and Tree Maps;581
23.1.2;Formatting Tree Maps;581
23.1.2.1;Legend;582
23.1.2.2;Data Colors;582
23.1.2.3;Data Labels;582
23.1.2.4;Category Labels;582
23.1.2.5;Title;582
23.1.2.6;Background;582
23.1.2.7;Lock Aspect;582
23.1.2.8;Border;582
23.2;Gauges;583
23.2.1;Formatting Gauges;584
23.3;KPIs;586
23.4;R Visuals;587
23.4.1;R Options;590
23.5;Additional Visuals;591
23.5.1;The Power BI Visuals Gallery;591
23.6;Loading Custom Visuals;594
23.7;A Rapid Overview of a Selection of Custom Visuals;598
23.7.1;Aster Plots;599
23.7.2;Radar Charts;599
23.7.3;Bullet Charts;600
23.7.4;Word Clouds;600
23.7.5;Sunburst Charts;601
23.7.6;Streamgraphs;601
23.7.7;Tornado Charts;602
23.7.8;Chord Charts;603
23.7.9;Sankey Diagrams;604
23.7.10;Correlation Plots;605
23.7.11;Countdown;607
23.8;Custom Slicers;607
23.9;Conclusion;607
24;Chapter 19: Maps in Power BI Desktop;608
24.1;Working with Bing Maps;608
24.2;Creating Maps in Power BI Desktop;609
24.3;Using Geographical Data;611
24.3.1;Geographical Data Types;612
24.3.1.1;Define the Data Category in the Data Model;613
24.3.1.2;Add Multiple Levels of Geographical Information;613
24.4;Drilling Down in Maps;613
24.5;Adjusting the Map Display in Power BI Desktop;614
24.5.1;Positioning the Map Elements;615
24.5.2;Zooming In or Out;615
24.5.3;Multivalue Series;615
24.5.4;Color Saturation;616
24.5.5;Highlighting Map Data;617
24.6;Filled Maps;618
24.7;Shape Maps;619
24.7.1;Map Keys;620
24.7.2;Adding Shape Maps;621
24.8;Formatting Maps;621
24.8.1;Maps;621
24.8.1.1;Data Colors;622
24.8.1.2;Category Labels;622
24.8.1.3;Bubbles;622
24.8.1.4;Map Controls;622
24.8.1.5;Map Styles;622
24.8.2;Filled Maps;623
24.8.3;Tooltips;624
24.8.4;Shape Maps;624
24.8.4.1;Specific Data Colors;625
24.8.4.2;Defining a Color Range for Data;625
24.9;ARCGis Maps;626
24.9.1;Creating an ARCGis Map;627
24.9.2;Selecting a Basemap Type;628
24.9.3;Selecting a Location Type;629
24.9.4;Adding a Map Theme;630
24.9.5;Choosing a Symbol Type;630
24.9.6;Adding Pins;631
24.9.7;Adding a Reference Layer;632
24.9.8;Adding Infographics;634
24.10;Conclusion;635
25;Chapter 20: Filtering Data;636
25.1;Filters;637
25.2;Visual-Level Filters;637
25.2.1;The Filters Well;638
25.2.2;Adding Filters;638
25.2.3;Applying Filters;639
25.2.4;The Select All Filter;641
25.2.5;Clearing Filters;642
25.3;Filtering Different Data Types;642
25.3.1;Filtering Numeric Data;643
25.3.1.1;Range Filter Mode;643
25.3.1.2;Numeric Filter Options;644
25.3.2;Filtering Date and Time Data;645
25.3.2.1;Date and Time Filters;646
25.3.2.2;Date Filter Options;650
25.3.2.3;Relative Date Filtering;650
25.3.3;Filtering True or False Data;652
25.4;Advanced Text Filters;652
25.4.1;Applying an Advanced Text Filter;653
25.4.2;Clearing an Advanced Filter;654
25.4.3;Reverting to Basic Filtering;654
25.4.4;Text Filter Options;654
25.4.4.1;Top N Filtering;655
25.5;Specific Visualization-Level Filters;656
25.6;Multiple Filters;657
25.7;Page-Level Filters;657
25.8;Report-Level Filters;659
25.9;Removing Filters;661
25.10;Filter Field Reuse;662
25.10.1;Requiring Single Selection;664
25.11;Using the Filter Hierarchy;664
25.12;Filtering Tips;665
25.12.1;Don’t Filter Too Soon;665
25.12.2;Annotate, Annotate, Annotate;665
25.12.3;Avoid Complex Filters;666
25.13;Conclusion;666
26;Chapter 21: Using Slicers;667
26.1;Slicers;667
26.1.1;Adding a Slicer;668
26.1.2;Applying Slicers;669
26.1.3;Clearing a Slicer;670
26.1.4;Deleting a Slicer;670
26.1.4.1;Converting a Slicer to Another Visual Type;670
26.1.5;Modifying a Slicer;671
26.2;Date Slicers;671
26.3;Formatting Slicers;673
26.3.1;Slicer Orientation;673
26.3.2;Modifying the Outline;674
26.3.3;Adjusting Selection Controls;674
26.3.3.1;Adding or Removing the Select All Box;675
26.3.3.2;Enabling Single Select;675
26.3.4;Setting the Exact Size and X and Y coordinates of a Slicer;675
26.3.5;Formatting the Slicer Header;676
26.3.6;Formatting Slicer Items;676
26.3.7;Sorting Slicer Elements;677
26.3.8;Switching to Dropdown Slicers;677
26.3.9;Exporting Slicer Data;678
26.4;Using Charts As Slicers;679
26.4.1;Charts As Slicers;679
26.4.2;Highlighting Chart Data;681
26.4.3;Cross-Chart Highlighting;682
26.4.4;Highlighting Data in Bubble Charts;684
26.5;Charts As Complex Slicers;686
26.5.1;Column and Bar Charts As Filters;688
26.6;Specifying Visual Interactions;690
26.7;What-If Slicers;691
26.8;Custom Visuals As Slicers;694
26.8.1;Timeline Slicer;694
26.8.2;Timebrush Slicer;695
26.8.3;Chicklet Slicer;695
26.9;Choosing the Correct Approach to Interactive Data Selection;696
26.10;Conclusion;696
27;Chapter 22: Enhancing Dashboards;697
27.1;Formatting Ribbons;697
27.1.1;The View Ribbon;698
27.1.2;The Format Ribbon;698
27.2;Formatting the Page;699
27.3;Aligning and Distributing Visuals;700
27.3.1;Aligning Visuals;700
27.3.2;Distributing Visuals;701
27.3.3;Aligning to the Grid;702
27.3.3.1;Displaying the Grid;702
27.3.4;Specifying the Exact Position of a Visual;702
27.4;Adding Text Boxes to Annotate a Report;703
27.4.1;Adding a Text Box;703
27.4.2;Moving Text Boxes;704
27.4.3;Formatting a Text Box;704
27.4.4;Adding a Hyperlink;705
27.4.5;Removing a Hyperlink;706
27.4.6;Deleting Text Boxes;706
27.5;Modifying the Page Background Color;706
27.6;Images;707
27.6.1;Image Sources;708
27.6.2;Adding an Image;708
27.6.3;Removing an Image;710
27.6.4;Resizing Images;710
27.6.5;Formatting Images;711
27.6.6;Background Images;711
27.6.6.1;Adding a Background Image;712
27.6.7;Some Uses for Independent Images;713
27.7;Adding Shapes;713
27.7.1;Formatting Shapes;714
27.7.1.1;Lines and Fill Color in Shapes;715
27.7.1.2;Shape Rotation;716
27.7.2;Removing Shapes;716
27.7.3;Standardizing Shapes;717
27.8;Organizing Visuals on the Page;717
27.8.1;Layering Visuals;717
27.9;Drill-Through;719
27.9.1;The Source Page;719
27.9.2;The Destination Page;720
27.9.3;Applying Drillthrough;722
27.9.4;The Back Button;723
27.10;Phone Layout;723
27.11;Report Themes;726
27.12;Exporting Data from a Visualization;727
27.13;Conclusion;728
28;Chapter 23: PowerBI.com;729
28.1;Publishing Reports to PowerBI.com;729
28.1.1;Creating a Power BI Account;730
28.2;Using Power BI Desktop Files in PowerBI.com;734
28.2.1;Logging On to PowerBI.com;735
28.2.2;Adding a Power BI Desktop File;737
28.2.3;Publishing a Power BI Desktop File;738
28.2.4;Dashboards, Reports, and Datasets;739
28.3;Interacting with a Report on PowerBI.com;740
28.3.1;Printing PowerBI.com Reports;740
28.4;Working with PowerBI.com Reports;740
28.4.1;The PowerBI.com Report Menu;741
28.4.2;Report Editing;741
28.4.3;Exiting Edit Mode;742
28.5;Adding Datasets to PowerBI.com;742
28.6;Creating New Reports in PowerBI.com;745
28.7;PowerBI.com Dashboards;746
28.7.1;Adding Tiles to PowerBI.com Dashboards;747
28.7.2;Pinning Entire Reports;749
28.7.3;Editing Dashboard Tiles;751
28.7.3.1;Deleting Tiles;751
28.7.3.2;Modifying Tile Details;752
28.7.3.3;Exporting the Data Behind the Tile;753
28.7.3.4;Pinning the Tile to Another Dashboard;753
28.7.4;Modifying Dashboards;753
28.7.4.1;Resizing Tiles;754
28.7.4.2;Adding Other Tiles (Images, Text Boxes, Video, or Other Web Content);754
28.7.4.3;Displaying Dashboards in Full Screen Mode;756
28.7.4.4;Printing Dashboards;757
28.8;PowerBI Pro;757
28.9;Sharing Dashboards;757
28.10;PowerBI.com Gateways;758
28.10.1;Downloading a Gateway;759
28.11;Configuring Data Sources;765
28.12;Applying Dataset Settings;766
28.12.1;Manual Data Refresh;768
28.13;Scheduled Data Refresh;769
28.14;PowerBI.com on Tablets and Smartphones;769
28.15;Conclusion;771
29;Appendix A: Sample Data;772
29.1;Sample Data;772
29.1.1;Downloading the Sample Data;772
29.1.2;Images;772
29.1.3;Sample Databases;773
29.1.3.1;The CarSalesData Database;773
29.1.3.2;The CarSalesMemoryBased Database;773
29.1.3.3;The Analysis Services Database;774
29.1.3.4;The SSAS Tabular Database;774
30;Index;775