This page has 0 threads | Add post

Courseware

Fast-track Power BI/DAX course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Power BI courses).

Click here to download a sample of the first two chapters of this Fast-track Power BI/DAX manual, or review how to license our courseware.

Chapter 1 - GETTING STARTED▼

Section	Title	Subsections	Page
1.1	Getting Started in Power BI Desktop	Example for this Chapter	13
1.2	Working with Files	Creating New Files Opening and Saving Files	14
1.3	Views in Power BI Desktop	Switching Panes Report, Table and Model View	15
1.4	Getting Data		17
1.5	Transforming Data	Editing Queries / Transforming Data The Power BI Query Editor Promoting Row Headers Replacing Values Changing Data Types Removing Columns	18
1.6	Creating Visuals	Inserting a Visual Assigning Grouping Fields to a Visual Assigning Numerical Fields	21
1.7	Three Ways to Format Visuals	In Situ Selection Changing what’s on your Chart The Format Pane	23
1.8	Publishing your Report		25

Chapter 2 - IMPORTING DATA▼

Section	Title	Subsections	Page
2.1	Our Example		26
2.2	Importing from Different Sources	Re-Using a Data Source	27
2.3	Importing from Excel		28
2.4	Importing CSV or Text Files		29
2.5	Importing from SQL Server	Using Queries and Stored Procedures Passing Arguments to Stored Procedures	29
2.6	Importing from a Website		32
2.7	Entering Data Manually	Pasting Data Typing in Data	33

Chapter 3 - DATA MODELS▼

Section	Title	Subsections	Page
3.1	Data Models	Viewing a Model Selecting Single Model Items Selecting Multiple Items Searching for Fields	35
3.2	Model Diagrams	Arranging Tables in a Model Diagram Layouts Collapsing and Expanding Tables Controlling Expand/Collapse Field Visibility Seeing Table Information	37
3.3	Hiding Objects	Why you might Want to Hide Tables and Fields Hiding Tables Hiding Fields/Columns	39
3.4	Model Properties	Table Properties Display Folders Default Number and Date Formatting Changing the Default Aggregation for a Field	41
3.5	Relationships	The Need for Relationships Parent-Child Relationships Creating a Relationship Editing Relationships The Effect of Relationships Cross-Filter Direction	43

Chapter 4 - VISUALS▼

Section	Title	Subsections	Page
4.1	Overview of Visuals		46
4.2	Adding and Changing Visuals	Adding a Visual then Selecting its Data Data-First Visual Creation Changing Visual Types	47
4.3	Working with Visuals	The Visual Header Focus Mode Spotlight Mode Resizing a Visual Selecting Visuals Moving, Copying and Deleting Visuals Locking Visuals Aligning and Distributing Visualisations Grouping Visualisations Showing a Visual’s Underlying Data	49
4.4	The Selection Pane	Viewing the Selection Pane Changing the Visual Layer Order Changing the Tab Order Hiding Visuals	54

Chapter 5 - VISUAL INTERACTIONS▼

Section	Title	Subsections	Page
5.1	Overview		56
5.2	Editing Visual Interactions	What the 3 Symbols Mean	57

Chapter 6 - FORMATTING VISUALS▼

Section	Title	Subsections	Page
6.1	Finding Formatting Properties	Searching for a Property Finding a Property	59
6.2	Common Formats to Apply	Setting Visual Backgrounds Borders and Shadow Effects Titles, Subtitles and Dividing Lines	60
6.3	Header Icons		63
6.4	Tooltips		64
6.5	Useful General Formatting Tricks	The Wonderful Format Painter Expanding and Collapsing Cards Removing all Formatting	65
6.6	Themes		67

Chapter 7 - TABLES▼

Section	Title	Subsections	Page
7.1	Basic Tables		68
7.2	Working with Table Columns	Sorting Tables by Columns Re-ordering Columns Changing Column Widths Automatically Changing Column Widths Manually Changing Word Wrap Options Renaming Columns	68
7.3	Aggregating Data		72
7.4	Formatting Numbers	Formatting Numbers within a Single Table Formatting Numbers for all Visuals Setting Custom Number Formats	73
7.5	Working with Dates in Tables	Displaying Dates as Dates Changing the Default Format for a Date Setting a Custom Date Format	75
7.6	Specific to Formatting Tables	Column Headers Totals Font Size and Typeface Table Padding and Gridlines Table Styles Formatting Columns Individually	77

Chapter 8 - CONDITIONAL FORMATTING▼

Section	Title	Subsections	Page
8.1	Conditional Formatting		80
8.2	Applying Conditional Formatting	Setting Conditional Formatting Changing Conditional Formatting	81
8.3	Gradient Effects	Adding a Middle Colour	82
8.4	Rules-Based Conditional Formatting		83
8.5	Formatting Using Field Values		84
8.6	Data Bars		85
8.7	Displaying Icons		86

Chapter 9 - MATRICES▼

Section	Title	Subsections	Page
9.1	Overview of Matrices		87
9.2	Creating a Matrix	The Sections of a Matrix Sorting in a Matrix	88
9.3	Multiple Rows, Columns and Values	Multiple Row Fields Multiple Values Fields Multiple Column Fields	89

Chapter 10 - TEXT BOXES, IMAGES AND SHAPES▼

Section	Title	Subsections	Page
10.1	Overview	Drawing Text Boxes, Images or Shapes	91
10.2	Images	Scaling Images Adding a Hyperlink to an Image	92
10.3	Text Boxes	Inserting Values	93
10.4	Shapes	Adding a Shape	93

Chapter 11 - CHART BASICS▼

Section	Title	Subsections	Page
11.1	The Parts of a Chart		95
11.2	Types of Chart Available		96
11.3	Working with Charts	Creating a Chart Suggesting Chart Types Sorting Charts Zoom Sliders Excluding and Including Data	97
11.4	Quick Ways to Format Charts		101
11.5	Chart Legends		102
11.6	Detail Labels	Leader Lines for Data Labels Total Labels	103
11.7	Background and Gridlines	Plot Area and Background Gridlines	105
11.8	Axes	Categorical versus Continuous Formatting Formatting Axes Scaling Ranges Spacing Categories	106
11.9	Conditional Formatting		108

Chapter 12 - SMALL MULTIPLES▼

Section	Title	Subsections	Page
12.1	Overview of Small Multiples		109
12.2	Creating and Formatting Small Multiples	Setting Grid Width and Height Formatting Small Multiple Titles Other Formatting Options Suppressing Axis Titles Using Different Axes for Different Charts	110
12.3	Making the Background Colour Dynamic		113

Chapter 13 - MORE ON CHARTS▼

Section	Title	Subsections	Page
13.1	Line, Combination and Area Charts	Multiple Fields in Line Charts Line Formatting Secondary Axes Combination Charts Area Charts	114
13.2	Pie, Donut and Treemap Charts	Formatting Pie and Donut Charts	118
13.3	Scatter and Bubble Charts	Creating Scatter Charts Making Bubble Charts Formatting Scatter and Bubble Charts Animating Bubble Charts	119

Chapter 14 - GROUPING AND BINNING▼

Section	Title	Subsections	Page
14.1	Grouping	Starting a Group Editing Groups Using Group Fields	122
14.2	Binning		123

Chapter 15 - DRILL-DOWN▼

Section	Title	Subsections	Page
15.1	Drill-Down for Charts	What is Drill-Down? Enabling Drill Down Drilling Down Drilling Up What Happens when you Drill Down Drill Down and Visual Interactions Viewing the Next Hierarchy Level Expanding All Levels in a Hierarchy	125
15.2	Drill-Down in a Matrix	Choosing Row or Column Fields	129

Chapter 16 - CARDS▼

Section	Title	Subsections	Page
16.1	New Cards		130
16.2	Working with Cards	Creating a New Card Formatting Card Values and Labels Formatting the Numbers in Cards Card Shapes Formatting the Cards Themselves Adding Images to Cards	131
16.3	Reference Labels		134
16.4	Multi-row Cards		135

Chapter 17 - GAUGES▼

Section	Title	Subsections	Page
17.1	Gauges	Adding a Gauge Minimum, Maximum and Target Values Formatting Gauges	136

Chapter 18 - KEY PERFORMANCE INDICATORS (KPIS)▼

Section	Title	Subsections	Page
18.1	Overview of KPIs	Creating a KPI	138
18.2	Creating Targets	Formatting KPIs	139

Chapter 19 - SLICERS▼

Section	Title	Subsections	Page
19.1	Introducing Slicers		141
19.2	Working with Slicers	Creating a Slicer Selecting and Clearing Items Changing Selection Behaviour Searching in Slicers Dropdown Slicers Tile Slicers Customising your Slicer Header	142
19.3	Hierarchical Slicers		146
19.4	Number and Date Slicers	Sliders Choosing Dates Picking Relative Dates Changing the Anchor Date	147

Chapter 20 - NEW SLICERS▼

Section	Title	Subsections	Page
20.1	Overview of the New Slicer Visual	Benefits of the New Slicer Visual Enabling the Visual	149
20.2	Working with the New Slicer	Adding a New Slicer Visual Basic Configuration Adding Images and Text Adding Hover and Selection Effects	150

Chapter 21 - FILTERING REPORTS▼

Section	Title	Subsections	Page
21.1	How Filters Work		153
21.2	Working with Filters	Showing the Filters Pane Applying a Basic Filter Adding Fields to the Filters Pane Removing a Filter Advanced Text Filters Advanced Number Filters Relative Date Filtering Top and Bottom Filters Sorting Filters	154
21.3	Formatting Filters		158
21.4	Controlling Filters for End Users	Locking and Hiding Filters Report Filter Settings	159

Chapter 22 - DRILL-THROUGH FILTERS▼

Section	Title	Subsections	Page
22.1	What are Drill-Through Filters?		160
22.2	Creating a Drill-through Filter	Step 1 – Create the Main Report Page Step 2 – Create the Drill-Through Target Page Step 3 – Name and Hide your Drill-Through Page Step 4 – Configuring your Drill-Through Page Step 5 – Testing your Drill-Through Page	161
22.3	Three Ways to Drill Through	Modern Tooltips (Left Clicking) Right-Clicking Using a Drill-Through Button	163
22.4	Adding a Context-Sensitive Title		164
22.5	Extra Drill-through Options	Keeping All Filters Drill-through from Summarised Fields	165

Chapter 23 - BUTTONS AND SHAPES▼

Section	Title	Subsections	Page
23.1	Overview	Types of Clickable Objects Types of Action	166
23.2	Adding Clickable Shapes		167
23.3	Adding Clickable Images		167
23.4	Adding Clickable Buttons	Adding the Button Setting Default, Hover, Selection and Disabled Effects Formatting Buttons	169

Chapter 24 - PAGE NAVIGATION▼

Section	Title	Subsections	Page
24.1	Overview		171
24.2	Page Navigators	Creating Page Navigators Formatting Page Navigators Displaying Page Navigators as Grids	172
24.3	Simple Custom Page Navigators		174
24.4	Dynamic Page Navigation	Step 1 – Creating (and Editing) the Table Step 2 – Creating the Slicer Step 3 – Creating the Button Step 4 – Setting the Button’s Action and Tooltip Step 5 – Showing your Menu on All Pages	175

Chapter 25 - CUSTOM VISUALS▼

Section	Title	Subsections	Page
25.1	What are Custom Visuals?		178
25.2	Adding Custom Visuals	Pinning Custom Visuals	179
25.3	Working with Custom Visuals	Applying a Custom Visual Removing Custom Visuals	180

Chapter 26 - QUERYING DATA▼

Section	Title	Subsections	Page
26.1	What are Queries?		181
26.2	Working with Queries	Opening the Query Editor The Query Editor Default Query Steps Viewing Data at Different Steps Editing a Query Step Renaming Steps Deleting a Query Step Deleting Multiple Query Steps Adding a New Step Viewing M Formulae Applying Query Changes	182
26.3	Common Transforms	Changing Data Types Renaming Columns Removing Columns Removing Rows Sorting Rows Filtering Rows Splitting Columns by Delimiter Splitting Columns by Number of Characters Extracting Values Replacing Values Duplicating Columns	187
26.4	Creating New Columns	Creating a Column by Example Creating a Formula Creating Conditional Columns	192

Chapter 27 - MAPS▼

Section	Title	Subsections	Page
27.1	Overview of Maps		195
27.2	Choosing a Map Visual		196
27.3	Maps Using Latitude and Longitude	Stopping Aggregation for Simple Maps Changing the Aggregation Method for Locations	197
27.4	Maps without Latitude and Longitude	Using a Recognised Geographical Entity Categorising Columns as Places Fixing Locations to the UK Converting Postcodes to Latitude/Longitude	199
27.5	General Maps	Treating Maps as Visuals Changing the Map Style Conditional Formatting Manual Zoom Settings Viewing Controls Selecting Points on a Map Selecting within Driving Time/Distance Drill-Down in Maps	201
27.6	Specific Types of Maps	Bubble Maps Heat Maps Cluster Maps	206
27.7	Layers	3D Column Layers Traffic Layers Reference Layers	208

Chapter 28 - PUBLISHING▼

Section	Title	Subsections	Page
28.1	Overview of Publishing		211
28.2	An Infinite Number of Variables	Your Power BI Licence Linking to Data Data Sources	212
28.3	Workspaces	Choosing a Workspace	214
28.4	Creating Workspaces		215
28.5	Publishing Reports		216
28.6	Viewing and Editing Reports	Reports and Datasets Viewing Individual Reports Editing a Report	217
28.7	Dashboards and Tiles	Adding Tiles to Dashboards Using Tiles	219
28.8	Lineage View		220
28.9	Refreshing Data	Types of Connection Types of Refresh Web Connections: a Warning Viewing and Managing Connections Viewing Individual Connections Scheduling Refreshes	220
28.10	Sharing and Exporting Reports	Creating a PowerPoint Presentation Exporting to PDF Generating a Public URL for your Report Embedding your Report in a Website Creating a Power BI Report File Sharing a Report	225

Chapter 29 - INTRODUCTION TO DAX IN POWER BI▼

Section	Title	Subsections	Page
29.1	DAX in Power BI	How DAX is Used 1 – Calculated Columns How DAX is Used 2 – Measures How DAX is Used 3 – Calculated Tables Choosing the Type of Calculation Where Else is DAX Used?	228
29.2	The Construct-a-Creature Database	The Tables and Relationships	230
29.3	Getting Help with DAX	Getting Help within Power BI Other Sources of Help	231

Chapter 30 - BASIC CALCULATED COLUMNS▼

Section	Title	Subsections	Page
30.1	Calculated Columns	Key Features of Calculated Columns Creating a Calculated Column Entering a Formula Calculated Column Properties Using Calculated Columns in Visuals	232
30.2	Referencing Columns and Tables	Qualifying Column Names Table Names Referencing Columns in Other Tables The Related Function	235
30.3	Editing DAX Code	Multiple Lines and Indenting Comments Keyboard Shortcuts	237

Chapter 31 - WORKING WITH DATA TYPES▼

Section	Title	Subsections	Page
31.1	DAX Data Types	Viewing a Column’s Data Type	239
31.2	Working with Numbers	Basic Arithmetic Controlling the Calculation Order Safely Dividing Numbers Numeric Functions	240
31.3	Working with Text	Writing Text in Calculations Concatenating Text	243
31.4	Text Functions	Finding and Extracting Text Replacing Text Generating Text Converting and Formatting Text	244
31.5	Working with Dates	Entering Date and Time Values Returning the Current Date and Time Calculating Date and Time Values Calculating the Difference Between Dates Extracting Date Parts Formatting Dates	246

Chapter 32 - CONDITIONAL FUNCTIONS▼

Section	Title	Subsections	Page
32.1	The IF Function	Testing a Single Condition Comparison Operators The IN Operator Nesting IF Functions Combining Logical Tests The NOT Operator	249
32.2	Working with Blanks	Producing a Blank Blank Arithmetic Testing for Blanks The COALESCE Function	251
32.3	Testing for Errors	The ISERROR and IFERROR Functions Avoiding Error Functions	253
32.4	The SWITCH Function	A Simple SWITCH Function Logical Tests in a SWITCH Function	254

Chapter 33 - BASIC MEASURES▼

Section	Title	Subsections	Page
33.1	Introduction to Measures	Measures vs. Calculated Columns Implicit Measures	255
33.2	Creating a Measure	Adding a Measure to a Table Formatting Measures Displaying a Measure in a Visual Referencing Measures	256
33.3	Filter Context	What is Filter Context? How DAX Applies Filter Context	259
33.4	Measures Tables	Creating a Separate Measures Table Moving Measures	261
33.5	Quick Measures	Creating a Quick Measure Editing a Quick Measure Using a Quick Measure	263

Chapter 34 - AGGREGATION FUNCTIONS▼

Section	Title	Subsections	Page
34.1	Aggregating Column Values	Basic Aggregation Functions Functions for Counting Dealing with Boolean Values	265
34.2	Aggregating Expressions	The AggregateX Functions	267
34.3	Iterators and Row Context	A Reminder of Filter Context Row Context in Iterator Functions The Final Result How to Spot Iterators	268

Chapter 35 - THE CALCULATE FUNCTION▼

Section	Title	Subsections	Page
35.1	Introducing the CALCULATE Function	Expressions in the CALCULATE Function	270
35.2	Adding New Filters	Basic Filter Expressions Adding Multiple Filters Filter Arguments and Filter Context Multiple Columns in Filter Arguments	271
35.3	Replacing Filters	Replacing an Existing Filter Comparing Differently Filtered Measures Dealing with Blank Values	274
35.4	Keeping Filters	The Problem with the Default Behaviour The KEEPFILTERS Function Using the VALUES Function	276
35.5	Removing Filters	Removing Every Filter Using the ALL Function Comparing Filtered and Unfiltered Values Removing Filters from Specific Fields An Issue with Sort-By Fields Removing Filters from a Specific Table	278
35.6	Special Filter Removal Functions	The ALLEXCEPT Function The ALLSELECTED Function	282

Chapter 36 - VARIABLES▼

Section	Title	Subsections	Page
36.1	Introduction to Variables	Using Variables in Measures	284
36.2	How Variables are Evaluated	Lazy Evaluation DAX Variables are Constants	285
36.3	Debugging with Variables	Returning Different Variables	286
36.4	Nesting Variables	Variables in Functions Variable Scope	287

Chapter 37 - THE FILTER FUNCTION▼

Section	Title	Subsections	Page
37.1	Introduction to the FILTER Function	A Basic FILTER Example Using the CALCULATE Function How CALCULATE and FILTER are Related Using Multiple Filters Using Variables	289
37.2	FILTER vs. CALCULATE	Referencing Multiple Fields Using Fields from Different Tables Referring to Measures Replacing Filters	291
37.3	The CALCULATETABLE Function	Using CALCULATETABLE	294

Chapter 38 - FILTERS AND RELATIONSHIPS▼

Section	Title	Subsections	Page
38.1	Relationships and Filter Direction	The Problem with Relationships Changing the Cross Filter Direction Solving the Problem using Filters	295
38.2	Cross Filter Direction in Measures	The CROSSFILTER Function Using Single and Both Filter Directions Simultaneously Multiple CROSSFILTER Functions	298

Chapter 39 - CONTEXT TRANSITION▼

Section	Title	Subsections	Page
39.1	What is Context Transition?	Row and Filter Context	300
39.2	Context Transition in Calculated Columns	Row Context in Calculated Columns Performing Context Transition Implicit Context Transition The RELATEDTABLE Function	301
39.3	Context Transition in Measures	Row Context in Measures Context Transition in Measures The Effect of Filter Context Removing Filters	303
39.4	Ranking Values	The RANKX Function Ranking in Calculated Columns Context Transition in Calculated Columns Ranking in Measures	306

Chapter 40 - TIME INTELLIGENCE▼

Section	Title	Subsections	Page
40.1	Introduction to Time Intelligence	Calendar Tables The Date Column Referring to Calendar Tables	308
40.2	Time Intelligence Functions	General Time Intelligence Functions Using the DATEADD Function How DATEADD Works Using the DATESINPERIOD Function Using the Current Date Using Specific Dates	310
40.3	To Date Calculations	Returning Date Ranges Calculating Running Totals Total To Date Functions Easier Running Total Calculations Specifying Year End Dates Calculating Life to Date Values	313
40.4	Next and Previous Periods	Next and Previous Period Functions Comparing Entire Previous Years Comparing Parts of Previous Years	316
40.5	Period Start and End Dates	Period Start and End Functions Start and End Dates Opening and Closing Balances First and Last Non-Blank Dates First and Last Non-Blank Values Non-Blank Opening Balances	319
40.6	Moving Averages	Calculating a Moving Average	323

Chapter 41 - CUSTOM CALENDARS▼

Section	Title	Subsections	Page
41.1	Why use Custom Calendars?	Disabling Automatic Calendars	324
41.2	Creating a Custom Calendar	The CALENDARAUTO and CALENDAR Functions Adding Extra Columns Financial Years	325
41.3	Finishing the Calendar	Marking as a Date Table Changing Default Aggregations Setting Sort-By Columns Creating Hierarchies Hiding Fields Creating a Relationship	328
41.4	Using a Custom Calendar	Creating Visuals Time Intelligence Functions	331
41.5	Multiple Date Fields	Using Multiple Calendars Using a Single Calendar Changing the Active Relationship The USERELATIONSHIP Function	332
41.6	Special Dates		335

Chapter 42 - DYNAMIC MEASURES▼

Section	Title	Subsections	Page
42.1	Dynamic Labels	Why use Dynamic Labels?	336
42.2	Returning a Single Value	The VALUES Function Testing for a Single Value The SELECTEDVALUE Function	337
42.3	Concatenating Values	The CONCATENATEX Function More Complex Expressions	339
42.4	Filtered Values	Testing for Filtered Values Testing for Cross Filtered Values Selecting the Top N Rows	340
42.5	Disconnected Slicers	Creating a Disconnected Table Creating a Disconnected Slicer Referencing the Selected Value	342
42.6	Formatting with Measures	Calculating Colours with Measures Using Measures in Conditional Formatting Choosing Colours with Slicers	344

Chapter 43 - VISUAL CALCULATIONS▼

Section	Title	Subsections	Page
43.1	What are Visual Calculations?		346
43.2	Creating Visual Calculations	Creating a Visual Adding a Visual Calculation Writing the Formula Using Pre-Defined Visual Calculations	347
43.3	Editing a Visual Calculation	Formatting the Results Editing the Formula	350
43.4	Hiding Fields in a Visual		351
43.5	Running Totals	Calculating a Running Total Running Totals and Hierarchies Resetting a Running Total Changing the Direction	352
43.6	Moving Averages	Calculating a Moving Average Excluding the Current Row	355
43.7	Referring to Other Rows	Next and Previous Rows Setting the Step Value First and Last Rows	356
43.8	Referring to Parent Values	Collapsing Filters Collapsing Filters Relatively Collapsing All Filters	358
43.9	Referring to Child Rows	Expanding Filters Expanding Filters Relatively	360
43.10	Controlling Group Headers	Testing for the Presence of a Column	361

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

Power BI course manuals