Microsoft Power BI Complete Reference

Description

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