DAX courseware and training manuals

You can use DAX within Power BI Desktop, PowerPivot and Analysis Services (tabular model), the course manuals for which are as follows:

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


Power BI Desktop courseware

Here is a list of all of our Power BI Desktop 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
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
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)
Getting Data (3 pages) Importing Third Party Apps
Importing Excel Workbooks Pinning Parts of a Workbook ; Publishing from Excel
Analysis and Insights (6 pages) Quick Insights Scoped Quick Insights
Asking Questions Displaying the Question Box ; Asking Questions ; Getting Help while Asking Questions ; Mixing Datasets ; Featured Questions
Using Synonyms
Row-level security (4 pages) How Roles Work
Row-Level Security in Power BI Desktop Creating Roles ; Testing Roles
Roles in Power BI Service Assigning People to Roles in Power BI Service ; Testing Roles in Power BI Service
Mobile Phone Considerations (3 pages) Power BI Desktop for Mobile Phones
Mobile View for Power BI Service
Power BI Mobile

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

SSAS - Tabular model courseware

Here is a list of all of our SSAS - Tabular model manual chapters (you can download the ones marked):

Chapter Sections Subsections
Overview of SSAS Tabular (4 pages) What Tabular Models Do
Deployed Tabular Models
Tabular or Multi-dimensional Models? The Two Types of Model ; Similarities between the Two Model Types ; Why you should Use Tabular Models ; Why you should Use Multi-dimensional Models
Adding a Tabular Model Instance (2 pages) Installing a Tabular Model Instance
Creating Projects in Visual Studio (7 pages) Ensuring Analysis Services is Running
Running Visual Studio Visual Studio Versions and Names ; Running Visual Studio
Configuring Visual Studio Changing Start-up Options ; Changing the Default Server Location
Creating a Project Files and Folders Created ; Projects and Solutions ; Creating Separate Folders for Solutions
Visual Studio Windows Moving and Auto-Hiding Windows ; Solution Explorer and Properties
Models (10 pages) Working with Models Adding Models ; Editing Models
Adding Data
The Workspace Database Changing the Workspace Server
Deploying your Database Checking the Production Server Used ; Deploying a Model ; Viewing Deployment Progress ; Viewing the Deployed Database
Viewing your Model’s Data Analysing a Model in Excel ; Browsing a Model in SSMS ; Creating a Pivot Table Based on a Model
Generating Measures Using Generated Measures
Model tables (15 pages) The Make-a-Mammal Database
Loading Tables Step 1 – Choose to Load Tables ; Step 2 – Connecting to the Database ; Step 3 – Choosing Data Tables ; Step 4 – Loading the Data ; Step 5 – Saving your Model
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 The Need to Hide Columns/Tables ; Hiding Tables ; Hiding Columns ; Choosing not to Display Hidden Columns and Tables
Renaming Tables and Columns Renaming Columns ; Renaming Tables
Changing what you’ve Imported for a Table
Relationships Editing Relationships ; Creating Relationships ; Editing and Creating Relationships Manually
Pivot Tables in Excel (16 pages) Creating the Model and Measures
Creating a Pivot Table
Drill Down
Quick Explore
Working with Pivot Tables Renaming Fields ; Refreshing Changes to the Model ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Filtering
Slicers Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer ; Aligning Slicers
Controlling Multiple Pivot Tables with Slicers First Rename your Pivot Table ; Getting a Slicer to Control Multiple Pivot Tables ; An Alternative Method – Telling a Pivot Table which Slicers Control it
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
OLAP Tools
Other Data Sources (11 pages) Formats Supported
Starting to Import from Other Sources Using 32-bit Excel or Access Data Sources on 64-bit Computers
Linking to Access
Importing Excel Workbooks
Importing Text or CSV Files
Importing from the Clipboard Creating a New Table from the Clipboard ; Appending Existing Data from the Clipboard
Importing from Reporting Services
Importing from Analysis Services Importing from Multi-Dimensional Models ; Designing the Cube Query in MDX ; Importing from Tabular Models using MDX ; Importing from Tabular Models using DAX
Calculated Columns (8 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 Function Wizard
Using RELATED to Link Tables
The BLANK Function Blank Arithmetic
Aggregator Columns and SWITCH The IF Function ; The SWITCH Function
Power View Introduction (11 pages) What is Power View?
Enabling the Power View Add-in
Creating a Connection to your Model Step 1 – Starting to Create a Connection ; Saving a Connection for Reuse ; Creating a Power View Report
Basic Visualisations (Tables) Starting a Table ; Adding Fields to a Visualisation ; Removing Fields ; Changing Column Order ; Changing Aggregation Methods ; Sorting a Table or Visualisation ; Showing or Hiding Totals
Selecting, Resizing, Moving and Deleting Resizing Tables and Fields ; Moving Tables and Fields ; Selecting and Deleting Objects
Power View Window Settings Controlling what’s on Screen ; Popping Out Windows
Formatting Tools on the POWER VIEW Ribbon Tab ; Number Formatting
Text Boxes and Images Text Boxes ; Images
Filtering in PowerView (5 pages) Types of Filter in Power View
Slicers
Tiles Types of Tile ; Creating Tiles ; Removing Tiles ; Adding Visualisations to Tiles
Filters Setting Report-Level Filters ; Filters for a Specific Table, Chart or Matrix ; Applying Filters
Matrices and cards (2 pages) About Matrices and Cards Matrices ; Cards and Callouts
Drill-down for Matrices
Charts in PowerView (8 pages) Introduction to Charts
Creating Charts Step 1 – Create a Basic Table ; Step 2 – Make this into a Chart ; Step 3 – Make this a Multiple Chart ; Step 4 – Setting the Title and Legend ; Step 5 – Filtering the Chart
Drill Down in Pie Charts
Scatter and Bubble Charts Scatter Charts ; Colouring and Labelling Scatter Charts ; Bubble Charts ; Playing Bubble or Scatter Charts
Measures (7 pages) Calculated Columns vs. Measures Calculated Columns are Row-Based ; Measures are Aggregated ; Pivot Table Placement ; Note on Differences from PowerPivot
Creating Measures Step 1 – Choosing a Host Table ; Step 2 – Choosing a Cell ; Step 3 – Creating the Measure ; Step 4 – Naming your Measure ; Step 5 - Changing the Default Formatting ; Step 6 - Applying Filters to Test your Measure ; Step 7 - Testing your Measure in Excel
Filter Context
DAX Editors (6 pages) Summary of Alternate Editors Shortcomings of the Built-in Editor ; Third-Party Tools Available
DAX Studio Installing DAX Studio ; Connecting to your Model ; Refreshing your Model ; Hints on Using DAX Studio ; Copying Measure Formulae into SSAS
DAX Editor Installing and Running DAX Editor ; Using DAX Editor ; Connecting to the Right Database ; Problems with DAX Editor
Measure Syntax (5 pages) Basic Syntax Rules Data Types ; Symbols you can Use
Aggregation Functions Aggregating a Column ; Aggregating an Expression (X-Suffix Functions)
Calculating Ratios Counting Rows using the COUNTROWS Function ; Creating Ratios: the Fields Needed ; The Final Pivot Table ; Summing Ratios wouldn’t Work
The CALCULATE Function (5 pages) Syntax of the CALCULATE Function
Replacing Individual Filters Replacing a Single Filter ; Replacing More than One Filter
Removing Filters Using ALL Removing a Single Filter ; Removing Multiple Filters ; Removing All Filters bar One
Using VALUES to Edit a Constraint On its own, VALUES is pointless ; Amending the Filter 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
The EARLIER Function (4 pages) Overview of the EARLIER Function Our Three Worked Examples ; What the EARLIIER Function Does
Case Study: Running Totals
Case Study: Ranking using EARLIER
Case Study: Group Averages
Banding (3 pages) What is Banding? Creating a Banding Table
Creating a Banding Formula
Sorting the Bands
Ranking (4 pages) The RANKX Function Syntax of the Rank Function ; Entering RANKX in a Calculated Column
Using RANKX in Measures Step 1 – Creating the Measure to Rank ; Step 2 – using ALL
Customising Ranking Changing the Sort Order ; Dealing with ties ; Hypothetical sorting
Calendars (11 pages) The Need for a Separate Date Table The Excel Approach – Date Functions ; The Tabular Approach – Create a Separate Table
Creating your Dates Table Creating a Calendar Table in Excel ; Creating a Calendar Table in SQL Server
Creating Relationships Changing Column Data Types in Preparation ; Creating Relationships ; Using Integer Date Keys
Announcing your Date Table to the World
Using Calendars in Pivot Tables Sorting Months (and Other Fields)
Managing Date Granularity
Timelines
Creating New Aggregator Columns A Bad Solution ; The Better Solution: a Bank Holiday Table ; Creating a Relationship for the Bank Holiday Table ; Creating a Calculated Column for Working Days
Multiple Calendar Tables (4 pages) The Problem, and Two Solutions Repeat the Table or the Relationship?
Solution One: Duplicate the Calendar Table
Solution Two: Duplicate the Relationship The CALCULATETABLE Function ; The USERELATIONSHIP Function ; Our Measures
Date Functions (11 pages) Contents
Period to Date Using DATESYTD (and how Date Functions Work) ; TOTALYTD, TOTALQTD and TOTALMTD ; Changing the Financial Year End
Comparing with Previous Periods The SAMEPERIODLASTYEAR Function ; Using DATEADD to Compare with any Previous Period
Parallel Periods
Moving Averages
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Using PERIOD Tables Step 1 – Create a Period Dimension ; Step 2 – Create the Date Measures ; Step 3 – Create the Necessary Dimension Measure ; Step 4 – Creating your Pivot Table
KPIs (5 pages)

Download this chapter
About KPIs Three Parts of a KPI
Creating a KPI Step 1 – Create the Calculated Fields Needed ; Step 2 – Starting to Create the KPI ; Step 3 – Customising your KPI ; Step 4 – Displaying the KPI in Excel
Absolute Targets
Hierarchies (9 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
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 - Finding Managers at Each Level (the PATHITEM Function) ; Step 4 – Returning each Manager’s Name (the LOOKUPVALUE Function) ; Step 5 – Creating a Hierarchy ; Step 6 – Dealing with Blanks ; Step 7 – Suppressing Extra Levels (the ISFILTERED Function)
Perspectives (4 pages) What are Perspectives?
Creating Perspectives
Using Perspectives Switching Perspectives in a Model ; Choosing a Perspective when Analysing Data in Excel ; Choosing Perspectives for Data Connections ; Changing the Perspective for an Existing Pivot Table
Prototyping in PowerPivot (8 pages) Enabling PowerPivot
Using PowerPivot
Summary of Differences between PowerPivot and SSAS Tabular Different Pivot Table Field List ; Implicit Measures ; Creating Measures in the Client ; Linked Excel Tables
Uploading PowerPivot Models into Tabular Preparation: SQL Server 2012 Updates ; Preparation: Remove Linked Tables? ; Importing a PowerPivot Model
Using DAX and MDX Queries (12 pages) Comparing DAX and MDX Which Packages Support which Language?
Querying Data in Management Studio Management Studio Queries using MDX ; Management Studio Queries using DAX
Creating SSRS Reports Creating a Shared Data Source and Starting a Dataset ; Querying for SSRS using MDX ; Querying for SSRS Using DAX
DAX (or MDX) in PowerPivot
Integration Services Data Sources Step One – Creating a Data Source and Connection ; Step Two – Choose a Provider ; Step Three – Choosing a Database ; Step Four – Pasting your DAX or MDX Query
DAX and MDX for Excel Step 1 – Starting to Create the Connection ; Step 2 – Finishing the Connection ; Step 3 – Make your Connection use a Query ; Step 4 – Using this Connection ; Step 5 – Setting the Query
Writing DAX Queries (11 pages) DAX Studio
SQL versus DAX Querying
Listing Tables (EVALUATE) Listing All Rows ; Ordering Rows ; Starting at a Particular Row ; Returning N Rows Only ; Sampling Rows
Summarising Summarisation Syntax ; Examples of Summarisation ; 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: Transaction Statistics by Town ; Defining Measures First
Combining Table Results GENERATE and GENERATEALL
Other Useful Functions Using ROW to Display One Row of Data ; Using CONTAINS to Check if a Field Value Exists
Processing (10 pages) Column Storage Row versus Column Storage ; Data Compression
Ways to Speed up Processing Sort Tables First ; Omit High-Cardinality Columns where Possible ; Avoid Calculated Columns ; Avoid Expensive Queries ; Use Server Timings ; Don’t Store DateTime Columns ; Consider Splitting Id Columns
Processing Concepts What Processing Involves (Data/Metadata) ; Two Stages of Processing ; Types of Processing ; The Most Efficient Strategy
Processing in SSDT Processing an Individual Table ; Processing All Tables for a Given Connection ; Processing an Entire Model ; Changing Processing Options for Deployment
Processing in SSMS Processing an Entire Database ; Processing Tables
Partitions (5 pages) Partitions
Partitions in SSDT Processing Partitions in SSDT
Partitions in SSMS Processing Partitions in SSMS
DirectQuery (5 pages) What is DirectQuery? How Normal Tabular Models Work ; How DirectQuery Works
Enabling DirectQuery Limitations of DirectQuery ; Enabling DirectQuery Mode for a Model ; Dealing with Errors ; Working in DirectQuery Mode
Deploying DirectQuery Models The Four Possible Query Modes for Deployment ; Switching Query Mode after Deployment
Security (11 pages) Overview of Security Types of Role in SSAS Tabular ; Levels of Database Access
Server Administrators
Managing Roles Managing Roles in SSDT ; Managing Roles in Management Studio
Controlling Data Access (Row Filters) Row Filters in SSDT ; Row Filters in SSMS
Three Ways to Test Row Filters Testing using Excel ; Testing using Connection Strings ; Testing by Running as a Different User
Creating a Permissions Table
Dynamic Security Two DAX Functions Needed ; Using CustomData for Dynamic Security ; Incorporating a Test for the User Name

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