Book Image

Hands-On Business Intelligence with DAX

By : Ian Horne
Book Image

Hands-On Business Intelligence with DAX

By: Ian Horne

Overview of this book

Data Analysis Expressions (DAX) is known for its ability to increase efficiency by extracting new information from data that is already present in your model. With this book, you’ll learn to use DAX’s functionality and flexibility in the BI and data analytics domains. You’ll start by learning the basics of DAX, along with understanding the importance of good data models, and how to write efficient DAX formulas by using variables and formatting styles. You’ll then explore how DAX queries work with the help of examples. The book will guide you through optimizing the BI workflow by writing powerful DAX queries. Next, you’ll learn to manipulate and load data of varying complexity within Microsoft products such as Power BI, SQL Server, and Excel Power Pivot. You’ll then discover how to build and extend your data models to gain additional insights, before covering progressive DAX syntax and functions to understand complex relationships in DAX. Later, you’ll focus on important DAX functions, specifically those related to tables, date and time, filtering, and statistics. Finally, you’ll delve into advanced topics such as how the formula and storage engines work to optimize queries. By the end of this book, you’ll have gained hands-on experience in employing DAX to enhance your data models by extracting new information and gaining deeper insights.

Related Content you might be interested in

Current Title:

Small book image
Hands-On Business Intelligence with DAX
Ian Horne
Jan, 2020 | 402 pages
Small book image
Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
Derek Wilson
Jan, 2017 | 372 pages
Small book image
Extreme DAX
Michiel Rozema | Henk Vlootman
Jan, 2022 | 470 pages
Small book image
Raspberry Pi 3 Projects for Java Programmers
John Sirach | Pradeeka Seneviratne | Rajdeep Chandra
May, 2017 | 286 pages
Table of Contents (18 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
1
Section 1: Introduction to DAX for the BI Pro
Section 1: Introduction to DAX for the BI Pro
Free Chapter
2
What is DAX?
What is DAX?
Introducing DAX
Working with data types and operators
Working with calculated columns and measures
Evaluation contexts – part 1
Using the CALCULATE function
Summary
3
Using DAX Variables and Formatting
Using DAX Variables and Formatting
Getting started with DAX variables
Formatting your DAX code
Debugging errors in your DAX code
Summary
4
Building Data Models
Building Data Models
Introduction to data modeling
Data modeling concepts in DAX
Getting data into your data model
Extending your data model
It's a date
Summary
5
Working with DAX in Power BI, Excel, and SSAS
Working with DAX in Power BI, Excel, and SSAS
Working with DAX in Power BI Desktop
Working with DAX in Excel Power Pivot
Working with DAX in SSAS Tabular
Summary
6
Getting It into Context
Getting It into Context
Introducing evaluation contexts – part 2
Deep diving into row context
Deep diving into filter context
Changing context using DAX functions
Summary
7
Section 2: Understanding DAX Functions and Syntax
Section 2: Understanding DAX Functions and Syntax
8
Progressive DAX Syntax and Functions
Progressive DAX Syntax and Functions
Breaking down DAX syntax
Dealing with relationships
Looking at DAX functions
Introduction to aggregation functions
Functions for parent-child hierarchies
Summary
9
Table Functions
Table Functions
Introducing table functions
Looking at table manipulation functions
Working with table functions
Summary
10
Date, Time, and Time Intelligence Functions
Date, Time, and Time Intelligence Functions
Introduction to date and time functions
Working with date and time functions
Looking at time intelligence functions
Making your data more intelligent over time
Summary
11
Filter Functions
Filter Functions
Introduction to filter functions
Filtering your data with filter functions
Summary
12
Statistical Functions
Statistical Functions
Introducing statistical functions
Calculating averages
Working with percentiles
Ranking your data
Calculating standard deviation and variance
Summary
13
Working with DAX Patterns
Working with DAX Patterns
Introducing Power BI Quick Measures
Calculating cumulative totals
Binning data using segmentation
Comparing equivalent periods
Working with mathematical patterns
Summary
14
Section 3: Taking DAX to the Next Level
Section 3: Taking DAX to the Next Level
15
Optimizing Your Data Model
Optimizing Your Data Model
Introducing the VertiPaq engine
Understanding your data model
Simplifying your data model
Creating summary tables
Summary
16
Optimizing Your DAX Queries
Optimizing Your DAX Queries
Introduction to the DAX calculation engines
Monitoring performance with DAX Studio
Using SQL Server Profiler
Using Power BI Performance Analyzer
Summary
17
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Deep diving into filter context

As we have seen already in Chapter 1, What is DAX?, the filter context can be defined as a set of filters that are applied over the tables in a data model before a DAX expression is evaluated. The context in which a DAX expression is evaluated directly affects the result that is returned. This means that the same DAX expression can return different results, depending upon the context.

Filter context exists in the following:

  • All visuals in Power BI
  • A pivot table in Excel

Filter context can be applied through use of the following:

  • Rows and columns in a visual or a pivot table
  • Slicers
  • Filters
  • Interaction with visuals (acting as filters)
  • The CALCULATE function

A DAX expression is only evaluated when all the filters coming from the context just listed have been applied to the tables in the data model. In the case of a matrix visual in Power BI or...

Previous Section
End of Section 4
Next Section