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 Basics (3 pages) What is Power BI Desktop? Downloading Power BI Desktop ; Installing from the Microsoft Store ; Starting Power BI Desktop ; Keeping the Application Updated ; Signing In to the Power BI Service
Getting Started (13 pages) Getting Started in Power BI Desktop Example for this Chapter ; Showing Keyboard Shortcuts
Working with Files Creating New Files ; Saving Files ; Closing Files
The Power BI Desktop Screen
Getting Data
Transforming Data Editing a Query ; The Power Query Editor ; Removing the Rating Column ; Extracting Text from a Column ; Undoing Changes
Creating Visualisations Inserting a Visualisation ; Assigning Fields to a Visualisation
Formatting Visuals Viewing Format Options ; Searching for Format Properties
Filtering Visuals Adding Fields to the Filters List ; Applying a Filter ; Removing Filters ; Viewing Filters
Working with Report Pages Changing the Page View ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages
Creating Templates Creating Templates ; Using Templates
Importing Data (10 pages) Our Example
Importing from Different Sources Importing from Excel ; Importing from CSV or Other Text File ; Importing from SQL Server ; Writing a Query to Return SQL Server Data ; Importing from a Website ; Importing Data from Access
Entering Data Manually Pasting Data ; Typing in Data
Data Models (8 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 ; Hiding Objects in the Report View
Model Properties Table Properties ; Field Properties
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 Visualisation ; The Visual Editing Panes ; The Visual Options Menu ; Focus Mode ; Spotlight Mode ; Resizing a Visual ; Moving, Selecting and Deleting Visuals ; Locking Visuals ; Aligning and Distributing Visualisations ; Grouping Visualisations
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 Column ; 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 Tables Font Size and Typeface ; Table Borders ; Table Padding ; Table Gridlines ; Formatting Columns Individually ; Table Styles ; Titles ; Totals
The Wonderful Format Painter
Images and URLs in Tables Showing Images ; URLs in Tables
Conditional Formatting (6 pages)

Download this chapter 		Conditional Formatting Applying Conditional Formatting to Fields ; Choosing Colour Scales ; Diverging Colour Scales ; Rules-Based Conditional Formatting ; Configuring Data Bars ; Formatting Using Field Values ; Displaying Icons ; Conditional Formatting in Other Parts of a Visual
Matrices (4 pages) Overview of Matrices
Creating a Matrix Sorting in a Matrix
Multiple Rows, Columns and Values Basic Drill Down ; Disabling Stepped Layout ; Displaying Buttons for Quick Drill Down
Text Boxes, Images and Shapes (5 pages) Non-Data Visualisations Drawing Text Boxes, Images or Shapes
Images Scaling Images ; Adding a Hyperlink to an Image
Text Boxes Formatting Text in a Text Box ; Adding a Hyperlink to Text
Shapes Line vs. Border ; Fill vs. Background ; Changing the Z-Order of a Shape
Chart Basics (10 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 ; The Plot Area and Background ; Axes ; Tooltips ; Conditional Formatting
Excluding and Including Data
More on Charts (11 pages) Column and Bar Charts Continuous and Categorical Axes ; Category Width and Padding
Line Charts Line Formatting ; Trendlines and Statistics
Combined Charts Formatting the Secondary Axis
Waterfall Charts Showing Breakdowns
Scatter and Bubble Charts Detail Fields ; 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 (4 pages) Gauges Gauge Fields ; Formatting Gauges
KPIs Colour Coding in KPIs
Cards Multi-row Cards
Custom Visuals (3 pages) What are Custom Visuals?
Adding Custom Visuals
Removing Custom Visuals
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 Listing Records ; Showing Data
Slicers (7 pages) Slicer Basics Creating a Slicer ; Selecting and Clearing Items ; Changing Selection Behaviour ; Searching in Slicers ; Dropdown Slicers ; Slicer Orientation ; Formatting Slicers ; Images in Slicers ; Slicer Interactions
Number and Date Slicers Sliders ; Choosing Dates ; Picking Relative Dates ; Changing the Anchor Date
Synchronised Slicers
Filtering Reports (7 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 ; Number Filters ; Relative Date Filtering ; Top and Bottom Filters
Sorting Filters Manually Arranging Filters ; Sorting Filters
Formatting Filters Formatting the Filters Pane ; Formatting Filter Cards
Controlling Filters for End Users Locking and Hiding Filters ; Report Filter Settings
Drill-Through Filters (6 pages)

Download this chapter 		What are Drillthrough 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
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
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 ; Creating 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
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
Filled Maps (Choropleths)
Drilldown in Maps Controlling Map Zoom
ArcGIS Maps (8 pages) Creating ArcGIS Maps
Editing ArcGIS Maps Edit Mode ; Changing the Base Map ; Changing the Location Type ; Choosing a Theme ; Symbol Appearance
Pins
Showing Drive Times
Reference Layers
Power BI Service (9 pages) Introduction to Power BI Service The Power BI Service New Look ; What Publishing Involves
Publishing Reports from PB Desktop
Using Power BI Service Going to Power BI Service ; Workspaces ; The Four Main Areas of Power BI Service
Dashboards and Tiles Adding Tiles to Dashboards ; Viewing Dashboards
Creating Reports in Power BI Service Creating or Editing a Report ; Saving Reports
Printing and Exporting Reports Exporting to PowerPoint ; Exporting to PDF
Sharing Reports Sharing Models ; Sharing a Report for Free ; Sharing Individual Reports (Paid)
App Workspaces (7 pages) What are App Workspaces?
Creating an App Workspace
Using Workspaces Publishing to Workspaces ; Setting Access Rights
Publishing and Updating App Workspaces Starting to Publish/Update ; Configuring how Your App will Look ; Configuring your Navigation Menu ; Configuring Access to Data Sources ; Finishing Publication
Installing and Deleting Apps Installing Apps ; Deleting Apps

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 (13 pages) Loading Multiple Files Setting the Folder ; Combining 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
Data Profiling (2 pages) Showing Profiling Data Displaying Pop-up Column Profiles ; Grouping Profiled Data
Simple Parameters (9 pages)

Download this chapter 		Parameters, Slicers and Filters
Working with Parameters Accessing Parameters ; Creating Parameters ; 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 – Adding a Quick Measure to Reference the Parameter Value ; Step 3 – Creating 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 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 ; 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
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 (10 pages) Examples of Custom Visuals
Possible Disadvantages of Custom Visuals
Working with Custom Visuals Installing Custom Visuals ; Using Custom Visuals ; Deleting Custom Visuals
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
Synoptic Panels (7 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
Tooltips (10 pages) Overview
Basic Tooltips Minimal Tooltips – the Default ; Basic 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 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
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
Dynamic Formatting (6 pages) What is Dynamic Formatting?
Case Study 1 – Dynamic Visual Titles Creating the Base Measure(s) ; Creating the Final Measure ; Setting the Measure to be the Visual Title
Case Study 2 – Variable Background Colours Method One – Using a Measure ; Method Two – Using an Additional Field ; Method Three – Using a Conditional Column
Case Study 3 – Colouring Charts Proportionally
Q&A Visuals (4 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 ; Teaching the Q&A Feature ; Row Labels
Buttons and Actions (4 pages)

Download this chapter 		Overview Types of Button
Assigning Actions to Shapes or Images
Creating Buttons Adding Buttons ; Default, Hover and Selection Effects ; Formatting Buttons ; Dynamic Formatting
Bookmarks (6 pages) Example of Bookmarks The Bookmarks Needed
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
Ideas for Bookmarks (13 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
Moving between Pages Creating the Pages ; Synchronising the Home Page Slicer ; Creating the Bookmarks ; Assigning Actions
Pop-Up Buttons Creating Each Help Message ; Creating the Bookmarks ; Assigning Actions to the Bookmarks
Suppressing Built-in Drill-Drown
Built-in themes (3 pages) Applying Existing Themes
Using the Themes Gallery
Limitations of Themes Themes don’t Override Custom Colours ; Custom Visuals don’t Always Support Themes
Custom Themes (11 pages) Overview The Benefits of Creating a Theme ; Applying a Custom Theme
Creating a Custom Theme Step 1 – Get a Base Theme to Edit ; Step 2 – Understand the Main Theme Parts ; Step 3 – Loading your Theme in a JSON Editor ; Step 4 – Making Changes ; Step 5 – Saving your Changes ; Step 6 – Applying your Theme
Two Unhelpful (?) New Features
Making Specific Changes Syntax 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 (6 pages) 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
Creating Mobile Dashboards
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 (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
Key Influencers (6 pages) The Key Influencers Visual Showing Key Influencers ; 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

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