Microsoft Power BI Complete Reference

Title Page......Page 2
Copyright......Page 3
Microsoft Power BI Complete Reference......Page 4
About Packt......Page 6
Why subscribe?......Page 7
PacktPub.com......Page 8
Contributors......Page 9
About the authors......Page 10
About the reviewers......Page 13
Packt is searching for authors like you......Page 18
Preface......Page 48
Who this book is for......Page 50
What this book covers......Page 14
To get the most out of this book......Page 51
Download the example code files......Page 53
Conventions used......Page 55
Get in touch......Page 56
Reviews......Page 57
Getting Started with Importing Data Options......Page 58
Getting started......Page 60
Importing data......Page 63
Excel as a source......Page 65
SQL Server as a source......Page 68
Web as a source......Page 72
DirectQuery......Page 75
Limitations......Page 77
Live Connection......Page 80
Limitations......Page 82
Which should I choose?......Page 83
Summary......Page 85
Data Transformation Strategies......Page 86
The Power Query Editor......Page 88
Transform basics......Page 91
Use First Row as Headers......Page 93
Remove Columns......Page 95
Change type......Page 98
Add Column From Examples......Page 100
Advanced data transformation options......Page 102
Conditional Columns......Page 103
Fill Down......Page 106
Unpivot......Page 108
Merging Queries......Page 115
Appending Queries......Page 120
Leveraging R......Page 122
Installation and configuration......Page 123
The R Script transform......Page 126
M formula language......Page 129
#shared......Page 132
Summary......Page 134
Building the Data Model......Page 135
Building relationships......Page 136
Editing relationships......Page 141
Creating a new relationship ......Page 145
Working with complex relationships......Page 148
Many-to-many relationships......Page 149
Cross-filtering direction......Page 152
Enabling filtering from the many side of a relationship......Page 156
Role-playing tables......Page 158
Importing the date table......Page 161
Usability enhancements......Page 164
Hiding tables and columns......Page 165
Renaming tables and columns......Page 167
Default summarization......Page 170
How to display one column but sort by another......Page 173
Data categorization......Page 176
Creating hierarchies......Page 179
Summary......Page 183
Leveraging DAX......Page 184
Building calculated columns......Page 185
String functions – Month, Year......Page 188
Format function – Month Year......Page 190
Age calculation......Page 191
SWITCH() – age breakdown......Page 193
Navigation functions – RELATED......Page 195
Calculated measures – the basics......Page 199
Calculated measure – basic aggregations......Page 201
Total Sales......Page 202
Total Cost......Page 203
Profit......Page 204
Profit Margin......Page 206
Optional parameters......Page 207
Filter context......Page 208
Calculate......Page 209
Percentage of total calculation......Page 211
Time intelligence......Page 212
Year to Date Sales......Page 213
YTD Sales (Fiscal Calendar)......Page 214
Prior Year Sales ......Page 216
Summary......Page 217
Visualizing Data......Page 218
Data visualization basics......Page 221
Visuals for filtering......Page 225
Interactive filtering......Page 226
The Slicer visual......Page 231
Visualizing tabular data......Page 236
The table visual......Page 237
The Matrix visual......Page 241
Visualizing categorical data......Page 245
Bar and Column charts......Page 246
Pie and Donut charts......Page 249
The Treemap visual......Page 251
The Scatter chart......Page 254
Visualizing trend data......Page 257
Line and Area charts......Page 258
Combo charts......Page 260
The Ribbon Chart......Page 263
The Waterfall Chart......Page 265
The Funnel Chart......Page 267
Visualizing KPI data......Page 269
The Gauge visual......Page 270
The KPI visual......Page 273
Visualizing geographical data......Page 275
The Map visual......Page 277
The Filled Map visual......Page 280
The Shape Map visual......Page 281
The ArcGIS Map visual......Page 283
Leveraging Power BI custom visuals......Page 285
Data visualization tips and tricks......Page 286
Edit interactions......Page 287
The Analytics pane......Page 290
The Top N filter......Page 292
Show value as......Page 294
Summary......Page 296
Using a Cloud Deployment with the Power BI Service......Page 297
Deploying to the Power BI service......Page 298
DATASETS......Page 304
WORKBOOKS......Page 305
Creating and interacting with dashboards......Page 306
Creating your first dashboard......Page 307
Asking your dashboard a question......Page 311
Subscribing to reports and dashboards......Page 314
Sharing your dashboards......Page 315
Workspaces......Page 316
Setting up row-level security......Page 318
Scheduling data refreshes......Page 322
Summary......Page 328
Planning Power BI Projects......Page 329
Power BI deployment modes......Page 331
Corporate BI......Page 333
Self-Service Visualization......Page 335
Self-Service BI......Page 336
Choosing a deployment mode......Page 337
Project discovery and ingestion......Page 332
Sample Power BI project template ......Page 340
Sample template – Adventure Works BI......Page 341
Power BI project roles......Page 346
Dataset designer......Page 348
Report authors......Page 350
Power BI admin......Page 351
Project role collaboration......Page 354
Power BI licenses......Page 355
Power BI license scenarios......Page 359
Power BI Premium features......Page 361
Data warehouse bus matrix......Page 363
Dataset design process......Page 365
Selecting the business process......Page 366
Declaring the grain......Page 369
Identifying the dimensions......Page 371
Defining the facts......Page 374
Data profiling......Page 376
Dataset planning......Page 382
Data transformations......Page 383
DirectQuery mode......Page 387
Sample project analysis......Page 388
Summary......Page 391
Connecting to Sources and Transforming Data with M......Page 392
Query design per dataset mode......Page 394
Import mode dataset queries......Page 397
DirectQuery dataset queries......Page 399
Data sources......Page 401
Authentication......Page 403
Data source settings......Page 405
Privacy levels......Page 408
Power BI as a data source......Page 412
Power BI Desktop options......Page 414
Global options......Page 417
CURRENT FILE options......Page 418
SQL views......Page 421
SQL views versus M queries......Page 424
SQL view examples......Page 428
Date dimension view......Page 430
Mark As Date Table......Page 433
Product Dimension view......Page 436
Slowly-changing dimensions......Page 438
M queries......Page 439
Data Source Parameters......Page 442
Staging Queries......Page 445
DirectQuery staging......Page 448
Fact and dimension queries......Page 449
Source Reference Only......Page 450
M query summary......Page 452
Excel workbook – Annual Sales Plan......Page 454
Data types......Page 458
Item access in M......Page 460
DirectQuery report execution......Page 462
Bridge Tables Queries......Page 463
Parameter Tables......Page 465
Security Tables......Page 469
Query folding......Page 470
Partial query folding......Page 473
M Query examples......Page 474
Trailing three years filter......Page 475
Customer history column......Page 477
Derived column data types......Page 480
Product dimension integration......Page 482
M editing tools......Page 486
Advanced Editor......Page 487
Visual Studio Code......Page 489
Visual Studio......Page 491
Summary......Page 494
Designing Import and DirectQuery Data Models......Page 495
Dataset layers......Page 497
Dataset objectives......Page 499
Competing objectives......Page 503
External factors......Page 504
The Data Model ......Page 505
The Relationships View......Page 506
The Data View......Page 509
The Report View......Page 511
Fact tables......Page 513
Fact table columns......Page 514
Fact column data types......Page 517
Fact-to-dimension relationships......Page 519
Bridge tables......Page 525
Parameter tables......Page 527
Measure groups......Page 528
Last refreshed date......Page 531
Measure support logic......Page 533
Relationships......Page 536
Uniqueness ......Page 537
Ambiguity......Page 539
Model metadata......Page 542
Visibility......Page 543
Column metadata......Page 544
Default Summarization......Page 545
Data format......Page 547
Data category......Page 548
Field descriptions......Page 550
Optimizing performance......Page 553
Import......Page 554
Columnar compression......Page 555
Memory analysis via DMVs......Page 557
DirectQuery ......Page 560
Optimized DAX functions......Page 561
Columnstore and HTAP......Page 562
Summary......Page 564
Developing DAX Measures and Security Roles......Page 565
DAX measures......Page 567
Measure evaluation process......Page 568
Row context......Page 571
Scalar and table functions......Page 573
The CALCULATE() function......Page 575
Related tables......Page 577
The FILTER() function......Page 579
DAX variables......Page 581
Base measures......Page 586
Measure support expressions......Page 588
KPI Targets......Page 590
Current and prior periods......Page 592
Date intelligence metrics......Page 595
Current versus prior and growth rates......Page 599
Rolling periods......Page 600
Dimension metrics......Page 602
Missing dimensions......Page 604
Ranking metrics......Page 607
Dynamic ranking measures......Page 610
Security roles......Page 612
Dynamic row-level security......Page 618
Performance testing......Page 621
DAX Studio......Page 622
Tracing a Power BI dataset via DAX Studio......Page 624
Summary......Page 631
Creating and Formatting Power BI Reports......Page 632
Report planning......Page 634
Power BI report architecture......Page 642
Live connections to Power BI datasets......Page 645
Customizing Live connection reports......Page 650
Switching source datasets......Page 652
Visualization best practices......Page 654
Visualization anti-patterns......Page 662
Choosing the visual......Page 666
Tables versus charts......Page 668
Chart selection......Page 672
Visual interactions......Page 675
Edit interactions......Page 677
What-if parameters......Page 679
Slicers......Page 683
Slicer synchronization......Page 686
Custom slicer parameters......Page 689
Report filter scopes......Page 691
Report filter conditions......Page 694
Report and page filters......Page 699
Page filter or slicer?......Page 702
Relative date filtering......Page 704
Visualization formatting......Page 707
Visual-level formatting......Page 708
Line and column charts......Page 711
Tooltips......Page 714
Report page tooltips......Page 716
Column and line chart conditional formatting......Page 719
Column chart conditional formatting......Page 720
Line chart conditional formatting......Page 722
Table and matrix......Page 724
Table and matrix conditional formatting......Page 726
Values as rows......Page 730
Scatter charts......Page 731
Map visuals......Page 734
Bubble map......Page 736
Filled map......Page 739
Mobile-optimized reports......Page 741
Responsive visuals......Page 745
Report design summary......Page 747
Summary......Page 751
Applying Custom Visuals, Animation, and Analytics......Page 752
Drillthrough report pages......Page 754
Custom labels and the back button......Page 758
Multi-column drillthrough......Page 760
Bookmarks......Page 764
Selection pane and the Spotlight property......Page 767
Custom report navigation......Page 770
View mode......Page 772
ArcGIS Map visual for Power BI......Page 773
ArcGIS Maps Plus subscriptions......Page 779
Waterfall chart breakdown......Page 781
Analytics pane......Page 784
Trend Line......Page 785
Forecast line......Page 789
Quick Insights ......Page 793
Explain the increase/decrease......Page 796
Custom visuals......Page 799
Adding a custom visual......Page 801
Power KPI visual......Page 807
Chiclet Slicer......Page 814
Impact Bubble Chart......Page 817
Dot Plot by Maq Software......Page 820
Animation and data storytelling......Page 823
Play axis for scatter charts......Page 824
Pulse Chart......Page 827
Summary......Page 830
Designing Power BI Dashboards and Architectures......Page 831
Dashboards versus reports......Page 833
Multi-dashboard architectures......Page 835
Single-dashboard architecture......Page 837
Multiple-dashboard architecture......Page 839
Organizational dashboard architecture......Page 841
Multiple datasets......Page 846
Dashboard tiles......Page 849
Tile details and custom links......Page 852
Images and text boxes......Page 855
SQL Server Reporting Services......Page 857
Excel workbooks......Page 863
Live report pages......Page 868
Mobile-optimized dashboards......Page 872
Summary......Page 875
Managing Application Workspaces and Content......Page 876
Application workspaces......Page 878
Workspace roles and rights......Page 881
Workspace admins......Page 882
Workspace members......Page 884
My Workspace......Page 887
Staged deployments......Page 889
Workspace datasets......Page 894
Power BI REST API......Page 896
Client application ID......Page 897
Workspace and content IDs......Page 900
PowerShell sample scripts......Page 902
Dashboard data classifications......Page 905
Version control......Page 911
OneDrive for Business version history......Page 912
Source control for M and DAX code......Page 915
Metadata management......Page 918
Field descriptions......Page 919
Creating descriptions......Page 920
View field descriptions......Page 922
Metadata reporting......Page 926
Query field descriptions......Page 927
Standard metadata reports......Page 929
Server and database parameters......Page 930
Querying the DMVs from Power BI......Page 932
Integrating and enhancing DMV data......Page 935
Metadata report pages......Page 936
Summary......Page 938
Managing the On-Premises Data Gateway......Page 939
On-premises data gateway planning......Page 941
Top gateway planning tasks......Page 948
Determining whether a gateway is needed......Page 949
Identifying where the gateway should be installed......Page 951
Defining the gateway infrastructure and hardware requirements......Page 953
On-premises data gateway versus personal mode......Page 955
Gateway clusters......Page 958
Gateway architectures......Page 961
Gateway security......Page 966
Gateway configuration......Page 971
The gateway service account......Page 974
TCP versus HTTPS mode ......Page 976
Managing gateway clusters......Page 978
Gateway administrators......Page 980
Gateway data sources and users......Page 982
PowerShell support for gateway clusters......Page 984
Troubleshooting and monitoring gateways......Page 986
Restoring, migrating, and taking over a gateway......Page 988
Gateway log files......Page 990
Performance Monitor counters......Page 992
DirectQuery datasets......Page 994
Single sign-on to DirectQuery sources via Kerberos......Page 995
Live connections to Analysis Services models......Page 997
Azure Analysis Services refresh......Page 999
Dashboard cache refresh......Page 1002
Summary......Page 1006
Deploying the Power BI Report Server......Page 1007
Planning for the Power BI Report Server......Page 1009
Feature differences with the Power BI service......Page 1013
Parity with SQL Server Reporting Services......Page 1017
Data sources and connectivity options......Page 1020
Hardware and user licensing......Page 1022
Pro licenses for report authors......Page 1025
Alternative and hybrid deployment models......Page 1026
Report Server reference topology......Page 1029
Installation......Page 1031
Hardware and software requirements......Page 1033
Analysis Services Integrated......Page 1035
Retrieve the Report Server product key......Page 1036
Migrating from SQL Server Reporting Services......Page 1039
Configuration......Page 1043
Service Account......Page 1045
Remote Report Server Database......Page 1047
Office Online Server for Excel Workbooks......Page 1049
Upgrade cycles ......Page 1051
Report Server Desktop Application......Page 1053
Running desktop versions side by side......Page 1055
Report Server Web Portal......Page 1057
Scheduled data refresh......Page 1060
Data source authentication......Page 1062
Power BI mobile applications......Page 1065
Report server administration......Page 1067
Securing Power BI report content......Page 1068
Execution logs......Page 1071
Scale Power BI Report Server......Page 1074
Summary......Page 1076
Creating Power BI Apps and Content Distribution......Page 1077
Content distribution methods......Page 1079
Power BI apps......Page 1082
Licensing apps......Page 1083
App deployment process......Page 1084
User permissions......Page 1088
Publishing apps......Page 1091
Installing apps......Page 1095
Apps on Power BI mobile......Page 1099
App updates......Page 1101
Dataset-to-workspace relationship......Page 1103
Self-Service BI workspace......Page 1105
Self-Service content distribution......Page 1106
Risks to Self-Service BI......Page 1108
Sharing dashboards and reports......Page 1110
Sharing scopes......Page 1116
Sharing versus Power BI apps......Page 1117
SharePoint Online embedding......Page 1118
Custom application embedding......Page 1123
Publish to web......Page 1126
Data alerts......Page 1128
Microsoft Flow integration......Page 1132
Email Subscriptions......Page 1135
Analyze in Excel......Page 1138
Power BI Publisher for Excel......Page 1142
Summary......Page 1144
Administering Power BI for an Organization......Page 1145
Data governance for Power BI......Page 1147
Implementing data governance......Page 1150
Azure Active Directory......Page 1154
Azure AD B2B collaboration......Page 1155
Licensing external users......Page 1159
Conditional access policies......Page 1161
Power BI Admin Portal......Page 1165
Usage metrics......Page 1167
Users and Audit logs......Page 1168
Tenant settings......Page 1169
Embed Codes......Page 1173
Organizational Custom visuals......Page 1175
Usage metrics reports......Page 1179
Audit logs......Page 1186
Audit log monitoring solutions......Page 1192
Audit logs solution template......Page 1197
Power BI Premium capacities......Page 1202
Capacity allocation......Page 1206
Create, size, and monitor capacities......Page 1209
Change capacity size......Page 1212
Monitor premium capacities......Page 1214
App workspace assignment......Page 1217
Capacity admins......Page 1220
Summary......Page 1221
Scaling with Premium and Analysis Services......Page 1222
Power BI Premium......Page 1224
Power BI Premium capabilities......Page 1227
Corporate Power BI datasets......Page 1230
Limitation of Corporate BI datasets – Reusability......Page 1231
Premium capacity nodes......Page 1234
Frontend versus backend resources......Page 1237
Power BI Premium capacity allocation......Page 1240
Corporate and Self-Service BI capacity ......Page 1242
Power BI Premium resource utilization......Page 1245
Data model optimizations......Page 1246
Report and visualization optimizations......Page 1250
Premium capacity estimations......Page 1253
Analysis Services......Page 1258
Analysis Services Models versus Power BI Desktop......Page 1260
Scale......Page 1263
Usability......Page 1266
Development and management tools......Page 1269
Azure Analysis Services versus SSAS......Page 1272
SSAS to Azure AS Migration......Page 1276
Provision Azure Analysis Services ......Page 1279
Migration of Power BI Desktop to Analysis Services......Page 1282
Summary......Page 1289
Other Books You May Enjoy......Page 1290
Leave a review - let other readers know what you think......Page 1293