Beginning Power BI for Business Users

a Million 113 Removing Implicit Measures 114 Column Formatting 115 Calculated Columns 115 Add Previous Year Column 116 Hiding Columns 117 LOOKUPVALUE Function 118 DIVIDE Function 119 Visualize Our GDP Data 120 Where Are We? 121 Cleaning Messy Data 122 Handle Many-to-Many Relationships 123 Duplicate a Query 125 Split Column 125 Format Column 127 Remove Duplicates 127 Index Column 128 Merge Data 128 Applying Changes 129 Finding the Power Query Button 131 Simple Cleanup Tasks 131 Replacing Values 132 Custom Columns 133 More Cleanup 135 Cleaning Date Columns 136 Conclusion 138 Chapter 6: Modeling Data 139 Dimensional Modeling 140 Why Does This Matter? 143 Makes Sense to You 143 Cross-Business Process Analysis 144 Flexible Grouping 144 Performance for Power BI 144 Facts 145 Types of Facts 146 Granularity 146 Relationships Between Facts (Never!) 147 Dimensions 148 Using Integer Keys for Relationships 149 Hierarchies in Dimensions 149 Date Dimensions 150 Power Query and Data Modeling 151 How to Build a Date Dimension 152 Import a Date Table from SQL Server Database 153 Create a Date Dimension with Power Query 155 Create a Date Dimension with DAX 158 Sort by Column 161 Mark It as a Date Table 161 How to Arrange into a Star- Schema 161 Create a State Dimension 162 Create a GDP Category Dimension 163 Add a New StateID Foreign Key to Our Fact Table 163 Unpivot the GDP Year Values 164 Putting It All into Action 166 Conclusion 167 Part 3: Going Deeper Into Power BI Chapter 7: Relationships and Filtering 171 Filtering Through Relationships 171 Visuals Interact Independently 174 The Direction of Relationships 176 Bidirectional Filtering 179 Report Filter Types 181 Visual Filters 181 Date Filtering 182 Numeric Filtering 182 Text Filtering 183 Page and Report Filters 185 Controlling the Use of Filters 185 Conclusion 186 Chapter 8: Enhancing Your Report 187 Configuring Drill- Down 187 Configuring Drill- Through 190 Buttons 193 Bookmarks 195 Personal Bookmarks vs. Report Bookmarks 199 Selections and Visibility 201 Conclusion 205 Chapter 9: Refreshing, Sharing, and Collaborating 207 Refreshing Data 207 Configuring Data Source Credentials 210 Power BI Data Gateway 213 Scheduling Refresh 214 Dataset Modes 216 Sharing and Collaborating 216 Workspaces 216 Creating Workspaces 217 Publishing 217 Security and Permission Levels 219 Power BI Apps 223 Sharing 230 Summary 232 Chapter 10: Introducing Dax 233 Setup 233 Calculated Columns 235 How to Create a Calculated Column 236 DAX Measures 240 COUNTROWS and DISTINCTCOUNT 244 Filter Context 246 CALCULATE Function 249 Conclusion 250 Chapter 11: Conclusion 251 Where Does My Organization Begin? 251 Delivery Strategy 251 Business-Led Self-Service 252 IT-Managed Self-Service 253 Corporate BI 253 Which Delivery Strategy Is Right for My Organization? 253 Where Do I Go from Here? 254 Top Gurus 254 Advice 254 Index 257