Power BI courseware and training manuals

In addition to courseware for Power BI Desktop, we also have course manuals on DAX.

You can see details of how to license our Power BI 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 Basics (2 pages) What is Power BI Desktop?
Installing and Updating Power BI
Getting Started (12 pages) Getting Started in Power BI Desktop Example for this Chapter ; Showing Keyboard Shortcuts
Working with Files Creating New Files ; Saving and Closing Files
The Power BI Desktop Screen
Getting Data
Transforming Data Editing a Query ; The Power Query Editor ; Removing and Renaming Columns ; Splitting Columns
Creating Visualisations Inserting a Visualisation ; Assigning Fields to a Visualisation
Formatting Visuals Searching for Format Properties
Filtering Visuals Adding Fields to the Filters List ; Applying a Filter
Working with Report Pages Changing the Page View ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages
Themes
Importing Data (10 pages) Our Example
Importing from Different Sources Re-Using a Data Source
Importing from Excel
Importing CSV or Text Files
Importing from SQL Server Using Queries and Stored Procedures
Importing from a Website
Importing a Power BI Dataset
Entering Data Manually Pasting Data ; Typing in Data
Data Models (10 pages) Data Models Viewing a Model ; Selecting Single Model Items ; Selecting Multiple Items ; Searching for Fields
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
Model Properties Table Properties ; Display Folders ; Default Number and Date Formatting ; Changing the Default Aggregation for a Field
Relationships The Need for Relationships ; Parent-Child Relationships ; Creating a Relationship ; Editing Relationships ; The Effect of Relationships ; Cross-Filter Direction
Visualisations (10 pages) Overview of Visualisations
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
The Selection Pane Changing the Visual Layer Order ; Changing the Tab Order ; Hiding Visuals ; Renaming a Visual
Visual Interactions Editing Interactions ; Visual Interaction Settings
Tables (15 pages) Basic Tables
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
Aggregating Data
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
Formatting Dates Displaying Dates as Dates ; Formatting Dates (Method 1) ; Formatting Dates (Method 2)
Formatting Visuals – General Visual Borders, Shadow and Background ; Titles
Specific to Formatting Tables Column Headers ; Totals ; Font Size and Typeface ; Table Padding and Gridlines ; Table Styles ; Formatting Columns Individually
The Wonderful Format Painter
Images and URLs in Tables Showing Images ; URLs in Tables
Conditional Formatting (7 pages)

Download this chapter 		Conditional Formatting
Applying Conditional Formatting to Fields Applying Conditional Formatting through the Field Well ; Applying Conditional Formatting through the Format Pane
Gradient Effects Adding a Middle Colour
Rules-Based Conditional Formatting
Data Bars
Formatting Using Field Values Using SWITCH to Generate Colours
Displaying Icons
Matrices (3 pages) Overview of Matrices
Creating a Matrix Sorting in a Matrix
Multiple Rows, Columns and Values Disabling or Editing Stepped Layout
Text Boxes, Images and Shapes (4 pages) Non-Data Visualisations Drawing Text Boxes, Images or Shapes
Images Scaling Images ; Adding a Hyperlink to an Image
Text Boxes Inserting Values
Shapes Adding a Shape
Chart Basics (12 pages) The Parts of a Chart
Types of Chart Available
Creating a Chart Creating a New Chart ; Turning a Table into a Chart
Sorting Charts
Formatting Charts Legends and Series Colours ; Detail Labels ; Total Labels ; Plot Area and Background ; Gridlines ; Axes ; Conditional Formatting
Excluding and Including Data
Zoom Sliders
Small Multiples (4 pages) What Makes a Good Small Multiple?
Creating Small Multiples Setting Grid Width and Height ; Formatting Small Multiple Titles ; Formatting Small Multiple Charts
More on Charts (11 pages) Column and Bar Charts Continuous and Categorical Axes ; Category Width and Padding
Line Charts Line Formatting ; Dual Axis Line Charts
Combined Charts Formatting the Secondary Axis
Waterfall Charts Showing Breakdowns
Scatter and Bubble Charts Animating Bubble Charts
Other Chart Types Area Charts ; Ribbon Charts ; Pie and Donut Charts ; Treemap Charts ; Funnel Charts
Grouping and Binning (3 pages) Grouping Starting a Group ; Editing Groups ; Using Group Fields
Binning
Visuals for Dashboards (5 pages) Gauges Minimum, Maximum and Target Values ; Formatting Gauges
KPIs Creating Targets ; Colour Coding Targets ; Formatting the Numbers Displayed
Cards
Custom Visuals (4 pages) What are Custom Visuals?
Adding Custom Visuals
Using a Custom Visual
Removing Custom Visuals Removing Custom Visuals Pinned to the Visualisations Pane
Drill-Down (6 pages) What is Drill-Down? Enabling Drill Down
Using Drill-Down Drilling Down and Up ; Drill Down and Visual Interactions ; Viewing the Next Hierarchy Level ; Expanding All Levels in a Hierarchy
Drill-Down in a Matrix Choosing Row or Column Fields
Showing Data Tables
Slicers (9 pages) 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
Number and Date Slicers Sliders ; Choosing Dates ; Picking Relative Dates ; Changing the Anchor Date
Sync Slicers
Hierarchical Slicers
Filtering Reports (8 pages) How Filters Work
The Filters Pane
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
Sorting Filters Manually Arranging Filters ; Sorting Filters
Formatting Filters
Controlling Filters for End Users Locking and Hiding Filters ; Report Filter Settings
Drill-Through Filters (5 pages)

Download this chapter 		What are Drill-Through Filters?
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
Extra Drillthrough Options Keeping All Filters ; Drillthrough from Summarised Fields
Buttons for Drill-Through
Querying Data (13 pages) What are Queries?
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
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
Creating New Columns Creating a Formula ; Creating a Column by Example
Conditional Columns
Calculated Columns (8 pages) Introduction to Calculated Columns
Creating Calculated Columns Starting a New Column ; Typing a Formula ; Hints for Editing DAX ; DAX Editors
Conditional Functions The IF Function ; Operators in DAX ; The SWITCH Function
The RELATED Function
Blanks Testing for Blanks ; Creating Blanks ; Blank Arithmetic
Testing for Errors
Maps (9 pages) Overview of Maps
Choosing a Mapping Tool Installing Preview Map Features
Creating Maps Assigning a Location ; Bubble Size ; Conditional Formatting ; Using Legend Fields ; Heat Maps ; Map Styles
Geocoding Categorising Geographic Data ; Adding a Country Code
Latitude and Longitude Converting Postcodes ; Cleaning the Imported Data ; Configure the Data Model ; Assigning Fields to a Map
Drilldown in Maps Controlling Map Zoom
Publishing (13 pages) Overview of Publishing
An Infinite Number of Variables Your Power BI Licence ; Linking to Data ; Data Sources
Logging on to Power BI Service
Creating Workspaces
Publishing a Report
Viewing and Editing Reports Reports and Datasets ; Lineage View ; Viewing Individual Reports ; Editing a Report
Dashboards and Tiles Adding Tiles to Dashboards ; Listing Dashboards ; Viewing Dashboards
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
Power BI Apps (5 pages) What are Apps?
Creating Apps Starting to Create an App ; Configuring how Your App will Look ; Configuring your Navigation Menu ; Configuring Access to Data Sources ; Finishing Publication
Installing Apps

Advanced Power BI - Data courseware

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

Chapter Sections Subsections
Manipulating Columns (8 pages) Splitting Columns Splitting by Number of Characters ; Splitting by Delimiters ; Splitting by Positions ; Splitting by Change in Case or Character Type ; Splitting into Rows ; Retaining Quotation Marks
Merging Columns
Extracting Data Replacing or Adding Columns ; An Example ; The Possible Options
Creating Columns (10 pages) Ways to Create New Columns
Columns from Examples Step 1 – Start the Feature ; Step 2 – Show some Examples ; Step 3 – Confirm the Formula ; Step 4 – Review your Formula ; Step 5 – Understanding your Formula
Built-up Columns
Custom Columns in M M Prefixes ; Our Example – Elapsed Days ; Creating a Custom Column ; A Special Case: Last Refresh Date/time
Conditional Columns
Indexing Columns
Combining Queries (11 pages) Ways to Combine Data
Loading Multiple Files from a Folder Setting the Folder ; Combining the Files ; Setting the Template for Import ; Tidying Up the Results ; An Outline of How it Works
Appending Queries Combining Queries using Append ; Tidying up your Results
Merging Data Loading the Tables to Merge ; Merging the Data ; Flattening the Combined Table ; Final Touches
Fuzzy Merging Setting a Similarity Threshold ; Enforce Case Sensitivity ; Using a Transformation Table
Manipulation Transforms (8 pages) Unpivoting Data Loading the Original Data ; Unpivoting the Data ; Renaming your Columns
Grouping Data Optional Step – Remove Excess Columns ; Starting Grouping ; Specifying How to Group Data
Pivoting Columns Remember Matrices ; Our Example for Pivot Columns ; Creating the Necessary New Columns ; Pivoting by Columns
Transposing Data Replacing within a Column ; Joining Rows Together
Normalising Data (6 pages) Our Example
Extracting Unique Lists Duplicating the Original Table ; Creating a Unique List of Directors/Genres
Numbering the Lists
Storing Id Numbers not Names Merging the ID Column ; Expanding the Merged Columns ; Removing Unnecessary Additional Columns ; Making Sure the Right Table Loads ; The Final Relationships ; Finishing your Data Model
Data Profiling (3 pages) Showing Profiling Data Displaying Pop-up Column Profiles ; Grouping Profiled Data
Query Dependencies
Filtering Queries (3 pages) Removing Nulls and Errors
Filtering Dates Previous or Next Time Periods ; Matching the Earliest/Latest Date ; Specific Time Periods
Error Handling (5 pages) Default Treatment of Errors Errors in the First 1000 Rows ; Errors in Subsequent Rows
Fixing Errors in a Separate Table Step 1 – Duplicate the Table ; Step 2 – Remove the Errors from One Table ; Step 3 – Correct the Errors in the Second Table ; Merging the Data Back Together
Fixing Errors In Situ Replacing Errors ; Handling with a Custom Column
Datasets and Dataflows (5 pages) Overview
Loading from a Power BI Dataset
Power BI Dataflows Creating a Dataflow (the Data Source) ; Creating a Dataflow (the Transforms) ; Saving your Dataflow ; Loading Data from your Dataflow
Row-Level Security (9 pages) Overview of Row-Level Security Limitations and Constraints
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
Switching data sources (4 pages) Changing the Data Source Manually
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
Simple Parameters (8 pages)

Download this chapter 		Parameters, Slicers and Filters Parameters Needed for this Example
Working with Parameters Accessing Parameters ; Creating Parameters ; Managing Parameters ; Loading Parameters into Power BI
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 – Adding a Quick Measure to Reference the Parameter Value ; Step 2 – Create a Card to Display the Measure
Using Parameters with Templates
Dropdown Parameters (4 pages) Our Example
Creating a Simple List Parameter
Using List Queries for Parameters Step 1 – Create a Query Giving a Single Column of Data ; Step 2 – Make this a List Query ; Step 3 – 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 Connections (6 pages) SQL Server Dynamic Connections First Enable Parameterisation of Data Sources ; Creating a Parameter ; Using this Parameter to Get Data ; Referencing your Parameter ; 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 ; Step 4 - Changing the Worksheet Used
What-if Parameters (5 pages) Our Example The Steps to Follow
Creating and Using What-If Parameters Step 1 – Create the What-If Parameter ; Step 2 – Customise the Slicer ; Step 3 – Create a Quick Measure to Show your Data ; Step 4 – Display your Measure in a Visual
Custom Functions (14 pages) Overview
Example – Splitting Postcodes Step 1 – Getting the Initial Data ; Step 2 - Creating a Parameter ; Step 3 - Duplicate your Parameter ; Step 4 – Create a Query Based on the Parameter Value ; Step 5 - Turning this One-Off Query into a Function ; Step 6 - Understanding the Resulting List of Queries ; Step 7 – Test your Function ; Step 8 – Invoke your Function for Each Table Row ; Step 9 – Expand the Results
Example - Combining Selected Worksheets Setting Up your Base Query and Parameter ; Link the Query to the Parameter ; Creating your Custom Function ; Invoking your Custom Function ; Expanding the Results
Example – Combining All Worksheets
Combining Tables from Web Pages Getting the Table of Genres ; Getting at the URL ; Creating a Parameter ; Getting the List of Films for a Genre ; Integrating the List of Films and your Parameter ; Creating the Function ; Invoking the Function
Getting External Links
HTML and JSON (5 pages) Overview
Scraping HTML Creating an HTML.Table Function ; Expanding the Table Returned ; Getting the Readable Text Only
Transforming JSON Parsing JSON (or XML) ; Expanding the Resulting Record ; Extracting the List of Topics ; Creating the Relationship
Using APIs (10 pages) Introducing APIs
Some Simple Examples Random Dog Pictures ; XKCD Cartoons ; Displaying Images Properly
Case Study – Exchange Rates Finding an API ; Getting the URL Needed ; Turning the Results into a Usable Table ; Adding Country Codes ; Getting the UK Exchange Rate
Multiple Table APIs Importing individual Tables ; Simplifying Column Titles ; The Need for a Bridging Table ; Starting the Bridging Table ; Removing Non-Essential Columns ; Expanding the List of Films
Data Insights (4 pages) Overview of Data Insights Three Warnings
Text Analytics Detecting Language ; Extracting Key Phrases ; Scoring Sentiment
Tagging Images

Advanced Power BI - Reports courseware

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

Chapter Sections Subsections
Advanced Tables (5 pages) Showing Images in Tables
URLs in Tables Displaying Text Entries as URLs ; Displaying URLs as Icons ; Displaying Other Columns as Clickable URLs
Sorting by Multiple Columns
Preventing Data Grouping Preventing Summarising for a Single Field ; Changing the Default Summarisation Options
Dynamic Formatting (2 pages) An Example of Dynamic Formatting
Applying Dynamic Formatting
Data Analytics (8 pages) Overview of Lines
Adding Lines
Trend Lines The Combine Series Option
Forecast Lines
Anomalies Showing the Expected Range ; Showing Anomalies ; Explanatory Fields
Symmetry Shading
Ratio Lines
Decomposition tree (8 pages) Overview
Creating and Using Creating a Decomposition Tree ; Using a Decomposition Tree ; Removing Unwanted Levels ; Expand and Collapse Options ; Locking Levels
Formatting Decomposition Trees Title and Subtitle ; Tree Density ; Changing the Connectors ; Formatting the Data Bars
AI Splits Turning AI Splits Off ; Absolute Splits ; Relative Splits
Key Influencers (6 pages)

Download this chapter 		Creating a Key Influencers Visual Using Multiple Explanation Fields ; Changing the Analysis Type ; Binning Values ; Displaying a Count of Values ; The Expand By Option
Top Segments Viewing Top Segments ; Drilling into a Segment
Advanced Charts (3 pages) Waterfall Charts Parts of a Waterfall Chart ; Creating a Waterfall Chart
Ribbon Charts Formatting Ribbon Charts
Q&A Visuals (5 pages) The Q&A Visual Creating a Q&A Visual ; Making the Visualisation Permanent ; Formatting Q&A Visuals
Improving the Q&A Experience Ambiguous Terms ; Adding Synonyms (1 of 3) – using Model View ; Adding Synonyms (2 of 3) – Setting Field Synonyms ; Adding Synonyms (3 of 3) - Teaching the Q&A Feature
Tooltips (11 pages) Overview
Basic Tooltips Minimal Tooltips ; Configuring Basic Tooltips ; Adding Fields to Tooltips
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 5 – Choosing whether to Keep Filters ; Step 6 – Assigning your Tooltip Report Page to a Visual
Visual Header (Help) Tooltips Step 1 – Creating the Tooltip Page ; Step 2 – Creating the Report Page for the Tooltip ; Step 3 – Assigning your Page to the Visual Header
Buttons and Shapes (5 pages) Overview Types of Clickable Objects ; Types of Action
Adding Clickable Shapes
Adding Clickable Images
Adding Clickable Buttons Adding the Button ; Setting Default, Hover, Selection and Disabled Effects ; Formatting Buttons
Page Navigation (6 pages) Overview
Page Navigators Formatting Page Navigators
Simple Custom Page Navigators
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
Bookmarks (10 pages) Examples of Bookmarks
Our Case Study The Bookmarks Needed
Creating 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 to Pages using Bookmarks Creating the Home Bookmark
Bookmark Navigators Assigning Bookmarks to Groups ; Adding a Bookmark Navigator ; Formatting the Selected Bookmark ; Controlling Deselection Behaviour
Ideas for Bookmarks (10 pages) 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
Pop-Up Buttons Creating Each Help Message ; Creating the Bookmarks ; Assigning Actions to the Bookmarks
Quick Measures (10 pages) About Measures
Creating Normal Measures Step 1 - Creating a Measures Table ; Step 2 – Adding a Measure
Starting Quick Measuresw
Example: Comparison against Single Items Choosing the Calculation Type and Base Value ; Specifying the Filtered Value ; Viewing and Tidying Up your Quick Measure
Example: Listing Selected Items Creating the Slicer Selection Quick Measure ; Displaying the Slicer Selection ; Creating a Measure to Show the Visual Title ; Showing a Dynamic Title
Example: Line Chart Averages Creating the Base Measures ; Reviewing the DAX ; Creating the Final Measure ; The Final Chart
Example: Running Totals
Narratives (3 pages)

Download this chapter 		Smart Narratives What are Smart Narratives? ; Creating a Smart Narrative ; Formatting Values Generated
Explaining Changes
Shared datasets (2 pages) Creating Shared Datasets Linking to a Shared Dataset
Limitations of Shared Datasets
Shared Reports
Advanced Drill-Through (11 pages) Our Example
Creating the Basic Drill-Through Creating the Required Pages and Visuals ; Controlling Filtering ; Creating a Back Button
Making Drill-Through Easier Drill-Through using your Left Mouse Button ; Drill-Through Buttons ; Our Example Button ; Creating the Button ; Configuring the Disabled Appearance ; Creating a Measure for the Enabled Button Text ; Showing this Measure’s Text on your Button ; Conditionally Disabling a Button
Categorised versus Summarised
Cross-Report Drillthrough Step 1 – Enable Cross-Report Drillthrough ; Step 2 – Create a Drillthrough Target Report ; Step 3 – Publish the Reports ; Step 4 – Test the Drillthrough Feature
Custom Visuals (5 pages) Introducing Custom Visuals
Possible Disadvantages of Custom Visuals Difficulty of Use ; Cost and Reliability
Creating and Deleting Custom Visuals Installing Custom Visuals ; Using Custom Visuals ; Deleting Custom Visuals
Pinning and Unpinning Visuals Pinning Visuals ; Unpinning Visuals ; Restoring the Default Visuals
Examples of Custom Visuals (7 pages) Visuals Covered
Chiclet Slicers Step 1 – Ensure you have an Image URL ; Step 2 – Create your Chiclet Slicer ; Step 3 – Setting the Images ; Step 4 – Formatting the Slicer
The Play Axis Visual How it Works ; Setting the Play Axis Slicer Field ; Animation Settings ; Formatting the Caption
The Aquarium Custom Visual
Infographics (picture charts) (6 pages) Assembling what you Need
Creating a Picture Chart Step 1 – Creating the Chart ; Step 2 – Editing the Chart ; Step 3 – Adding your Images ; Step 4 – Assigning Pictures to Data Series ; Step 5 - Stacking your Pictures ; Step 6 – Set your Picture Unit Size ; Adding Text Labels
Synoptic Panels (8 pages) Overview
Creating the Image Map Adding an Image ; Filling In your Picture (Colour Dropper) ; Filling in your Picture (Drawing Shapes) ; Naming the Areas ; Saving your Image
Creating Synoptic Panels
Formatting Synoptic Panels The Toolbar ; Colours ; Data Labels ; Using States to Show Colours ; Further Reading on States

DAX courseware

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

Chapter Sections Subsections
Getting Started (5 pages) Introducing DAX Where is DAX used? ; How DAX is Used 1 - Calculated Columns ; How DAX is Used 2 – Measures ; How DAX is Used 3 – Queries
The Construct-a-Creature Database The Database Tables and Relationships
Column Storage Row versus Column Storage ; Data Compression ; Implications for Loading Data
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 ; Pressing the TAB Key ; 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
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
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
Trapping Errors (2 pages) Using the DIVIDE Function
Using IFERROR Generating your Own Errors using ERROR
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
Measures (11 pages) Introduction to Measures What Measures Are ; Examples of Measures
Creating a Measures Table
Creating Measures
Quick Measures Starting a Quick Measure ; Creating the Base Value ; Setting any Filtering ; Using a Quick Measure
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 Matrix ; 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 the Year as Text ; Step 5 - 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 ; Interlude - 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
Parent-child hierarchies (4 pages) What is a Parent-Child Hierarchy?
Creating 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 – Creating your Visual
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