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.
Table of Contents (18 chapters)
1
Section 1: Introduction to DAX for the BI Pro
7
Section 2: Understanding DAX Functions and Syntax
14
Section 3: Taking DAX to the Next Level

Working with data types and operators

In DAX, you define the data type for columns of data in a table. In this section, we will look at the different data types that are available and delve into the implicit data type conversions that take place when data is used in a DAX expression. We will also look at the different groups of available operators.

Data types

Choosing the correct data type when building your data model helps to ensure that the size of your model is kept to a minimum. It can also help with performance when it comes to refreshing the data in your model.

When you load new data into your model, the modeling engine will attempt to pick the most efficient data type for a column, based on the values that it is importing for that column. However, it is worth checking the data types that it selects, as it may not always choose the most appropriate data type for your data needs. For example, if a column currently contains only integer numbers, the modeling engine will pick the Whole Number data type. If this column subsequently contains fractional values, then the fractional part of these numbers will be lost when the data is imported. Worse still, if the column subsequently contains non-numeric data in the column, then you will get errors when the data is refreshed.

You should always use the correct data type, as some DAX functions have special data type requirements. Although DAX may implicitly convert a data type for you, there are some cases where it will not.

Implicit conversions are described later in this article. Table 1-2 gives details of the different data types available in DAX:

Data Type

Stored As

Comments

Whole Number

64-bit (8 byte) integer value

Integers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

Decimal Number

64-bit (8 byte) real number

Negative numbers between -1.79E +308 and -2.23E -308, zero, and positive number between 2.23E -308 and 1.79E + 308; the number of significant digits is limited to 15 decimal digits, with the separator occurring anywhere within the number.

Currency

(Fixed Decimal Number in Power BI)

64-bit (8 byte) real number

Numbers that have four decimal digits of fixed precision between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.

Date/Time

64 bit (8 byte) real number

Underneath the covers, the Date/Time value is stored as a Decimal Number type. Supports dates from March 1, 1900 through to December 31, 9999.

Text

A Unicode character string

Represents strings, numbers, or dates in a text format; maximum length is 268,435,456 Unicode characters or 536,870,912 bytes.

True/False

Boolean

A Boolean value that is either True or False.

Blank

N/A

Creates a blank with the BLANK function, and verify blanks with ISBLANK.

Table

N/A

Represents a table in the data model.

Table 1-2: Data types in DAX

DAX functions have specific requirements for the type of data used for inputs and outputs. If the data in a column passed as an argument is not compatible with the data type required by the function, DAX will try to implicitly convert it into the required data type. If this is not possible, it will return an error.

The type of implicit conversion that DAX performs is determined by the operator, it will convert the data into the type required before it performs the requested operation. Tables 1-3 through to 1-6 list the operators and show the implicit conversion that takes place when the data type in the row is combined with the data type in the column.

Table 1-3 shows the implicit conversion that takes place when a value with the data type in the row is added to a value with the data type in the column:

Addition (+)

Whole

Currency

Decimal

Date/Time

Whole

Whole

Currency

Decimal

Date/Time

Currency

Currency

Currency

Decimal

Date/Time

Decimal

Decimal

Decimal

Decimal

Date/Time

Date/Time

Date/Time

Date/Time

Date/Time

Date/Time

Table 1-3: Addition

Table 1-4 shows the implicit conversion that takes place when a value with the data type in the row is subtracted from a value with the data type in the column:

Subtraction (-)
Row – Column

Whole

Currency

Decimal

Date/Time

Whole

Whole

Currency

Decimal

Decimal

Currency

Currency

Currency

Decimal

Decimal

Decimal

Decimal

Decimal

Decimal

Decimal

Date/Time

Date/Time

Date/Time

Date/Time

Date/Time

Table 1-4: Subtraction

Table 1-5 shows the implicit conversion that takes place when a value with the data type in the row is multiplied by a value with the data type in the column:

Multiplication (*)

Whole

Currency

Decimal

Date/Time

Whole

Whole

Currency

Decimal

Whole

Currency

Currency

Decimal

Currency

Currency

Decimal

Decimal

Currency

Decimal

Decimal

Table 1-5: Multiplication

Table 1-6 shows the implicit conversion that takes place when a value with the data type in the row is divided by a value with the data type in the column:

Division (/)
Row / Column

Whole

Currency

Decimal

Date/Time

Whole

Decimal

Currency

Decimal

Decimal

Currency

Currency

Decimal

Currency

Decimal

Decimal

Decimal

Decimal

Decimal

Decimal

Date/Time

Decimal

Decimal

Decimal

Decimal

Table 1-6: Division

In addition to the implicit conversions of numeric types shown in the preceding tables, DAX will automatically convert numbers into strings and strings into numbers depending on the requirements of the operator.

For the concatenation operator (&), DAX will convert numeric values into string values:

Measure 1-1 = 2 & 3

In this example, the DAX measure will evaluate to the string value, "23".

For an arithmetic operator such as addition (+), string values will be converted into a numeric value where possible:

Measure 1-2 = "2" + "3"

In this example, the DAX measure will evaluate to the numeric value 5.

However, there is the potential for errors to occur when allowing for automatic conversion as described. For example, where you are passing string values to an arithmetic operator that cannot be converted into a number, your expression will generate an error. Therefore, you must ensure that correct data types are used for the columns that are used with operators. Exception handling should be done if there is any possibility of errors occurring.

Operators

There are four groups of operators in DAX:

  • Arithmetic
  • Comparison
  • Concatenation
  • Logical

Table 1-7 shows the different types of operator available within the arithmetic group, along with an example illustrating typical use:

Operator

Meaning

Example

+

Addition

3 + 7 = 10

-

Subtraction or sign

10 - 7 = 3

*

Multiplication

10 * 7 = 70

/

Division

10 / 5 = 2

^

Exponentiation

3 ^ 4 = 81

Table 1-7: DAX arithmetic operators

When using arithmetic operators it is important to consider the order in which they need to be applied. If necessary, use parentheses to override the precedence of an operator. Table 1-8 shows the order of precedence for each of the different DAX arithmetic operators:

Operator

Description

^

Exponentiation

-

Sign

* and /

Multiplication and division

+ and -

Addition and subtraction

Table 1-8: DAX arithmetic operator precedence

The following gives an example of where parenthesis can be used to override the precedence of an operator:

5*2+6 = 16

Here, the 5 is multiplied by the 2 to give 10, before the 6 is added to give 16. The multiplication operator (*) has higher precedence than the addition operator (+), so that part of the calculation is calculated first.

However, take a look at this example:

5*(2+6) = 40

Here, the use of the parentheses around 2+6 gives it higher precedence and causes it to be calculated before the result is multiplied by 5.

Table 1-9 shows the different types of comparison operators available, with an example illustrating the operator being used:

Operator

Meaning

Example

=

Equal to

[Firstname] = "Ian"

==

Strictly equal to

[Number] == 0
true only when number equals 0
and false if blank

>

Greater than

[Number] > 100

<

Less than

[Number] < 100

>=

Greater than or equal to

[Number] >= 100

<=

Less than or equal to

[Number] <= 100

<>

Not equal to

[Firstname] <> "Ian"

Table 1-9: DAX comparison operators
When using comparison expressions, you should consider the following points:
  • Boolean values are treated as greater than string values.
  • String values are treated as greater than numeric or date/time values.
  • Numeric and date/time values are treated the same.

Table 1-10 shows the concatenation operator, with some examples illustrating how it is used:

Operator

Meaning

Example

&

Joins two values together to form one text value

"abcd" & "efg" = "abcdefg"
2 & 3 = "23"

Table 1-10: DAX concatenation operator

It is important to note that, as we have seen in the previous section on data types, when using the concatenation operator, DAX will implicitly convert numeric values to string values.

Table 1-11 shows the different types of logical operators available, with examples of each operator being used:

Operator

Meaning

Example

&&

Logical AND: If both expressions are TRUE, return TRUE; otherwise return FALSE.

(true) && (true) = true
(true) && (false) = false

||

Logical OR: If either expression is TRUE, return TRUE; when both expressions are FALSE, return FALSE.

(true) || (true) = true
(true) || (false) = true
(false) || (true) = true
(false) || (false) = false

IN

Logical OR: Creates a logical OR condition between each value included in a list of values.

Channel(ChannelName) IN (‘Store’, ‘Online’, ‘Catalog’)

Table 1-11: DAX logical operators

In addition to the preceding logical operators, DAX also has the logical AND and OR functions that replicate the functionality of the AND operator (&&) and OR operator (||) respectively.

The advantage of using these functions over the equivalent operators in a complex expression is that it is easier to format and read the code. However, one drawback is that the functions only accept two arguments, restricting you to comparing two conditions only. To be able to compare multiple conditions, you will need to nest the functions. In this case, it might be better to use the AND operator (&&) instead.

The following gives an example of the syntax for the AND function:

Measure 1-3 =
IF (
AND (
20 > 10,
-20 < -10
),
"All true",
"One or more false"
)

The following gives an example showing the syntax of the AND function nested to compare three conditions:

Measure 1-4 =
IF (
AND (
AND (
10 > 9,
5 < 10
),
20 > 10
),
"All true",
"One or more false"
)

The following gives an alternative example of the one given, using the equivalent AND operator (&&):

Measure 1-5 =
IF (
10 > 9
&& 5 < 10
&& 20 > 10,
"All true",
"One or more false"
)

Any column in a table can have blank values, which are the result of the data source containing NULL in values. How a blank value affects the result of a DAX expression depends on the data type expected and the operator being used. In some instances, a blank value will be converted into a zero or an empty string, while in others, it will propagate through as a blank. Table 1-12 shows how different DAX operators handle blank values:

Expression

DAX

BLANK + BLANK

BLANK

BLANK & "Hello"

Hello

BLANK + 2

2

BLANK * 2

BLANK

2 / BLANK

Infinity

0 / BLANK

NaN

BLANK / BLANK

BLANK

FALSE OR BLANK

FALSE

FALSE AND BLANK

FALSE

TRUE OR BLANK

TRUE

TRUE AND BLANK

FALSE

BLANK OR BLANK

BLANK

BLANK AND BLANK

BLANK

Table 1-12: Handling blank values in DAX

The BLANK data type represents nulls, blank values, empty cells, and missing values. The BLANK function is used to generate blanks, while the ISBLANK function is used to verify a blank value.