Power BI Tools courseware and training manuals

In addition to courseware for Power BI Desktop, we also have course manuals for PowerPivot (whether this still counts as a Power BI application we're not sure!):

You can see details of how to license our Power BI Tools courseware (including volume requirements) here.

Basic Power BI courseware

Here is a list of all of our Basic Power BI manual chapters (you can download the ones marked):

Chapter Sections Subsections
Power BI Desktop Basics (2 pages) What is Power BI Desktop? Downloading Power BI Desktop ; Starting Power BI Desktop ; Signing in to an account
Getting Started (11 pages) Example for this Chapter Showing Keyboard Shortcuts
Working with Files
The Power BI Desktop Screen
Getting Data Choosing the Table of Data to Import
Basic Querying Starting to Query Data ; Removing the Rating Column ; Globally Replacing Text in a Query ; Returning to your Table
Creating Visuals
Formatting Visuals Searching for a Format Property
Filtering your Data
Working with Reports Changing how you View Reports ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages
Importing tables (12 pages) Our Example
Importing from Different Sources Importing from Facebook ; Importing from Excel ; Importing from a CSV or Other Text File ; Importing from SQL Server ; Importing from a Website ; Pasting Data into a Model ; Loading data from Access ; Importing from Analysis Services
Typing in Data
Hiding Columns and Tables Hiding Columns ; Hiding Tables ; Showing or Unhiding All Hidden Columns/Tables
Visualisations (7 pages) Overview of Visualisations
Working with Visualisations Adding a Visualisation ; Editing Visualisations (Fields, Format or Analytics) ; Zooming In and Out (Focus Mode) ; Spotlighting a Visual ; Resizing a Visualisation ; Moving, Selecting and Deleting Visualisations ; Locking Visuals ; Aligning and Distributing Visualisations
Using the Selection Pane Displaying and Using the Selection Pane ; Hiding Visuals ; Renaming Visuals
Visual Interactions (2 pages) Visual Interactions How Visualisations Affect Each Other by Default ; Editing Interactions ; What the Different Settings Mean
Tables (17 pages) Basic Tables
Working with Table Columns Sorting Tables by Column ; Re-ordering Columns ; Changing Column Widths ; Changing Word Wrap Options ; Renaming Columns
Aggregating Data
Formatting Tables Formatting Numbers within a Single Table ; Formatting Numbers for All Tables ; Formatting Dates ; Font Size and Typeface ; Putting Borders around Tables ; Table Padding ; Putting Gridlines within Tables ; Formatting Columns Individually ; Table Styles ; Titles ; Totals
The Wonderful Format Painter
Conditional Formatting (Colour Scales / Data Bars) Applying Conditional Formatting or Data Bars ; Choosing Colour Scales ; Divergent Colour Scales ; Configuring Data Bars ; Rules-Based Conditional Formatting
Images and URLs in Tables Showing Images ; URLs in Tables
Matrices (3 pages) Overview of Matrices
Creating a Matrix
Multiple Rows or Columns Customising or Disabling Stepped Layout
Text boxes, images and shapes (3 pages) Overview Our Example ; Adding Text Boxes, Images or Shapes ; Working with Images ; Working with Text Boxes ; Working with Shapes ; Bringing to Front/Sending to Back
Charts (general) (10 pages) The Parts of a Chart
Types of Chart Available
Creating a Chart Creating a Chart from Scratch ; Turning a Table into a Chart
Sorting Charts
Formatting Charts Legends and Series Colours ; Data Labels ; The Plot Area ; Axes ; Tooltips ; Colour Saturation
Excluding and Including Data
Charts (specific) (13 pages) Column and Bar Charts Showing All Axis Titles (Minimum Category Width)
Line Charts Line Scaling, Widths and Markers ; Superimposing Trendlines and Statistics
Area Charts
Ribbon Charts
Pie and Donut Charts Showing Percentages of Totals
Treemap Charts
Combined Charts Controlling the Secondary Axis
Funnel Charts
Waterfall Charts Setting Breakdowns
Scatter and Bubble Charts Creating Scatter and Bubble Charts ; Properties Unique to Scatter Charts ; Data (Details) Fields ; Playing Bubble Charts
Custom Visuals (3 pages) What are Custom Visuals?
Adding Custom Visuals Deleting a Custom Visual
Showing Single Figures (4 pages)

Download this chapter 		Gauges Components of a Gauge ; Gauge Fields ; Formatting Gauges
KPIs
Cards
Multi-Row Cards (1 page) Creating a Multi-Row Card
Querying Data (13 pages) What Queries Do
Working with Queries Creating Queries ; Saving/Closing a Query ; Deleting Parts of a Query ; Deleting All Subsequent Steps ; Inserting Steps ; Changing what a Step Does ; Grouping Queries
Common Query Transforms The Source and Navigation Steps, and Other Automatic Steps ; Filtering Rows ; Sorting Rows ; Deleting Columns ; Moving Columns ; Splitting Columns by Delimiter ; Splitting Columns by Number of Characters ; Replacing Text
Adding Columns Inserting a Column using a Formula ; Using Column by Example
Conditional Columns
Relationships (7 pages) Relationships
Browsing Relationships Resetting Layout ; Fitting to Screen ; Maximising a Table
Creating Relationships The Need for Relationships ; Parent-Child Relationships ; Creating a Relationship ; Editing Relationships ; The Effect of Relationships ; Cross-Filter Direction
Using Relationships in Tables Our Example ; Showing Columns from Parent Tables ; Showing Columns from Child Tables
Combining Tables (6 pages) Merging Rows Using UNION to Combine Tables ; Appending Rows from One Table to Another
Merging Tables (Joins) Starting a Merge Query ; Choosing a Join Type ; Displaying Columns from the Joined Table
Combining Workbooks when Importing Step 1 – Getting All the Files in a Folder ; Step 2 – Combining the Files
Slicers (8 pages) Slicer Basics Adding Slicers ; Selecting and Clearing in Slicers ; Changing Selection Behaviour ; Searching in Slicers ; Cascading Slicers ; Images in Slicers ; Formatting Slicers ; Making Slicers Drop Down ; Horizontal Slicers
Synchronised Slicers
Date Slicers Relative Date Slicers ; Changing the Anchor Date
Maps (10 pages) Overview of Maps
Creating Maps Colour Saturation ; Category Labels ; Bubble Sizes
Geocoding Avoiding Problems 1: Choosing a Field Category ; Avoiding Problems 2: Forcing Localisation
Latitude and Longitude Getting Latitude and Longitude from Postcodes ; Cleansing the Converted Data ; Creating a Relationship ; Creating the Near-Final Map ; The Last Step: Mending the Relationship
Filled Maps (Choropleths)
Shape Maps Enabling Shape Maps ; Using Shape Maps
ArcGIS Maps (7 pages) Creating ArcGIS Maps Editing ArcGIS Maps
Formatting Maps Changing the Base Map ; Choosing a Theme ; Symbol Appearance
Pins
Showing Drive Times
Reference Layers
Filtering reports (4 pages) How Filters Work Three Levels of Filtering
Adding and Removing Filters Removing Filters
Other Filter Options Combining Filters (Advanced) ; Relative Date Filtering ; Top and Bottom Filters
Drill-through Filters (3 pages) What Drill-Through Filters Do
Creating the Filtered Report Pages Creating the Report Pages ; Applying a Drill-Through Filter
Using a Drill-Through Filter
Binning and grouping (4 pages) Grouping Starting a Group ; Allocating Items to Members of the Group ; Using Groups you’ve Created
Binning
Hierarchies (2 pages) Using Hierarchies
Creating Hierarchies Deleting Hierarchies
Drill-down (6 pages) Overview of Drill-Down The Pre-Requisite for Drill-Down to Work: Multiple Axis Fields
Using Drill-Down Turning Drill-Down On and Off ; Drilling Down ; Drilling Back Up ; Special Considerations for Matrices ; Going to the Next Hierarchy Level ; Expanding All Levels in a Hierarchy
Showing Data
Listing Records
Templates (1 page) Creating Templates Creating Templates ; Using Templates
New Columns (8 pages) Overview of New Columns Reasons to Use New Columns ; DAX
Creating New Columns Starting a New Column ; Typing in a Formula ; Hints for Editing DAX ; DAX Editors ; Using AND and OR in DAX Conditions
Using IF and SWITCH The IF Function ; The SWITCH Function
The RELATED Function
Blanks Blank Arithmetic
Testing for Errors (IFERROR)
Basic Measures (5 pages)

Download this chapter 		Measures Always Involve Aggregation
Creating Measures Starting your Measure ; Creating and Formatting a Measure Formula ; Using your Measure in a Visualisation or Filter ; The Importance of Giving DAX what it Asks for
DAX Aggregation Functions Aggregating Expressions
Measures Referring to Measures
Changing Filter Context (6 pages) Filter Context Revisited
The CALCULATE Function Syntax of the CALCULATE Function ; Removing Filters ; Removing Multiple Filters ; Removing All Filters Except One ; Replacing one filter ; Replacing Multiple Filters
Filter Function Using Calculate instead of Filter
Building on the Filter Context (VALUES) Without the VALUES Function ; With the VALUES Function
Advanced DAX (3 pages) Variables
The EARLIER Function Our Two Worked Examples ; What the EARLIER Function Does ; Example: Number of Purchases on the Same Date ; Example: Ordering Purchases by Date
Calendars and Dates (8 pages) What Calendars Are, and Why to Use Them
Calendar Tables within Power BI Desktop Calendars using the M Language ; Renaming the Date Column ; Creating New Columns
Importing Calendar Tables Creating a Calendar Table in Excel ; Creating a Calendar Table in SQL Server
Using Calendar Tables Simple Relationships ; Sorting Months ; Multiple Table Relationships
Case Study: Working Days
Date Formulae (8 pages) Cumulative to Date The TOTALYTD, TOTALQTD and TOTALMTD Functions ; DATESYTD, DATESQTD, DATESMTD Functions
Comparisons with Previous Years The SAMEPERIODLASTYEAR Function ; The DATEADD Function
Parallel Periods
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Moving Averages
Power BI Service (4 pages) Introduction to Power BI Service
Publishing Reports from PB Desktop
Reports within Power BI Service Creating Reports ; Editing Reports ; Filtering Reports ; Another Way to View Items in Power BI Service
Dashboards and tiles (9 pages) Overview of Dashboards
Working with Dashboards Creating a Dashboard ; Editing and Deleting Dashboards ; Setting a Featured Dashboard ; Refreshing Dashboards
Working with Tiles Adding Tiles ; Moving and Resizing Tiles ; Changing Dashboard Tile Flow ; Exporting Data for a Tile ; Viewing the Report or Dataset for a Tile ; Changing a Tile’s Title and Subtitle ; Changing what Happens when you Click on Tiles
Live Pages
Adding Tiles Directly Examples of Each Added Tile Type
Refreshing Data (6 pages) What Needs Refreshing
On-Premises Data Gateways Types of Gateway ; What Constitutes a Gateway
Installing and Configuring Gateways Step 1 - Getting Gateway Software ; Step 2 – Running the Installation Software ; Step 3 – Signing In ; Step 4 – Assigning Datasets to Gateways
Refreshing Data Manually Refreshing Data ; Scheduled Refreshing ; Viewing Refresh History ; Scheduled Refreshes
Sharing (7 pages) Ways to Share
Publishing an App Step 1 - Creating an App Workspace ; Step 2 – Publishing to the App Workspace ; Step 3 – Add Dashboards ; Step 4 – Start to Publish your App ; Step 5 – Configure your App ; Step 6 - Getting Access to a Published App
Sharing Dashboards Subscribing to Dashboards
Sharing Reports Saving Reports (PDF and PowerPoint)

Power BI for Excel courseware

Here is a list of all of our Power BI for Excel manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introduction to PowerPivot (2 pages) The Course Database
The Power BI Components
Creating Data Models (12 pages) Going into PowerPivot
Importing Tables Step 1 – Connecting to the Database ; Step 2 – Choosing Data Tables ; Step 3 – Importing the Data ; Step 4 – Saving your Work (the xVelocity Engine)
Importing More Tables using an Existing Connection
Filtering Rows and Columns on Import Ignoring Columns ; Filtering Rows
Data and Diagram View Zooming In and Out in Diagram View
Hiding Columns and Tables from Client Views Hiding Columns ; Hiding Tables
Renaming Tables and Columns Renaming Columns ; Renaming Tables
Changing what you’ve Imported for a Table
Formatting Table Columns
Relationships (3 pages) Viewing Existing Relationships
Creating Relationships Creating Relationships in Diagram View ; Creating Relationships Manually
Pivot Tables using PowerPivot (16 pages) Creating the Data Model
Creating a Pivot Table Step 1 – Starting the Pivot Table ; Step 2 – Choosing Fields
Drill Down and Quick Explore Drill Down ; Deleting drill down results ; The Quick Explore Option
Working with Pivot Tables Renaming Fields ; Changing how Values are Calculated ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Filtering
Slicers and Timelines Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer ; Inserting Timelines ; Controlling Multiple Pivot Tables with Slicers/Timelines
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
Using Excel Workbooks (4 pages)

Download this chapter 		Linking to Excel Tables Creating Tables ; Naming a Table ; Listing Tables ; Linking to Tables
Importing Excel Workbooks
Links to Other Data Sources (5 pages) Formats Supported
Linking to Access
Importing Cubes
Importing from the Clipboard Creating a New Table from the Clipboard ; Appending Existing Data from the Clipboard
Importing from a CSV File
Getting Data (8 pages) A Potted History of Get & Transform
What Get & Transform Does
Getting Data Step 1 - Creating the Query ; Step 2 – Load or Edit?
Managing Queries
Linking to a Website
Linking to an XML File
Other Data Sources
Transforming Data (12 pages) Editing a Query The Query Editor
Editing Query Steps The Hard Way – the M Language ; The Easy Way – Query Steps
Adding Simple Transforms Removing Columns ; Reordering Columns ; Filtering Rows ; Sorting Columns
Splitting Columns, etc. – a Case Study Step 1 – Splitting by Number of Characters ; Step 2 – Splitting by Delimiter ; Step 3 – Replacing Values ; Step 4 – Changing Data Types ; Step 5 - Merging Columns ; Step 6 – Inserting a Start Date Column ; Step 7 – Getting the Month Number ; Step 8 – Aggregating the Data ; Step 9 – Renaming Query Steps ; Step 10 – Running your Query
Managing your Queries Renaming Queries ; Grouping Queries
Recent Data Sources
Calculated Columns (7 pages) What are Calculated Columns? Differences between Calculated Columns and Excel Formulae
Creating Calculated Columns An Alternative Method, using the Keyboard
Working with Calculated Columns Moving Calculated Columns ; Sorting by Calculated Columns
The PowerPivot Function Wizard
Using RELATED to Link Tables
The BLANK Function Blank Arithmetic
Aggregator Columns and SWITCH Using SWITCH
Measures (10 pages) Query Context
Implicit Measures Viewing Implicit Measures
Using AutoSum Using an AutoSum Measure in a Pivot Table
Creating Measures in Excel Step 1 - Starting to Create the Formula ; Step 2 – Choosing the Table ; Step 3 – Completing the Dialog Box
Step 4 – Checking your Formula Step 5 – Using your Formula
Creating Measures in PowerPivot Step 1 – Choosing where to Start ; Step 2 – Creating the Formula ; Step 3 – Renaming the Measure ; Step 4 – Using the Measure
Measures vs. Columns – the Differences Difference 1 - Measures Aggregate ; Difference 2 - Use in Pivot Tables ; Difference 3 - Key Performance Indicators (KPIs)
Basic DAX Formulae (6 pages) Aggregation Functions
X-Suffix Functions Simple Aggregate Functions don’t Work with Expressions ; Aggregate Functions with X Suffix ; Our Example SUMX Function
Calculating Ratios Counting Rows ; Creating Ratios: Measures Needed ; The Final Pivot Table ; Summing Ratios doesn’t Work
DAX Variables
The CALCULATE Function (4 pages) Syntax of the CALCULATE Function
Replacing Filters Replacing a Single Filter ; Replacing More than One Filter
Removing Filters Removing a Single Filter ; Removing Multiple Filters
Using VALUES to Edit a Constraint On its own, VALUES is pointless ; Amending the Query Context Filter
The FILTER Function (3 pages) Syntax of the FILTER Function
Filtering to Show Subsets of Data
Removing Filters using ALL Removing and Partially Reinstating Filters
Ranking (3 pages) The RANKX Function Syntax of the Rank Function ; Entering RANKX in a Calculated Column
Using RANKX in Measures Step 1 – Creating an Intermediate Measure ; Step 2 – using ALL ; Changing the Sort Order
The EARLIER Function (2 pages) Case Study: Ranking using EARLIER
Case Study: Group Statistics
Calendars (13 pages) The Need for a Separate Date Table The Excel Approach – Date Functions ; The PowerPivot Approach – Create a Separate Table
Creating a Calendar Table Creating a Calendar in Excel ; Creating a Calendar Table in SQL Server ; Creating a Calendar Automatically
Using your Calendar Change 1 – Mark your Calendar as a Date Table ; Change 2 - Changing Column Data Types ; Change 3 - Creating Relationships
Creating Pivot Tables using Dates Sorting Months Correctly ; Sorting Months by Financial Year
Multiple Date Tables Solution 1 – Multiple Calendars ; Solution 2 – Multiple Relationships
Worked Example – Working Days Calculation Step 1 – Create a Bank Holiday Table ; Step 2 – Create a Relationship for the Bank Holiday Table ; Creating a Calculated Column for Working Days
Date Functions (9 pages) Formulae Covered The Underlying Principle
Period to Date The Year-to-Date Function Family ; Dates to Date Functions ; Fiscal Year Calculations
Comparing with Previous Periods The SAMEPERIODLASTYEAR Function ; Using DATEADD to Compare with any Previous Period
Parallel Periods Comparing with the Previous Year’s Year-to-Date
Moving Averages
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Hierarchies (3 pages) Using Hierarchies Advantages and Disadvantages of Hierarchies
Creating Hierarchies Step 1 – Getting the Correct Fields in a Single Table ; Step 2 – Creating the Hierarchy ; Step 3 – Including Fields in your Hierarchy ; Step 4 – Using a Hierarchy
KPIs (5 pages)

Download this chapter 		About KPIs The Three Parts of a KPI
Creating a KPI Step 1 – Create the Measures Needed ; Step 2 – Starting to Create the KPI ; Step 3 – Customising your KPI ; Step 4 – Displaying Parts of the KPI
Absolute Targets
Perspectives (2 pages) Overview of Perspectives What Perspectives Are ; Using Perspectives in PowerPivot
Creating and Editing Perspectives
Power View (4 pages) Enabling Power View
What is Power View?
Creating a Power View Visualisation
Why Not to Use Power View

Advanced Power BI courseware

Here is a list of all of our Advanced Power BI manual chapters (you can download the ones marked):

Chapter Sections Subsections
Advanced Query Editor (14 pages) Loading Multiple Files Setting the Folder ; Setting the Files ; Setting the Template for Import ; Tidying Up the Results ; How it Works
Appending Queries
Merging Data Loading the Tables to Merge ; Merging the Data ; Flattening the Combined Table ; Tidying the Final Table
Query Dependencies
Switching the Data Source using M Showing the Advanced Editor ; The Excel Query ; Creating a SQL Server Version of our Table ; Switching the Data Source ; Resolving Problems ; Viewing the Results
Unpivoting Data Loading the Original Data ; Unpivoting the Data ; Renaming your Columns
Dynamic Filtering without Parameters (8 pages) Our Aim
Creating the Slicers The Postcode Slicer ; The Date Slicer
Displaying a Dynamic Title Step 1 – Create a Table ; Step 2 – Creating the Date Bound Measures ; Step 3 – Creating the Title Measure ; Step 4 – Create a Card ; Step 5 – Coping with Multiple Slicer Choices ; Step 6 – A Quick Measure to Display Multiple Values
Dynamic Filtering using Parameters (9 pages)

Download this chapter 		Overview of Chapter
Working with Parameters Accessing Parameters ; Creating Parameters ; Basing Parameters on Lists ; Managing Parameters
Entering Values for Parameters Entering Parameter Values in Query Editor ; Entering Parameter Values in Power BI Desktop
Filtering using Parameters
Referencing Parameters in Power BI Step 1 – Loading Parameters into your Data Model ; Step 2 – Creating a Table to Hold Measures ; Step 3 – Creating the Title Measure ; Step 4 – Creating a Card to Display the Measure
Dropdown Parameters (3 pages) List Queries
Creating the List Query Step 1 – Create a Query Giving a Single Column of Data ; Step 2 – Make this a New Query
Creating the Dropdown Parameter
Parameters and Stored Procedures (4 pages) Overview
Loading Data from a Stored Procedure
Making Parameters Dynamic Creating the Parameters ; Referencing the Parameters in the Advanced Editor ; Editing Parameter Values
Dynamic Data Sources (6 pages) SQL Server Dynamic Connections First Enable Parameterisation of Data Sources ; Creating a Parameter ; Using this Parameter to Get Data ; Changing the Server or Database ; The Query Editor Step Created
Dynamic Connections to Excel Step 1 – Create a Connection (Get Data) ; Step 2 – Create a Parameter ; Step 3 – Parameterising the Connection Used ; Changing the Worksheet Used
Data Analytics (6 pages) Overview of Lines
Adding Lines
Trend Lines and Forecasts The Combine Series Option
Forecast Lines
Symmetry Shading and Ratio Lines Preparing your Data for Symmetry Shading ; Creating a Ratio Line and Symmetry Shading ; Post-Script on Log Scatter Charts
Custom Visuals (6 pages) Installing Custom Visuals
Chiclet Slicers Step 1 – Get the Images ; Step 2 – Ensure you have an Image URL ; Step 3 – Create your Chiclet Slicer ; Step 4 – Setting the Images ; Step 5 – Formatting the Slicer ; Step 6 – Testing your Slicer
The Play Axis Visual How it Works ; Setting the Play Axis Slicer Field ; Animation Settings ; Formatting the Caption
Tooltips (10 pages) Overview
Minimal Tooltips
Displaying Field Values Adding Additional Fields
Static Report Pages Step 1 – Create the Tooltip Page ; Step 2 – Setting the Page Size ; Step 3 – Hiding your Report ; Step 4 – Creating the Tooltip Report ; Step 5 – Assigning a Report Page to a Tooltip
Dynamic Report Page Tooltips Step 1 – Creating the Tooltip Page ; Step 2 – Creating the Report Page for the Tooltip ; Step 3 – Assigning Fields to the Tooltip Report Page ; Step 4 – Choosing When to Show your Tooltip ; Step 4 – Choosing whether to Keep Filters ; Step 5 – Assigning your Tooltip Report Page to a Visual
Manual Assignment
Quick Measures (7 pages) About Measures
Creating Normal Measures Step 1 - Creating a Measures Table ; Step 2 – Adding a Measure
Starting Quick Measures
Case Study 1 – London versus Regional Sales Choosing the Calculation Type and Base Value ; Specifying the Filtered Value ; Viewing and Tidying Up your Quick Measure
Case Study 2 – Listing Slicer Selections Creating the Slicer Selection Quick Measure ; Viewing the DAX Formulae ; Displaying the Slicer Selection
Converting Postcodes (4 pages) Overview of the Problem
Converting Postcodes to Latitude/Longitude
Getting at the Latitude/Longitude Data Loading the Latitude and Longitude Data into Power BI ; Removing Duplicate Postcodes and Creating a Relationship ; Combining Columns ; Showing Latitude and Longitude in Maps
MapBox Visual (8 pages) Introduction Getting the MapBox Custom Visual
Enabling your Map with a Token
Creating a Map Searching for a Place (Geocoding) ; Choosing the Map Style
Selecting Points Using the Lasso and Polygon
Different Types of Map Circle Maps ; Cluster Maps ; Heat Maps ; Switching Maps on Zoom
Shape Maps (7 pages) Introduction Enabling Shape Maps
Finding a Map Some Useful Terms ; Finding a topoJSON File
Creating the Shape Map Starting the Map ; Assigning a Map File ; Reviewing Map Keys ; Creating Map Keys in your Data ; Setting the Location Field ; Zoom Settings ; Setting a Tooltip ; Setting the Default Colour ; Setting Map Colours
Synoptic Panels and Maps (8 pages) Overview
Creating the Image Creating a Base Image ; Adding this Image in Synoptic Designer ; Filling In your Picture ; Naming the Areas ; Saving your Image
Creating the Visual Tying your Data to your Map ; Importing the Synoptic Panel Custom Visual ; Creating a Synoptic Panel
Formatting the Visual Colour Saturation ; Settig Colour Thresholds (“States”)
Buttons and Actions (4 pages) Overview Types of Button
Assigning Actions to Shapes or Images
Creating Buttons Adding Buttons ; Default, Hover and Selection Effects ; Formatting Buttons
Bookmarks (6 pages) Example of Bookmarks
Preparing to Use Bookmarks Naming your Visuals in the Selection Pane ; Displaying the Bookmarks Pane ; Creating Bookmarks
Configuring your Bookmarks Step 1 – Data Settings ; Step 2 – Display Settings ; Step 3 – Update the Bookmark ; Step 4 - Linking to your Bookmarks
Linking Back to the Home Page Creating the Home Bookmark
Uses of Bookmarks (13 pages) Ideas in this Chapter
Clearing Filters and Slicers Creating the Slicers and Action Button ; Clearing the Slicers ; Creating and Customising your Bookmark ; Attaching the Bookmark to your Action Button
Clicking on Pictures to Filter Charts Setting the Scene ; Setting the Bookmarks to Filter ; Attaching the Bookmarks to the Images
Sort Icons Creating the Buttons ; Create the Bookmarks ; Configure the Bookmarks
Tabbed Controls Creating the Visuals ; Creating the Tabbed Effect ; Creating the Bookmarks ; Attaching the Bookmarks to the Tabs
Moving between Pages Creating the Pages ; Synchronising the Home Page Slicer ; Creating the Bookmarks ; Assigning Actions
Pop-Up Buttons Creating the Pop-Up Message ; Naming the Parts of the Pop-Up Message ; Creating the Bookmarks ; Assigning Actions to the Bookmarks
Themes (4 pages) Applying Existing Themes
Using the Themes Gallery
Limitations of Themes Themes don’t Override Custom Colours ; Custom Visuals don’t Support Themes
Creating Custom Themes (8 pages) Overview
Understanding the Parts of a Theme Foreground and Background Colours ; The TableAccent Property ; The DataColors property
Understanding JSON JSON is just like XML ; Objects and Name/Value Pairs ; Arrays
Creating a JSON File Creating a New File ; Adding a Line ; Adding an Array ; Changing Colours ; Saving your Changes
Extending Custom Themes (6 pages) Syntax of Visual Styles CardName Values
Examples of Visual Styles Example: Setting the Default Font ; Example: Setting the Default Legend ; Example: Setting the Default Title ; Example: Pie Chart Labels ; Example: Setting a Border
This Courseware’s Theme
Templates (6 pages) Creating a Template
Using a Template
Ideas for Templates Themes ; Data ; Parameterised Data Connections ; Parameters for Filtering ; Corporate Logos ; Layouts
Power BI Mobile (8 pages)

Download this chapter 		What is Power BI Mobile?
Limitations of Mobile Reports Problems with Titles ; Portrait and Landscape Issues ; Standard Formatting across Report Types ; Grid Mesh Size
Creating Mobile Reports
Using Mobile Reports Using Drill-Down on Mobile Phones
Mobile Dashboards Designing Mobile Dashboards ; Using Mobile Dashboards
Question and Answer (3 pages) Q&A in Power BI Desktop
Q & A in Power BI Service
Synonyms
Analysing in Excel (5 pages) Pivoting Report Data
Preparation in Power BI Desktop Tidying your Data Model ; Creating a Measure Table ; Creating Measures
Preparation in Power BI Service
Analyzing in Excel
Row-Level Security (10 pages) Overview of Row-Level Security Limitations and Constraints
How Roles Work
Roles in Power BI Desktop Creating Roles ; Testing Roles
Roles in Power BI Service Configuring the App Workspace ; Assigning Users to Roles ; Testing your Roles
Dynamic Security Creating a Permissions Table ; Creating a Role
Creating Groups of Users Creating the Tables ; Linking the Tables ; Creating the Role

DAX courseware

Here is a list of all of our DAX manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introducing DAX (1 page) Where is DAX Used?
Creating a Data Model (6 pages) The Woozle Data Model The Geographical Tables ; The Product Category Tables ; Some Terms
Creating Reports in Power BI Desktop Loading the Data Tables ; Creating Visuals in Power BI
Creating Reports in PowerPivot Loading Tables into Data Models in PowerPivot ; Creating Pivot Tables in PowerPivot
Writing DAX (6 pages) Calculated Columns Referring to Columns/Fields ; Referring to Tables ; Fully Qualified References
Writing DAX Laying out your Formulae ; Using Multiple Lines ; DAX Formatter ; Comments
DAX Syntax Functions and Arguments ; Mathematical Operators ; Concatenating Text
DAX Studio (3 pages) Using DAX Studio Installing DAX Studio ; Connecting to your Data Model
Four Uses of DAX Studio Use 1 - Getting at DAX Functions ; Use 2 – Writing DAX Queries ; Use 3 – Better Formatting ; Use 4 – Saving DAX
Testing Conditions (3 pages)

Download this chapter 		Testing Single Conditions The IF Function ; Relational Operators ; Logical Operators ; Using IN to Test if Items Exist in a List
The SWITCH Function
Linking Tables (4 pages) The RELATED Function
Dealing with Blanks BLANK Arithmetic
The RELATEDTABLE Function
Working with Data Types (5 pages) DAX Data Types
Scalar Date Functions
Scalar Text Functions Finding and Replacing Text ; Converting Text ; Formatting Text ; Getting the Length of and Extracting Text
Scalar Number Functions
Trapping Errors (2 pages) Using the DIVIDE Function
Using IFERROR Generating your Own Errors using ERROR
Creating and Storing Measures (6 pages) Implicit Measures Recognising Implicit Measures ; Showing Implicit Measures in PowerPivot
Creating Measures Creating Measures in Power BI Desktop ; Creating Measures in PowerPivot
Using a Measures Table Creating a Measures Table in Power BI Desktop ; Creating a Measures Table in PowerPivot
Aggregating Data (6 pages) Automatically Aggregating Data Choosing Automatic Measures in PowerPivot ; Changing Automatic Measures in Power BI
DAX Aggregation Functions
Aggregating Expressions The Problem ; Why the Simple Solution Won’t Work ; The Answer – X-Suffix Functions ; Syntax of AggregateX Functions
Calculating Ratios Counting Rows using the COUNTROWS Function ; Creating Ratios: the Fields Needed ; The Final Pivot Table ; Summing Ratios Wouldn’t Work
Filter Context (5 pages) Our Simple Example
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
Row Context (3 pages) Row Context for Calculated Columns
Iterator Functions Normal Aggregate Functions Use Filter Context ; Iterator Functions Use Row Context
The CALCULATE Function (9 pages) Syntax of the CALCULATE Function
Removing a Constraint Our Example ; A Quick Note on Ratios ; The Formula for this Example ; How this Works
Removing Multiple Constraints Using Multiple ALL Functions ; Using ALLEXCEPT
Replacing a Constraint Filter Context Revisited – Column Storage ; How Replacing Filter Context Really Works
Using ALLSELECTED
Context Transition
The VALUES Function (7 pages) Introducing the VALUES Function
Detecting the Number of Values The HASONEVALUE Function ; Using COUNTROWS to Count VALUES
Using VALUES to Modify Filter Context The Obvious Way doesn’t Work ; Using the VALUES Function to Solve the Problem
Parameter Tables
Dynamic Titles using ISFILTERED Dynamic Titles for Single-Value Filters ; Dynamic Titles for Multi-Value Filters
Variables (5 pages) Referring to Measures within Measures
Creating Variables
Lazy Evaluation and its Implications
Storing Tables in Variables
Debugging using Variables
The FILTER Function (7 pages) The Basic FILTER Function Using CALCULATE as an Alternative to FILTER
FILTER as an Iterator Function Starting Off – Our Example ; Getting the Filter Context ; Row Context within this Filter Context ; Deriving the Final Result
Multiple Conditions in FILTER Functions Combining Conditions using && and || ; Combining Conditions using AND / OR ; Combining Conditions by Nesting the FILTER Function
Using ALL and FILTER
FILTER and CALCULATE aren’t Equivalent
The EARLIER Function (5 pages) Case Study of the EARLIER Function Our Example ; An Outline of the EARLIER Function ; Row Context within Filter Context ; The Final Formula
Another Example – Running Totals
Using Variables instead of the EARLIER Function Ranking Sales using Variables ; Running Totals using Variables
Banding (3 pages) What is Banding? Creating and Loading a Banding Table
Creating a Banding Formula
Sorting the Bands
Ranking (5 pages) The RANKX Function Syntax of the Rank Function ; Intellisense for the RANKX Function
RANKX for Calculated Columns
Ranking Measures (Existing Columns) The Most Common Problem – Omitting ALL ; The Solution using ALL
Ranking using Aggregate Calculations RANKX is an Iterator Function
Ranking with Context Suppressing Totals ; Ranking over Selected Items
Calendar Tables (6 pages) What are Calendar Tables? Requirements for a Calendar Table ; Why you Need a Calendar Table
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 a Sort Month
Date Granularity
Special Days
Multiple Date Tables (8 pages)

Download this chapter 		The Problem, and Two Solutions Repeat the Table or the Relationship?
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
Solution Two: Duplicate the Relationship Creating the Duplicate Relationships ; The CALCULATETABLE Function ; The USERELATIONSHIP Function ; Our Measures
The CROSSFILTER Function One Solution – Change the Relationships Permanently ; A Better Solution – Use DAX to Temporarily Cross-Filter ; Multiple Cross-Filtering
How Time Intelligence Functions Work (4 pages) Our Example
Filter Context Reminder
Year-to-Date using CALCULATE
Year-to-Date using Time-Intelligence Functions The DATESYTD Function ; The TOTALYTD Function
DAX Date Functions (10 pages) Contents of the Chapter
Period to Date Using DATESYTD, DATESQTD and DATESMTD ; Using TOTALYTD, TOTALQTD and TOTALMTD
Changing the Financial Year End Functions with a Year End Date Argument ; Displaying Data for Different Financial Year Ends
Referencing Previous Periods The SAMEPERIODLASTYEAR Function ; The DATEADD Function
Parallel Periods
Moving Averages Definition of a Moving Average ; Moving Average using DATESINPERIOD and LASTDATE ; Moving Average using DATESBETWEEN, NEXTDATE and LASTDATE
Semi-Additive Measures Useful Semi-Additive Functions ; Using the FIRSTDATE and LASTDATE Functions ; Using FIRSTNONBLANK and LASTNONBLANK ; Detecting Relationships in FIRSTNONBLANK / LASTNONBLANK
Hierarchies (7 pages) Calculating Percentages The Problem ; A Solution
Parent-Child Hierarchies What is a Parent-Child Hierarchy? ; Step 1 – Create a List of Parent Ids (the PATH Function) ; Step 2 – Working out the Path Depth (the PATHLENGTH Function) ; Step 3 – Create a Measure Showing the Number of Levels ; Step 4 - Finding Managers at Each Level (PATHITEM and LOOKUPVALUE) ; Step 5 – Creating a Hierarchy ; Step 6 – Checking your Relationships ; Step 7 – Dealing with Blanks
Introducing DAX Queries (5 pages) Overview of DAX Queries
DAX Queries are like SQL
Where to Write DAX Queries
Debugging using DAX Queries A Worked Example ; Debugging using Table Variables ; Using Queries to Debug
Syntax of DAX Queries (12 pages) Listing Tables (EVALUATE) Listing All Rows ; Ordering Rows ; Starting at a Particular Row ; Returning N Rows Only ; Sampling Rows
Summarising Summarisation Syntax ; Example of Summarising by a Single Column ; Example of Summarizing by Multiple Columns ; Using SUMMARIZECOLUMNS ; Using ROLLUP to get All Combinations
Filtering in DAX Queries Example: Summarising Sales for Birds Only ; Example: Summarising Centre Size for Selected Towns
Adding Columns Example: Counting Rows ; Example: Purchase Statistics by Town
Using SELECTCOLUMNS Adding Columns using SELECTCOLUMNS
Defining Measures and Variables Defining Measures ; Defining Variables
Using ROW to Display One Row of Data
Combining Table Results using GENERATE and GENERATEALL

Notes:

  1. Since Wise Owl print courseware double-sided to save trees, the number of physical pages in each chapter is roughly half of the number of sides quoted.
  2. Downloads are provided as examples only, and should not be used or distributed in any way or form without the prior written permission of Wise Owl.
This page has 0 threads Add post