Wise Owl Courseware
Power BI Tools courseware and training manuals
In addition to courseware for Power BI Desktop, we also have course manuals for PowerPivot (whether this still counts as a Power BI application we're not sure!):
You can see details of how to license our Power BI Tools courseware (including volume requirements) here.
Basic Power BI courseware
Here is a list of all of our Basic Power BI manual chapters (you can download the ones marked):
| Chapter | Sections | Subsections |
|---|---|---|
| Power BI Desktop Basics (2 pages) | What is Power BI Desktop? | Downloading Power BI Desktop ; Starting Power BI Desktop ; Signing in to an account |
| Getting Started (11 pages) | Example for this Chapter | Showing Keyboard Shortcuts |
| Working with Files | ||
| The Power BI Desktop Screen | ||
| Getting Data | Choosing the Table of Data to Import | |
| Basic Querying | Starting to Query Data ; Removing the Rating Column ; Globally Replacing Text in a Query ; Returning to your Table | |
| Creating Visuals | ||
| Formatting Visuals | Searching for a Format Property | |
| Filtering your Data | ||
| Working with Reports | Changing how you View Reports ; Page Settings ; Renaming Pages ; Adding and Deleting Pages ; Hiding Pages | |
| Importing tables (12 pages) | Our Example | |
| Importing from Different Sources | Importing from Facebook ; Importing from Excel ; Importing from a CSV or Other Text File ; Importing from SQL Server ; Importing from a Website ; Pasting Data into a Model ; Loading data from Access ; Importing from Analysis Services | |
| Typing in Data | ||
| Hiding Columns and Tables | Hiding Columns ; Hiding Tables ; Showing or Unhiding All Hidden Columns/Tables | |
| Visualisations (7 pages) | Overview of Visualisations | |
| Working with Visualisations | Adding a Visualisation ; Editing Visualisations (Fields, Format or Analytics) ; Zooming In and Out (Focus Mode) ; Spotlighting a Visual ; Resizing a Visualisation ; Moving, Selecting and Deleting Visualisations ; Locking Visuals ; Aligning and Distributing Visualisations | |
| Using the Selection Pane | Displaying and Using the Selection Pane ; Hiding Visuals ; Renaming Visuals | |
| Visual Interactions (2 pages) | Visual Interactions | How Visualisations Affect Each Other by Default ; Editing Interactions ; What the Different Settings Mean |
| Tables (17 pages) | Basic Tables | |
| Working with Table Columns | Sorting Tables by Column ; Re-ordering Columns ; Changing Column Widths ; Changing Word Wrap Options ; Renaming Columns | |
| Aggregating Data | ||
| Formatting Tables | Formatting Numbers within a Single Table ; Formatting Numbers for All Tables ; Formatting Dates ; Font Size and Typeface ; Putting Borders around Tables ; Table Padding ; Putting Gridlines within Tables ; Formatting Columns Individually ; Table Styles ; Titles ; Totals | |
| The Wonderful Format Painter | ||
| Conditional Formatting (Colour Scales / Data Bars) | Applying Conditional Formatting or Data Bars ; Choosing Colour Scales ; Divergent Colour Scales ; Configuring Data Bars ; Rules-Based Conditional Formatting | |
| Images and URLs in Tables | Showing Images ; URLs in Tables | |
| Matrices (3 pages) | Overview of Matrices | |
| Creating a Matrix | ||
| Multiple Rows or Columns | Customising or Disabling Stepped Layout | |
| Text boxes, images and shapes (3 pages) | Overview | Our Example ; Adding Text Boxes, Images or Shapes ; Working with Images ; Working with Text Boxes ; Working with Shapes ; Bringing to Front/Sending to Back |
| Charts (general) (10 pages) | The Parts of a Chart | |
| Types of Chart Available | ||
| Creating a Chart | Creating a Chart from Scratch ; Turning a Table into a Chart | |
| Sorting Charts | ||
| Formatting Charts | Legends and Series Colours ; Data Labels ; The Plot Area ; Axes ; Tooltips ; Colour Saturation | |
| Excluding and Including Data | ||
| Charts (specific) (13 pages) | Column and Bar Charts | Showing All Axis Titles (Minimum Category Width) |
| Line Charts | Line Scaling, Widths and Markers ; Superimposing Trendlines and Statistics | |
| Area Charts | ||
| Ribbon Charts | ||
| Pie and Donut Charts | Showing Percentages of Totals | |
| Treemap Charts | ||
| Combined Charts | Controlling the Secondary Axis | |
| Funnel Charts | ||
| Waterfall Charts | Setting Breakdowns | |
| Scatter and Bubble Charts | Creating Scatter and Bubble Charts ; Properties Unique to Scatter Charts ; Data (Details) Fields ; Playing Bubble Charts | |
| Custom Visuals (3 pages) | What are Custom Visuals? | |
| Adding Custom Visuals | Deleting a Custom Visual | |
|
Showing Single Figures
(4 pages)
Download this chapter |
Gauges | Components of a Gauge ; Gauge Fields ; Formatting Gauges |
| KPIs | ||
| Cards | ||
| Multi-Row Cards (1 page) | Creating a Multi-Row Card | |
| Querying Data (13 pages) | What Queries Do | |
| Working with Queries | Creating Queries ; Saving/Closing a Query ; Deleting Parts of a Query ; Deleting All Subsequent Steps ; Inserting Steps ; Changing what a Step Does ; Grouping Queries | |
| Common Query Transforms | The Source and Navigation Steps, and Other Automatic Steps ; Filtering Rows ; Sorting Rows ; Deleting Columns ; Moving Columns ; Splitting Columns by Delimiter ; Splitting Columns by Number of Characters ; Replacing Text | |
| Adding Columns | Inserting a Column using a Formula ; Using Column by Example | |
| Conditional Columns | ||
| Relationships (7 pages) | Relationships | |
| Browsing Relationships | Resetting Layout ; Fitting to Screen ; Maximising a Table | |
| Creating Relationships | The Need for Relationships ; Parent-Child Relationships ; Creating a Relationship ; Editing Relationships ; The Effect of Relationships ; Cross-Filter Direction | |
| Using Relationships in Tables | Our Example ; Showing Columns from Parent Tables ; Showing Columns from Child Tables | |
| Combining Tables (6 pages) | Merging Rows | Using UNION to Combine Tables ; Appending Rows from One Table to Another |
| Merging Tables (Joins) | Starting a Merge Query ; Choosing a Join Type ; Displaying Columns from the Joined Table | |
| Combining Workbooks when Importing | Step 1 – Getting All the Files in a Folder ; Step 2 – Combining the Files | |
| Slicers (8 pages) | Slicer Basics | Adding Slicers ; Selecting and Clearing in Slicers ; Changing Selection Behaviour ; Searching in Slicers ; Cascading Slicers ; Images in Slicers ; Formatting Slicers ; Making Slicers Drop Down ; Horizontal Slicers |
| Synchronised Slicers | ||
| Date Slicers | Relative Date Slicers ; Changing the Anchor Date | |
| Maps (10 pages) | Overview of Maps | |
| Creating Maps | Colour Saturation ; Category Labels ; Bubble Sizes | |
| Geocoding | Avoiding Problems 1: Choosing a Field Category ; Avoiding Problems 2: Forcing Localisation | |
| Latitude and Longitude | Getting Latitude and Longitude from Postcodes ; Cleansing the Converted Data ; Creating a Relationship ; Creating the Near-Final Map ; The Last Step: Mending the Relationship | |
| Filled Maps (Choropleths) | ||
| Shape Maps | Enabling Shape Maps ; Using Shape Maps | |
| ArcGIS Maps (7 pages) | Creating ArcGIS Maps | Editing ArcGIS Maps |
| Formatting Maps | Changing the Base Map ; Choosing a Theme ; Symbol Appearance | |
| Pins | ||
| Showing Drive Times | ||
| Reference Layers | ||
| Filtering reports (4 pages) | How Filters Work | Three Levels of Filtering |
| Adding and Removing Filters | Removing Filters | |
| Other Filter Options | Combining Filters (Advanced) ; Relative Date Filtering ; Top and Bottom Filters | |
| Drill-through Filters (3 pages) | What Drill-Through Filters Do | |
| Creating the Filtered Report Pages | Creating the Report Pages ; Applying a Drill-Through Filter | |
| Using a Drill-Through Filter | ||
| Binning and grouping (4 pages) | Grouping | Starting a Group ; Allocating Items to Members of the Group ; Using Groups you’ve Created |
| Binning | ||
| Hierarchies (2 pages) | Using Hierarchies | |
| Creating Hierarchies | Deleting Hierarchies | |
| Drill-down (6 pages) | Overview of Drill-Down | The Pre-Requisite for Drill-Down to Work: Multiple Axis Fields |
| Using Drill-Down | Turning Drill-Down On and Off ; Drilling Down ; Drilling Back Up ; Special Considerations for Matrices ; Going to the Next Hierarchy Level ; Expanding All Levels in a Hierarchy | |
| Showing Data | ||
| Listing Records | ||
| Templates (1 page) | Creating Templates | Creating Templates ; Using Templates |
| New Columns (8 pages) | Overview of New Columns | Reasons to Use New Columns ; DAX |
| Creating New Columns | Starting a New Column ; Typing in a Formula ; Hints for Editing DAX ; DAX Editors ; Using AND and OR in DAX Conditions | |
| Using IF and SWITCH | The IF Function ; The SWITCH Function | |
| The RELATED Function | ||
| Blanks | Blank Arithmetic | |
| Testing for Errors (IFERROR) | ||
|
Basic Measures
(5 pages)
Download this chapter |
Measures Always Involve Aggregation | |
| Creating Measures | Starting your Measure ; Creating and Formatting a Measure Formula ; Using your Measure in a Visualisation or Filter ; The Importance of Giving DAX what it Asks for | |
| DAX Aggregation Functions | Aggregating Expressions | |
| Measures Referring to Measures | ||
| Changing Filter Context (6 pages) | Filter Context Revisited | |
| The CALCULATE Function | Syntax of the CALCULATE Function ; Removing Filters ; Removing Multiple Filters ; Removing All Filters Except One ; Replacing one filter ; Replacing Multiple Filters | |
| Filter Function | Using Calculate instead of Filter | |
| Building on the Filter Context (VALUES) | Without the VALUES Function ; With the VALUES Function | |
| Advanced DAX (3 pages) | Variables | |
| The EARLIER Function | Our Two Worked Examples ; What the EARLIER Function Does ; Example: Number of Purchases on the Same Date ; Example: Ordering Purchases by Date | |
| Calendars and Dates (8 pages) | What Calendars Are, and Why to Use Them | |
| Calendar Tables within Power BI Desktop | Calendars using the M Language ; Renaming the Date Column ; Creating New Columns | |
| Importing Calendar Tables | Creating a Calendar Table in Excel ; Creating a Calendar Table in SQL Server | |
| Using Calendar Tables | Simple Relationships ; Sorting Months ; Multiple Table Relationships | |
| Case Study: Working Days | ||
| Date Formulae (8 pages) | Cumulative to Date | The TOTALYTD, TOTALQTD and TOTALMTD Functions ; DATESYTD, DATESQTD, DATESMTD Functions |
| Comparisons with Previous Years | The SAMEPERIODLASTYEAR Function ; The DATEADD Function | |
| Parallel Periods | ||
| Semi-Additive Measures | Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank | |
| Moving Averages | ||
| Power BI Service (4 pages) | Introduction to Power BI Service | |
| Publishing Reports from PB Desktop | ||
| Reports within Power BI Service | Creating Reports ; Editing Reports ; Filtering Reports ; Another Way to View Items in Power BI Service | |
| Dashboards and tiles (9 pages) | Overview of Dashboards | |
| Working with Dashboards | Creating a Dashboard ; Editing and Deleting Dashboards ; Setting a Featured Dashboard ; Refreshing Dashboards | |
| Working with Tiles | Adding Tiles ; Moving and Resizing Tiles ; Changing Dashboard Tile Flow ; Exporting Data for a Tile ; Viewing the Report or Dataset for a Tile ; Changing a Tile’s Title and Subtitle ; Changing what Happens when you Click on Tiles | |
| Live Pages | ||
| Adding Tiles Directly | Examples of Each Added Tile Type | |
| Refreshing Data (6 pages) | What Needs Refreshing | |
| On-Premises Data Gateways | Types of Gateway ; What Constitutes a Gateway | |
| Installing and Configuring Gateways | Step 1 - Getting Gateway Software ; Step 2 – Running the Installation Software ; Step 3 – Signing In ; Step 4 – Assigning Datasets to Gateways | |
| Refreshing Data | Manually Refreshing Data ; Scheduled Refreshing ; Viewing Refresh History ; Scheduled Refreshes | |
| Sharing (7 pages) | Ways to Share | |
| Publishing an App | Step 1 - Creating an App Workspace ; Step 2 – Publishing to the App Workspace ; Step 3 – Add Dashboards ; Step 4 – Start to Publish your App ; Step 5 – Configure your App ; Step 6 - Getting Access to a Published App | |
| Sharing Dashboards | Subscribing to Dashboards | |
| Sharing Reports | Saving Reports (PDF and PowerPoint) |
Advanced Power BI courseware
Here is a list of all of our Advanced Power BI manual chapters (you can download the ones marked):
| Chapter | Sections | Subsections |
|---|---|---|
| Advanced Query Editor (14 pages) | Loading Multiple Files | Setting the Folder ; Setting the Files ; Setting the Template for Import ; Tidying Up the Results ; How it Works |
| Appending Queries | ||
| Merging Data | Loading the Tables to Merge ; Merging the Data ; Flattening the Combined Table ; Tidying the Final Table | |
| Query Dependencies | ||
| Switching the Data Source using M | Showing the Advanced Editor ; The Excel Query ; Creating a SQL Server Version of our Table ; Switching the Data Source ; Resolving Problems ; Viewing the Results | |
| Unpivoting Data | Loading the Original Data ; Unpivoting the Data ; Renaming your Columns | |
| Dynamic Filtering without Parameters (8 pages) | Our Aim | |
| Creating the Slicers | The Postcode Slicer ; The Date Slicer | |
| Displaying a Dynamic Title | Step 1 – Create a Table ; Step 2 – Creating the Date Bound Measures ; Step 3 – Creating the Title Measure ; Step 4 – Create a Card ; Step 5 – Coping with Multiple Slicer Choices ; Step 6 – A Quick Measure to Display Multiple Values | |
|
Dynamic Filtering using Parameters
(9 pages)
Download this chapter |
Overview of Chapter | |
| Working with Parameters | Accessing Parameters ; Creating Parameters ; Basing Parameters on Lists ; Managing Parameters | |
| Entering Values for Parameters | Entering Parameter Values in Query Editor ; Entering Parameter Values in Power BI Desktop | |
| Filtering using Parameters | ||
| Referencing Parameters in Power BI | Step 1 – Loading Parameters into your Data Model ; Step 2 – Creating a Table to Hold Measures ; Step 3 – Creating the Title Measure ; Step 4 – Creating a Card to Display the Measure | |
| Dropdown Parameters (3 pages) | List Queries | |
| Creating the List Query | Step 1 – Create a Query Giving a Single Column of Data ; Step 2 – Make this a New Query | |
| Creating the Dropdown Parameter | ||
| Parameters and Stored Procedures (4 pages) | Overview | |
| Loading Data from a Stored Procedure | ||
| Making Parameters Dynamic | Creating the Parameters ; Referencing the Parameters in the Advanced Editor ; Editing Parameter Values | |
| Dynamic Data Sources (6 pages) | SQL Server Dynamic Connections | First Enable Parameterisation of Data Sources ; Creating a Parameter ; Using this Parameter to Get Data ; Changing the Server or Database ; The Query Editor Step Created |
| Dynamic Connections to Excel | Step 1 – Create a Connection (Get Data) ; Step 2 – Create a Parameter ; Step 3 – Parameterising the Connection Used ; Changing the Worksheet Used | |
| Data Analytics (6 pages) | Overview of Lines | |
| Adding Lines | ||
| Trend Lines and Forecasts | The Combine Series Option | |
| Forecast Lines | ||
| Symmetry Shading and Ratio Lines | Preparing your Data for Symmetry Shading ; Creating a Ratio Line and Symmetry Shading ; Post-Script on Log Scatter Charts | |
| Custom Visuals (6 pages) | Installing Custom Visuals | |
| Chiclet Slicers | Step 1 – Get the Images ; Step 2 – Ensure you have an Image URL ; Step 3 – Create your Chiclet Slicer ; Step 4 – Setting the Images ; Step 5 – Formatting the Slicer ; Step 6 – Testing your Slicer | |
| The Play Axis Visual | How it Works ; Setting the Play Axis Slicer Field ; Animation Settings ; Formatting the Caption | |
| Tooltips (10 pages) | Overview | |
| Minimal Tooltips | ||
| Displaying Field Values | Adding Additional Fields | |
| Static Report Pages | Step 1 – Create the Tooltip Page ; Step 2 – Setting the Page Size ; Step 3 – Hiding your Report ; Step 4 – Creating the Tooltip Report ; Step 5 – Assigning a Report Page to a Tooltip | |
| Dynamic Report Page Tooltips | Step 1 – Creating the Tooltip Page ; Step 2 – Creating the Report Page for the Tooltip ; Step 3 – Assigning Fields to the Tooltip Report Page ; Step 4 – Choosing When to Show your Tooltip ; Step 4 – Choosing whether to Keep Filters ; Step 5 – Assigning your Tooltip Report Page to a Visual | |
| Manual Assignment | ||
| Quick Measures (7 pages) | About Measures | |
| Creating Normal Measures | Step 1 - Creating a Measures Table ; Step 2 – Adding a Measure | |
| Starting Quick Measures | ||
| Case Study 1 – London versus Regional Sales | Choosing the Calculation Type and Base Value ; Specifying the Filtered Value ; Viewing and Tidying Up your Quick Measure | |
| Case Study 2 – Listing Slicer Selections | Creating the Slicer Selection Quick Measure ; Viewing the DAX Formulae ; Displaying the Slicer Selection | |
| Converting Postcodes (4 pages) | Overview of the Problem | |
| Converting Postcodes to Latitude/Longitude | ||
| Getting at the Latitude/Longitude Data | Loading the Latitude and Longitude Data into Power BI ; Removing Duplicate Postcodes and Creating a Relationship ; Combining Columns ; Showing Latitude and Longitude in Maps | |
| MapBox Visual (8 pages) | Introduction | Getting the MapBox Custom Visual |
| Enabling your Map with a Token | ||
| Creating a Map | Searching for a Place (Geocoding) ; Choosing the Map Style | |
| Selecting Points | Using the Lasso and Polygon | |
| Different Types of Map | Circle Maps ; Cluster Maps ; Heat Maps ; Switching Maps on Zoom | |
| Shape Maps (7 pages) | Introduction | Enabling Shape Maps |
| Finding a Map | Some Useful Terms ; Finding a topoJSON File | |
| Creating the Shape Map | Starting the Map ; Assigning a Map File ; Reviewing Map Keys ; Creating Map Keys in your Data ; Setting the Location Field ; Zoom Settings ; Setting a Tooltip ; Setting the Default Colour ; Setting Map Colours | |
| Synoptic Panels and Maps (8 pages) | Overview | |
| Creating the Image | Creating a Base Image ; Adding this Image in Synoptic Designer ; Filling In your Picture ; Naming the Areas ; Saving your Image | |
| Creating the Visual | Tying your Data to your Map ; Importing the Synoptic Panel Custom Visual ; Creating a Synoptic Panel | |
| Formatting the Visual | Colour Saturation ; Settig Colour Thresholds (“States”) | |
| Buttons and Actions (4 pages) | Overview | Types of Button |
| Assigning Actions to Shapes or Images | ||
| Creating Buttons | Adding Buttons ; Default, Hover and Selection Effects ; Formatting Buttons | |
| Bookmarks (6 pages) | Example of Bookmarks | |
| Preparing to Use Bookmarks | Naming your Visuals in the Selection Pane ; Displaying the Bookmarks Pane ; Creating Bookmarks | |
| Configuring your Bookmarks | Step 1 – Data Settings ; Step 2 – Display Settings ; Step 3 – Update the Bookmark ; Step 4 - Linking to your Bookmarks | |
| Linking Back to the Home Page | Creating the Home Bookmark | |
| Uses of Bookmarks (13 pages) | Ideas in this Chapter | |
| Clearing Filters and Slicers | Creating the Slicers and Action Button ; Clearing the Slicers ; Creating and Customising your Bookmark ; Attaching the Bookmark to your Action Button | |
| Clicking on Pictures to Filter Charts | Setting the Scene ; Setting the Bookmarks to Filter ; Attaching the Bookmarks to the Images | |
| Sort Icons | Creating the Buttons ; Create the Bookmarks ; Configure the Bookmarks | |
| Tabbed Controls | Creating the Visuals ; Creating the Tabbed Effect ; Creating the Bookmarks ; Attaching the Bookmarks to the Tabs | |
| Moving between Pages | Creating the Pages ; Synchronising the Home Page Slicer ; Creating the Bookmarks ; Assigning Actions | |
| Pop-Up Buttons | Creating the Pop-Up Message ; Naming the Parts of the Pop-Up Message ; Creating the Bookmarks ; Assigning Actions to the Bookmarks | |
| Themes (4 pages) | Applying Existing Themes | |
| Using the Themes Gallery | ||
| Limitations of Themes | Themes don’t Override Custom Colours ; Custom Visuals don’t Support Themes | |
| Creating Custom Themes (8 pages) | Overview | |
| Understanding the Parts of a Theme | Foreground and Background Colours ; The TableAccent Property ; The DataColors property | |
| Understanding JSON | JSON is just like XML ; Objects and Name/Value Pairs ; Arrays | |
| Creating a JSON File | Creating a New File ; Adding a Line ; Adding an Array ; Changing Colours ; Saving your Changes | |
| Extending Custom Themes (6 pages) | Syntax of Visual Styles | CardName Values |
| Examples of Visual Styles | Example: Setting the Default Font ; Example: Setting the Default Legend ; Example: Setting the Default Title ; Example: Pie Chart Labels ; Example: Setting a Border | |
| This Courseware’s Theme | ||
| Templates (6 pages) | Creating a Template | |
| Using a Template | ||
| Ideas for Templates | Themes ; Data ; Parameterised Data Connections ; Parameters for Filtering ; Corporate Logos ; Layouts | |
|
Power BI Mobile
(8 pages)
Download this chapter |
What is Power BI Mobile? | |
| Limitations of Mobile Reports | Problems with Titles ; Portrait and Landscape Issues ; Standard Formatting across Report Types ; Grid Mesh Size | |
| Creating Mobile Reports | ||
| Using Mobile Reports | Using Drill-Down on Mobile Phones | |
| Mobile Dashboards | Designing Mobile Dashboards ; Using Mobile Dashboards | |
| Question and Answer (3 pages) | Q&A in Power BI Desktop | |
| Q & A in Power BI Service | ||
| Synonyms | ||
| Analysing in Excel (5 pages) | Pivoting Report Data | |
| Preparation in Power BI Desktop | Tidying your Data Model ; Creating a Measure Table ; Creating Measures | |
| Preparation in Power BI Service | ||
| Analyzing in Excel | ||
| Row-Level Security (10 pages) | Overview of Row-Level Security | Limitations and Constraints |
| How Roles Work | ||
| Roles in Power BI Desktop | Creating Roles ; Testing Roles | |
| Roles in Power BI Service | Configuring the App Workspace ; Assigning Users to Roles ; Testing your Roles | |
| Dynamic Security | Creating a Permissions Table ; Creating a Role | |
| Creating Groups of Users | Creating the Tables ; Linking the Tables ; Creating the Role |
DAX courseware
Here is a list of all of our DAX manual chapters (you can download the ones marked):
| Chapter | Sections | Subsections |
|---|---|---|
| Introducing DAX (1 page) | Where is DAX Used? | |
| Creating a Data Model (6 pages) | The Woozle Data Model | The Geographical Tables ; The Product Category Tables ; Some Terms |
| Creating Reports in Power BI Desktop | Loading the Data Tables ; Creating Visuals in Power BI | |
| Creating Reports in PowerPivot | Loading Tables into Data Models in PowerPivot ; Creating Pivot Tables in PowerPivot | |
| Writing DAX (6 pages) | Calculated Columns | Referring to Columns/Fields ; Referring to Tables ; Fully Qualified References |
| Writing DAX | Laying out your Formulae ; Using Multiple Lines ; DAX Formatter ; Comments | |
| DAX Syntax | Functions and Arguments ; Mathematical Operators ; Concatenating Text | |
| DAX Studio (3 pages) | Using DAX Studio | Installing DAX Studio ; Connecting to your Data Model |
| Four Uses of DAX Studio | Use 1 - Getting at DAX Functions ; Use 2 – Writing DAX Queries ; Use 3 – Better Formatting ; Use 4 – Saving DAX | |
|
Testing Conditions
(3 pages)
Download this chapter |
Testing Single Conditions | The IF Function ; Relational Operators ; Logical Operators ; Using IN to Test if Items Exist in a List |
| The SWITCH Function | ||
| Linking Tables (4 pages) | The RELATED Function | |
| Dealing with Blanks | BLANK Arithmetic | |
| The RELATEDTABLE Function | ||
| Working with Data Types (5 pages) | DAX Data Types | |
| Scalar Date Functions | ||
| Scalar Text Functions | Finding and Replacing Text ; Converting Text ; Formatting Text ; Getting the Length of and Extracting Text | |
| Scalar Number Functions | ||
| Trapping Errors (2 pages) | Using the DIVIDE Function | |
| Using IFERROR | Generating your Own Errors using ERROR | |
| Creating and Storing Measures (6 pages) | Implicit Measures | Recognising Implicit Measures ; Showing Implicit Measures in PowerPivot |
| Creating Measures | Creating Measures in Power BI Desktop ; Creating Measures in PowerPivot | |
| Using a Measures Table | Creating a Measures Table in Power BI Desktop ; Creating a Measures Table in PowerPivot | |
| Aggregating Data (6 pages) | Automatically Aggregating Data | Choosing Automatic Measures in PowerPivot ; Changing Automatic Measures in Power BI |
| DAX Aggregation Functions | ||
| Aggregating Expressions | The Problem ; Why the Simple Solution Won’t Work ; The Answer – X-Suffix Functions ; Syntax of AggregateX Functions | |
| Calculating Ratios | Counting Rows using the COUNTROWS Function ; Creating Ratios: the Fields Needed ; The Final Pivot Table ; Summing Ratios Wouldn’t Work | |
| Filter Context (5 pages) | Our Simple Example | |
| How Filter Context Works | What We’re Working Towards ; Step 1 – Assembling the Data ; Step 2 – Working out the Filter Context ; Step 3 – Getting the Filtered Data for the Context ; Step 4 – Aggregating the Data | |
| Row Context (3 pages) | Row Context for Calculated Columns | |
| Iterator Functions | Normal Aggregate Functions Use Filter Context ; Iterator Functions Use Row Context | |
| The CALCULATE Function (9 pages) | Syntax of the CALCULATE Function | |
| Removing a Constraint | Our Example ; A Quick Note on Ratios ; The Formula for this Example ; How this Works | |
| Removing Multiple Constraints | Using Multiple ALL Functions ; Using ALLEXCEPT | |
| Replacing a Constraint | Filter Context Revisited – Column Storage ; How Replacing Filter Context Really Works | |
| Using ALLSELECTED | ||
| Context Transition | ||
| The VALUES Function (7 pages) | Introducing the VALUES Function | |
| Detecting the Number of Values | The HASONEVALUE Function ; Using COUNTROWS to Count VALUES | |
| Using VALUES to Modify Filter Context | The Obvious Way doesn’t Work ; Using the VALUES Function to Solve the Problem | |
| Parameter Tables | ||
| Dynamic Titles using ISFILTERED | Dynamic Titles for Single-Value Filters ; Dynamic Titles for Multi-Value Filters | |
| Variables (5 pages) | Referring to Measures within Measures | |
| Creating Variables | ||
| Lazy Evaluation and its Implications | ||
| Storing Tables in Variables | ||
| Debugging using Variables | ||
| The FILTER Function (7 pages) | The Basic FILTER Function | Using CALCULATE as an Alternative to FILTER |
| FILTER as an Iterator Function | Starting Off – Our Example ; Getting the Filter Context ; Row Context within this Filter Context ; Deriving the Final Result | |
| Multiple Conditions in FILTER Functions | Combining Conditions using && and || ; Combining Conditions using AND / OR ; Combining Conditions by Nesting the FILTER Function | |
| Using ALL and FILTER | ||
| FILTER and CALCULATE aren’t Equivalent | ||
| The EARLIER Function (5 pages) | Case Study of the EARLIER Function | Our Example ; An Outline of the EARLIER Function ; Row Context within Filter Context ; The Final Formula |
| Another Example – Running Totals | ||
| Using Variables instead of the EARLIER Function | Ranking Sales using Variables ; Running Totals using Variables | |
| Banding (3 pages) | What is Banding? | Creating and Loading a Banding Table |
| Creating a Banding Formula | ||
| Sorting the Bands | ||
| Ranking (5 pages) | The RANKX Function | Syntax of the Rank Function ; Intellisense for the RANKX Function |
| RANKX for Calculated Columns | ||
| Ranking Measures (Existing Columns) | The Most Common Problem – Omitting ALL ; The Solution using ALL | |
| Ranking using Aggregate Calculations | RANKX is an Iterator Function | |
| Ranking with Context | Suppressing Totals ; Ranking over Selected Items | |
| Calendar Tables (6 pages) | What are Calendar Tables? | Requirements for a Calendar Table ; Why you Need a Calendar Table |
| Creating a Calendar | Step 1 – Getting the Calendar Data ; Step 2 – Loading and Linking to the Calendar Table ; Step 3 – Mark your Table as a Date Table ; Step 4 - Setting a Sort Month | |
| Date Granularity | ||
| Special Days | ||
|
Multiple Date Tables
(8 pages)
Download this chapter |
The Problem, and Two Solutions | Repeat the Table or the Relationship? |
| Solution One: Duplicate the Calendar Table | Step 1 - Importing and Linking to the Calendar Tables ; Step 2 – Renaming Tables and Fields ; Step 3 – Using your Multiple Calendars | |
| Solution Two: Duplicate the Relationship | Creating the Duplicate Relationships ; The CALCULATETABLE Function ; The USERELATIONSHIP Function ; Our Measures | |
| The CROSSFILTER Function | One Solution – Change the Relationships Permanently ; A Better Solution – Use DAX to Temporarily Cross-Filter ; Multiple Cross-Filtering | |
| How Time Intelligence Functions Work (4 pages) | Our Example | |
| Filter Context Reminder | ||
| Year-to-Date using CALCULATE | ||
| Year-to-Date using Time-Intelligence Functions | The DATESYTD Function ; The TOTALYTD Function | |
| DAX Date Functions (10 pages) | Contents of the Chapter | |
| Period to Date | Using DATESYTD, DATESQTD and DATESMTD ; Using TOTALYTD, TOTALQTD and TOTALMTD | |
| Changing the Financial Year End | Functions with a Year End Date Argument ; Displaying Data for Different Financial Year Ends | |
| Referencing Previous Periods | The SAMEPERIODLASTYEAR Function ; The DATEADD Function | |
| Parallel Periods | ||
| Moving Averages | Definition of a Moving Average ; Moving Average using DATESINPERIOD and LASTDATE ; Moving Average using DATESBETWEEN, NEXTDATE and LASTDATE | |
| Semi-Additive Measures | Useful Semi-Additive Functions ; Using the FIRSTDATE and LASTDATE Functions ; Using FIRSTNONBLANK and LASTNONBLANK ; Detecting Relationships in FIRSTNONBLANK / LASTNONBLANK | |
| Hierarchies (7 pages) | Calculating Percentages | The Problem ; A Solution |
| Parent-Child Hierarchies | What is a Parent-Child Hierarchy? ; Step 1 – Create a List of Parent Ids (the PATH Function) ; Step 2 – Working out the Path Depth (the PATHLENGTH Function) ; Step 3 – Create a Measure Showing the Number of Levels ; Step 4 - Finding Managers at Each Level (PATHITEM and LOOKUPVALUE) ; Step 5 – Creating a Hierarchy ; Step 6 – Checking your Relationships ; Step 7 – Dealing with Blanks | |
| Introducing DAX Queries (5 pages) | Overview of DAX Queries | |
| DAX Queries are like SQL | ||
| Where to Write DAX Queries | ||
| Debugging using DAX Queries | A Worked Example ; Debugging using Table Variables ; Using Queries to Debug | |
| Syntax of DAX Queries (12 pages) | Listing Tables (EVALUATE) | Listing All Rows ; Ordering Rows ; Starting at a Particular Row ; Returning N Rows Only ; Sampling Rows |
| Summarising | Summarisation Syntax ; Example of Summarising by a Single Column ; Example of Summarizing by Multiple Columns ; Using SUMMARIZECOLUMNS ; Using ROLLUP to get All Combinations | |
| Filtering in DAX Queries | Example: Summarising Sales for Birds Only ; Example: Summarising Centre Size for Selected Towns | |
| Adding Columns | Example: Counting Rows ; Example: Purchase Statistics by Town | |
| Using SELECTCOLUMNS | Adding Columns using SELECTCOLUMNS | |
| Defining Measures and Variables | Defining Measures ; Defining Variables | |
| Using ROW to Display One Row of Data | ||
| Combining Table Results using GENERATE and GENERATEALL |
Notes:
- 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.
- 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