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 - POWER BI BASICS▼

Section	Title	Subsections	Page
1.1	What is Power BI Desktop?		10
1.2	Installing and Updating Power BI		11

Chapter 2 - GETTING STARTED▼

Section	Title	Subsections	Page
2.1	Getting Started in Power BI Desktop	Example for this Chapter Showing Keyboard Shortcuts	12
2.2	Working with Files	Creating New Files Saving and Closing Files	13
2.3	The Power BI Desktop Screen		14
2.4	Getting Data		15
2.5	Transforming Data	Editing a Query The Power Query Editor Removing and Renaming Columns Splitting Columns	16
2.6	Creating Visualisations	Inserting a Visualisation Assigning Fields to a Visualisation	18
2.7	Formatting Visuals	Searching for Format Properties	19
2.8	Filtering Visuals	Adding Fields to the Filters List Applying a Filter	20
2.9	Working with Report Pages	Changing the Page View Page Settings Renaming Pages Adding and Deleting Pages Hiding Pages	21
2.10	Themes		23

Chapter 3 - IMPORTING DATA▼

Section	Title	Subsections	Page
3.1	Our Example		24
3.2	Importing from Different Sources	Re-Using a Data Source	25
3.3	Importing from Excel		26
3.4	Importing CSV or Text Files		27
3.5	Importing from SQL Server	Using Queries and Stored Procedures	28
3.6	Importing from a Website		30
3.7	Importing a Power BI Dataset		31
3.8	Entering Data Manually	Pasting Data Typing in Data	32

Chapter 4 - DATA MODELS▼

Section	Title	Subsections	Page
4.1	Data Models	Viewing a Model Selecting Single Model Items Selecting Multiple Items Searching for Fields	34
4.2	Model Diagrams	Arranging Tables in a Model Diagram Layouts Collapsing and Expanding Tables Controlling Expand/Collapse Field Visibility Seeing Table Information Hiding Objects in the Report View	36
4.3	Model Properties	Table Properties Display Folders Default Number and Date Formatting Changing the Default Aggregation for a Field	39
4.4	Relationships	The Need for Relationships Parent-Child Relationships Creating a Relationship Editing Relationships The Effect of Relationships Cross-Filter Direction	41

Chapter 5 - VISUALISATIONS▼

Section	Title	Subsections	Page
5.1	Overview of Visualisations		44
5.2	Working with Visualisations	Adding a Visual The Visual Editing Panes 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 Customising the Visuals Pane	45
5.3	The Selection Pane	Changing the Visual Layer Order Changing the Tab Order Hiding Visuals Renaming a Visual	50
5.4	Visual Interactions	Editing Interactions Visual Interaction Settings	52

Chapter 6 - TABLES▼

Section	Title	Subsections	Page
6.1	Basic Tables		54
6.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	55
6.3	Aggregating Data		57
6.4	Formatting Numbers	Formatting Numbers within a Single Table Formatting Numbers for all Visuals (Method 1) Formatting Numbers for all Visuals (Method 2) Setting Custom Number Formats	58
6.5	Formatting Dates	Displaying Dates as Dates Formatting Dates (Method 1) Formatting Dates (Method 2)	60
6.6	Formatting Visuals – General	Visual Borders, Shadow and Background Titles	62
6.7	Specific to Formatting Tables	Column Headers Totals Font Size and Typeface Table Padding and Gridlines Table Styles Formatting Columns Individually	63
6.8	The Wonderful Format Painter		66
6.9	Images and URLs in Tables	Showing Images URLs in Tables	67

Chapter 7 - CONDITIONAL FORMATTING▼

Section	Title	Subsections	Page
7.1	Conditional Formatting		69
7.2	Applying Conditional Formatting to Fields	Applying Conditional Formatting through the Field Well Applying Conditional Formatting through the Format Pane	70
7.3	Gradient Effects	Adding a Middle Colour	71
7.4	Rules-Based Conditional Formatting		72
7.5	Data Bars		73
7.6	Formatting Using Field Values	Using SWITCH to Generate Colours	74
7.7	Displaying Icons		75

Chapter 8 - MATRICES▼

Section	Title	Subsections	Page
8.1	Overview of Matrices		76
8.2	Creating a Matrix	Sorting in a Matrix	77
8.3	Multiple Rows, Columns and Values	Disabling or Editing Stepped Layout	78

Chapter 9 - TEXT BOXES, IMAGES AND SHAPES▼

Section	Title	Subsections	Page
9.1	Non-Data Visualisations	Drawing Text Boxes, Images or Shapes	79
9.2	Images	Scaling Images Adding a Hyperlink to an Image	80
9.3	Text Boxes	Inserting Values	81
9.4	Shapes	Adding a Shape	82

Chapter 10 - CHART BASICS▼

Section	Title	Subsections	Page
10.1	The Parts of a Chart		83
10.2	Types of Chart Available		84
10.3	Creating a Chart	Creating a New Chart Turning a Table into a Chart	85
10.4	Sorting Charts		86
10.5	Formatting Charts	Legends and Series Colours Detail Labels Total Labels Plot Area and Background Gridlines Axes Conditional Formatting	87
10.6	Excluding and Including Data		93
10.7	Zoom Sliders		94

Chapter 11 - SMALL MULTIPLES▼

Section	Title	Subsections	Page
11.1	What Makes a Good Small Multiple?		95
11.2	Creating Small Multiples	Setting Grid Width and Height Formatting Small Multiple Titles Formatting Small Multiple Charts	96

Chapter 12 - MORE ON CHARTS▼

Section	Title	Subsections	Page
12.1	Column and Bar Charts	Continuous and Categorical Axes Category Width and Padding	99
12.2	Line Charts	Line Formatting Dual Axis Line Charts	101
12.3	Combined Charts	Formatting the Secondary Axis	102
12.4	Waterfall Charts	Showing Breakdowns	104
12.5	Scatter and Bubble Charts	Animating Bubble Charts	105
12.6	Other Chart Types	Area Charts Ribbon Charts Pie and Donut Charts Treemap Charts Funnel Charts	107

Chapter 13 - GROUPING AND BINNING▼

Section	Title	Subsections	Page
13.1	Grouping	Starting a Group Editing Groups Using Group Fields	110
13.2	Binning		112

Chapter 14 - DRILL-DOWN▼

Section	Title	Subsections	Page
14.1	What is Drill-Down?	Enabling Drill Down	113
14.2	Using Drill-Down	Drilling Down and Up Drill Down and Visual Interactions Viewing the Next Hierarchy Level Expanding All Levels in a Hierarchy	114
14.3	Drill-Down in a Matrix	Choosing Row or Column Fields	117
14.4	Showing Data Tables		118

Chapter 15 - VISUALS FOR DASHBOARDS▼

Section	Title	Subsections	Page
15.1	Gauges	Minimum, Maximum and Target Values Formatting Gauges	119
15.2	KPIs	Creating Targets Colour Coding Targets Formatting the Numbers Displayed	121
15.3	Cards		123

Chapter 16 - SLICERS▼

Section	Title	Subsections	Page
16.1	Slicer Basics	Creating a Slicer Selecting and Clearing Items Changing Selection Behaviour Searching in Slicers Dropdown Slicers Slicer Orientation Formatting Slicers Customising your Slicer Header Images in Slicers Slicer Interactions	124
16.2	Number and Date Slicers	Sliders Choosing Dates Picking Relative Dates Changing the Anchor Date	129
16.3	Sync Slicers		131
16.4	Hierarchical Slicers		132

Chapter 17 - FILTERING REPORTS▼

Section	Title	Subsections	Page
17.1	How Filters Work		133
17.2	The Filters Pane		134
17.3	Applying and Removing Filters	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	135
17.4	Sorting Filters	Manually Arranging Filters Sorting Filters	138
17.5	Formatting Filters		139
17.6	Controlling Filters for End Users	Locking and Hiding Filters Report Filter Settings	140

Chapter 18 - DRILL-THROUGH FILTERS▼

Section	Title	Subsections	Page
18.1	What are Drill-Through Filters?		141
18.2	Creating a Drillthrough Filter	Step 1 – Create the Main Report Page Step 2 – Create the Drillthrough Target Page Step 3 – Create the Drillthrough Filter Step 4 – Use the Drillthrough Feature	142
18.3	Extra Drillthrough Options	Keeping All Filters Drillthrough from Summarised Fields	144
18.4	Buttons for Drill-Through		145

Chapter 19 - BUTTONS AND SHAPES▼

Section	Title	Subsections	Page
19.1	Overview	Types of Clickable Objects Types of Action	146
19.2	Adding Clickable Shapes		147
19.3	Adding Clickable Images		148
19.4	Adding Clickable Buttons	Adding the Button Setting Default, Hover, Selection and Disabled Effects Formatting Buttons	149

Chapter 20 - PAGE NAVIGATION▼

Section	Title	Subsections	Page
20.1	Overview		151
20.2	Page Navigators	Formatting Page Navigators	152
20.3	Simple Custom Page Navigators		153
20.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 Stage 5 – Setting the Tooltip	154

Chapter 21 - CUSTOM VISUALS▼

Section	Title	Subsections	Page
21.1	What are Custom Visuals?		157
21.2	Adding Custom Visuals		158
21.3	Using a Custom Visual		159
21.4	Removing Custom Visuals	Removing Custom Visuals Pinned to the Visualisations Pane	160

Chapter 22 - QUERYING DATA▼

Section	Title	Subsections	Page
22.1	What are Queries?		161
22.2	Working with Queries	Opening the Query Editor The Power Query Editor Default Query Steps Viewing Data at Different Steps Editing a Query Step Deleting a Query Step Adding a New Step Applying Query Changes The Advanced Editor	162
22.3	Transforming Data	Changing Data Types Renaming Columns Removing Columns Removing Rows Sorting and Filtering Rows Splitting Columns Extracting Values Replacing Values Replacing Errors Duplicating Columns	167
22.4	Creating New Columns	Creating a Formula Creating a Column by Example	171
22.5	Conditional Columns		173

Chapter 23 - MAPS▼

Section	Title	Subsections	Page
23.1	Overview of Maps		174
23.2	Choosing a Mapping Tool	Installing Preview Map Features	175
23.3	Creating Maps	Assigning a Location Bubble Size Conditional Formatting Using Legend Fields Heat Maps Map Styles	176
23.4	Geocoding	Categorising Geographic Data Adding a Country Code	179
23.5	Latitude and Longitude	Converting Postcodes Cleaning the Imported Data Configure the Data Model Assigning Fields to a Map	180
23.6	Drilldown in Maps	Controlling Map Zoom	182

Chapter 24 - PUBLISHING▼

Section	Title	Subsections	Page
24.1	Overview of Publishing		183
24.2	An Infinite Number of Variables	Your Power BI Licence Linking to Data Data Sources	184
24.3	Logging on to Power BI Service		185
24.4	Creating Workspaces		186
24.5	Publishing a Report		187
24.6	Viewing and Editing Reports	Reports and Datasets Lineage View Viewing Individual Reports Editing a Report	188
24.7	Dashboards and Tiles	Adding Tiles to Dashboards Listing Dashboards Viewing Dashboards	191
24.8	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	192

Chapter 25 - CALCULATED COLUMNS▼

Section	Title	Subsections	Page
25.1	Introduction to Calculated Columns		196
25.2	Creating Calculated Columns	Starting a New Column Typing a Formula Hints for Editing DAX DAX Editors	197
25.3	Conditional Functions	The IF Function Operators in DAX The SWITCH Function	199
25.4	The RELATED Function		201
25.5	Blanks	Testing for Blanks Creating Blanks Blank Arithmetic	202
25.6	Testing for Errors		203

Chapter 26 - DAX STUDIO▼

Section	Title	Subsections	Page
26.1	Using DAX Studio	Installing DAX Studio Connecting to your Data Model	204
26.2	Five Uses of DAX Studio	Use 1 - Getting at DAX Functions Use 2 – Writing DAX Queries Use 3 – Better Formatting Use 4 – Saving DAX Use 5 – Getting at Internal Data	205

Chapter 27 - MEASURES▼

Section	Title	Subsections	Page
27.1	Introduction to Measures	What Measures Are Examples of Measures	207
27.2	Creating a Measures Table		208
27.3	Creating Measures		209
27.4	Quick Measures	Starting a Quick Measure Creating the Base Value Setting any Filtering Using a Quick Measure	210
27.5	DAX Aggregation Functions		213
27.6	Aggregating Expressions	The Problem Why the Simple Solution Won’t Work The Answer – X-Suffix Functions Syntax of AggregateX Functions	214
27.7	Calculating Ratios	Counting Rows using the COUNTROWS Function Creating Ratios: the Fields Needed The Final Matrix Summing Ratios Wouldn’t Work	216

Chapter 28 - FILTER CONTEXT▼

Section	Title	Subsections	Page
28.1	Our Simple Example		218
28.2	How Filter Context Works	What We’re Working Towards Step 1 – Assembling the Data Step 2 – Working out the Filter Context Step 3 – Getting the Filtered Data for the Context Step 4 – Aggregating the Data	219

Chapter 29 - ROW CONTEXT▼

Section	Title	Subsections	Page
29.1	Row Context for Calculated Columns		223
29.2	Iterator Functions	Normal Aggregate Functions Use Filter Context Iterator Functions Use Row Context	224

Chapter 30 - THE CALCULATE FUNCTION▼

Section	Title	Subsections	Page
30.1	Syntax of the CALCULATE Function		226
30.2	Removing a Constraint	Our Example A Quick Note on Ratios The Formula for this Example How this Works	226
30.3	Removing Multiple Constraints	Using Multiple ALL Functions Using ALLEXCEPT	229
30.4	Replacing a Constraint	Filter Context Revisited – Column Storage How Replacing Filter Context Really Works	231
30.5	Using ALLSELECTED		233
30.6	Context Transition		234

Chapter 31 - VARIABLES▼

Section	Title	Page
31.1	Referring to Measures within Measures	235
31.2	Creating Variables	236
31.3	Lazy Evaluation and its Implications	237
31.4	Storing Tables in Variables	238
31.5	Debugging using Variables	239

Chapter 32 - THE FILTER FUNCTION▼

Section	Title	Subsections	Page
32.1	The Basic FILTER Function	Using CALCULATE as an Alternative to FILTER	240
32.2	FILTER as an Iterator Function	Starting Off – Our Example Getting the Filter Context Row Context within this Filter Context Deriving the Final Result	241
32.3	Multiple Conditions in FILTER Functions	Combining Conditions using && and \|\| Combining Conditions using AND / OR Combining Conditions by Nesting the FILTER Function	243
32.4	Using ALL and FILTER		245
32.5	FILTER and CALCULATE aren’t Equivalent		245

Chapter 33 - THE VALUES FUNCTION▼

Section	Title	Subsections	Page
33.1	Introducing the VALUES Function		247
33.2	Detecting the Number of Values	The HASONEVALUE Function Using COUNTROWS to Count VALUES	248
33.3	Using VALUES to Modify Filter Context	The Obvious Way doesn’t Work Using the VALUES Function to Solve the Problem	250
33.4	Parameter Tables		251
33.5	Dynamic Titles using ISFILTERED	Dynamic Titles for Single-Value Filters Dynamic Titles for Multi-Value Filters	252

Chapter 34 - CALENDAR TABLES▼

Section	Title	Subsections	Page
34.1	What are Calendar Tables?	Requirements for a Calendar Table Why you Need a Calendar Table	254
34.2	Creating a Calendar	Step 1 – Getting the Calendar Data Step 2 – Loading and Linking to the Calendar Table Step 3 – Mark your Table as a Date Table Step 4 – Setting the Year as Text Step 5 - Setting a Sort Month	255
34.3	Date Granularity		258
34.4	Special Days		259

Chapter 35 - MULTIPLE DATE TABLES▼

Section	Title	Subsections	Page
35.1	The Problem, and Two Solutions	Repeat the Table or the Relationship?	260
35.2	Solution One: Duplicate the Calendar Table	Step 1 - Importing and Linking to the Calendar Tables Step 2 – Renaming Tables and Fields Step 3 – Using your Multiple Calendars	261
35.3	Solution Two: Duplicate the Relationship	Creating the Duplicate Relationships Interlude - The CALCULATETABLE Function The USERELATIONSHIP Function Our Measures	263
35.4	CROSSFILTER Function	One Solution – Change the Relationships Permanently A Better Solution – Use DAX to Temporarily Cross-Filter Multiple Cross-Filtering	266

Chapter 36 - DAX DATE FUNCTIONS▼

Section	Title	Subsections	Page
36.1	Contents of the Chapter		268
36.2	Period to Date	Using DATESYTD, DATESQTD and DATESMTD Using TOTALYTD, TOTALQTD and TOTALMTD	269
36.3	Changing the Financial Year End	Functions with a Year End Date Argument Displaying Data for Different Financial Year Ends	270
36.4	Referencing Previous Periods	The SAMEPERIODLASTYEAR Function The DATEADD Function	272
36.5	Parallel Periods		273
36.6	Moving Averages	Definition of a Moving Average Moving Average using DATESINPERIOD and LASTDATE Moving Average using DATESBETWEEN, NEXTDATE and LASTDATE	274
36.7	Semi-Additive Measures	Useful Semi-Additive Functions Using the FIRSTDATE and LASTDATE Functions Using FIRSTNONBLANK and LASTNONBLANK Detecting Relationships in FIRSTNONBLANK / LASTNONBLANK	276

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

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

Choose software

Power BI course manuals