3D. Write DAX formulas for Power BI Desktop models

Question 1

What types of calculations can you add to a semantic model by using DAX?

Answer 1

• Calculated tables
• Calculated columns
• Measures

Question 2

What are calculated tables, and what should you remember about them?

Answer 2

You can write a DAX formula to add a calculated table to your model. The formula can duplicate or transform existing model data, or create a series of data, to produce a new table.

Calculated table data is always imported into your model, so it increases the model storage size and can prolong data refresh time.

A calculated table can’t connect to external data; you need to use Power Query to accomplish that task.

Question 3

In what ways can calculated tables be useful?

Answer 3

• Date tables
• Role-playing dimensions
• What-if analysis

Question 4

What are date tables?

Answer 4

Date tables are required to apply special time filters known as time intelligence. DAX time intelligence functions only work correctly when a date table is set up. When your source data doesn’t include a date table, you can create one as calculated tables by using the CALENDAR or CALENDARAUTO DAX functions.

Question 5

What is a role-playing dimension?

Answer 5

When two model tables have multiple relationships, it could be because your model has a role-playing dimension. For example, if you have a table named Sales that includes two date columns, OrderDateKey and ShipDateKey, both columns are related to the Date column in the Date table. In this case, the Date table is described as a role-playing dimension because it could play the role of order date or ship date.

Question 6

How do you make use of an inactive relationship between two tables?

Answer 6

The active relationship is used by default to propagate filters. Any remaining relationships between the two tables are inactive. In a model diagram, the relationships are represented as dashed lines.

Inactive relationships are only used when they’re expressly requested in a calculated formula by using the USERELATIONSHIP DAX function.

Question 7

What is an alternative design to using multiple relationships between two tables when creating role-playing dimensions?

Answer 7

A better design could be to copy the dimension table (by creating a calculated table), each with their own active relationship to the fact table.

Question 8

What is what-if analysis, how do they work, and how do they connect to tables in the model?

Answer 8

Power BI Desktop supports a feature called What-if parameters. When you create a what-if parameter, a calculated table is automatically added to your model.

What-if parameters allow report users to select or filter by values that are stored in the calculated table. Measure formulas can use selected value(s) in a meaningful way. For example, a what-if parameter could allow the report user to select a hypothetical currency exchange rate, and a measure could divide revenue values (in a local currency) by the selected rate.

Notably, what-if calculated tables aren’t related to other model tables because they’re not used to propagate filters. For this reason, they’re sometimes called disconnected tables.

Question 9

What are calculated columns?

Answer 9

You can write a DAX formula to add a calculated column to any table in your model. The formula is evaluated for each table row and it returns a single value.

Question 10

How do calculated columns relate to storage mode?

Answer 10

• When added to an Import storage mode table, the formula is evaluated when the semantic model is refreshed, and it increases the storage size of your model.
• When added to a DirectQuery storage mode table, the formula is evaluated by the underlying source database when the table is queried.

Question 11

What is a measure?

Answer 11

You can write a DAX formula to add a measure to any table in your model. The formula is concerned with achieving summarization over model data.

Similar to a calculated column, the formula must return a single value.

Question 12

How do calculated columns and measures differ in terms of when they’re evaluated?

Answer 12

• Calculated columns are valuated at data refresh time
• Measures are evaluated at query time. Their results are never stored in the model.

Question 13

What are “implicit” measures?

Answer 13

Implicit measures are columns that can be summarized by visuals in simplistic ways, like count, sum, minimum, maximum, and so on. You can identify implicit measures in the Fields pane because they’re shown with the sigma symbol ( ∑ ).

To be clear, explicit measures are model calculations that are written in DAX and are commonly referred to as simply measures.

Question 14

What is a DAX formula?

Answer 14

Each model calculation type, calculated table, calculated column, or measure is defined by its name, followed by the equals symbol (=), which is then followed by a DAX formula.

A DAX formula consists of expressions that return a result.

Question 15

What can DAX formulas return?

Answer 15

The result is either a table object or a scalar value.

Calculated table formulas must return a table object.

Calculated column and measure formulas must return a scalar value (single value).

Question 16

What is a DAX function?

Answer 16

Similar to Microsoft Excel, DAX is a functional language meaning that formulas rely on functions to accomplish specific goals. Typically, DAX functions have arguments that allow passing in variables. Formulas can use many function calls and will often nest functions within other functions.

In a formula, function names must be followed by parentheses. Within the parentheses, variables are passed in.

Question 17

What are DAX operators?

Answer 17

Formulas also rely on operators, which can perform arithmetic calculations, compare values, work with strings, or test conditions.

Question 18

What types of model objects can DAX formulas refer to?

Answer 18

• Tables
• Columns
• Measures

A formula can’t refer to a hierarchy or a hierarchy level. (Recall that a hierarchy level is based on a column, so your formula can refer to a hierarchy level’s column.)

Question 19

Typically, table names are enclosed in single quotation marks in DAX formulas. However, under what conditions can the single quotation marks be omitted?

Answer 19

• The table name does not include embedded spaces.
• The table name isn’t a reserved word that’s used by DAX. All DAX function names and operators are reserved words. Date is a DAX function name, which explains why, when you are referencing a table named Date, that you must enclose it within single quotation marks.

Question 20

In a DAX formula, what is fully qualified column, and why are they good practice?

Answer 20

When you reference a column in a formula, the column name must be enclosed within square brackets. Optionally, it can be preceded by its table name.

Because column names are unique within a table but not necessarily unique within the model, you can disambiguate the column reference by preceding it with its table name. This disambiguated column is known as a fully qualified column. Some DAX functions require passing in fully qualified columns.

Question 21

In DAX formulas, both columns and measures are enclosed in square brackets–so how can you tell them apart?

Answer 21

If you’re a DAX beginner, the fact that column and measure references are always enclosed within square brackets can cause confusion when you’re trying to read a formula. However, as you become proficient with DAX fundamentals, you’ll be able to determine which type of object it is because, in DAX formulas, columns, and measures are used in different ways.

Question 22

Is it recommended to precede measures with their table name in DAX formulas? Why, or why not?

Answer 22

It’s possible to precede a measure reference with its table name. However, measures are a model-level object. While they’re assigned to a home table, it’s only a cosmetic relationship to logically organize measures in the Fields pane.

Therefore, while we recommend that you always precede a column reference with its table name, the inverse is true for measures: We recommend that you never precede a measure reference with its table name.

Question 23

What is whitespace in the context of DAX formulas?

Answer 23

Whitespace refers to characters that you can use to format your formulas in a way that’s quick and simple to understand. Whitespace characters include:

• Spaces
• Tabs
• Carriage returns

Question 24

What is the recommended way to use whitespace in DAX formulas?

Answer 24

Whitespace is optional and it doesn’t modify your formula logic or negatively impact performance. We strongly recommend that you adopt a format style and apply it consistently, and consider the following recommendations:

• Use spaces between operators.
• Use tabs to indent nested function calls.
• Use carriage returns to separate function arguments, especially when it’s too long to fit on a single line. Formatting in this way makes it simpler to troubleshoot, especially when the formula is missing a parenthesis.
• Err on the side of too much whitespace than too little.

Question 25

How are model data types defined for relevant model objects?

Answer 25

• Column data types are defined in Power Query
• In the case of calculated columns, it’s inferred from the formula.
• Measure data types, similar to calculated column data types, are inferred from the formula.

Question 26

How do model data types compare with DAX data types?

Answer 26

Whole number 64-bit integer
Decimal number 64-bit real
Boolean Boolean
Text String
Date Date/time
Currency Currency
N/A BLANK

Question 27

Describe the BLANK DAX data type.

Answer 27

The BLANK data type deserves a special mention. DAX uses BLANK for both database NULL and for blank cells in Excel. BLANK doesn’t mean zero. Perhaps it might be simpler to think of it as the absence of a value.

Two DAX functions are related to the BLANK data type: the BLANK DAX function returns BLANK, while the ISBLANK DAX function tests whether an expression evaluates to BLANK.

Question 28

While many DAX functions are familiar to Excel users, some DAX functions do not exist in Excel because they’re specific to data modeling. Which types of functions?

Answer 28

• Relationship navigation functions
• Filter context modification functions
• Iterator functions
• Time intelligence functions
• Path functions

Question 29

Describe the IF function.

Answer 29

The IF DAX function tests whether a condition that’s provided as the first argument is met. It returns one value if the condition is TRUE and returns the other value if the condition is FALSE. The function’s syntax is:

IF(<logical_test>, <value_if_true>[, <value_if_false>])</value_if_false></value_if_true></logical_test>

If logical_test evaluates to FALSE and value_if_false isn’t provided, the function will return BLANK.

Question 30

Describe the DISTINCTCOUNT function.

Answer 30

You can use the DISTINCTCOUNT DAX function to count the number of distinct values in a column. This function is especially powerful in an analytics solution. Consider that the count of customers is different from the count of distinct customers. The latter doesn’t count repeat customers, so the difference is “How many customers” compared with “How many different customers.”

Question 31

Describe the DIVIDE function and the use of “alternate result.”

Answer 31

You can use the DIVIDE DAX function to achieve division. You must pass in numerator and denominator expressions. Optionally, you can pass in a value that represents an alternate result. The DIVIDE function’s syntax is:

DIVIDE(<numerator>, <denominator>[, <alternate_result>])</alternate_result></denominator></numerator>

The DIVIDE function automatically handles division by zero cases. If an alternate result isn’t passed in, and the denominator is zero or BLANK, the function returns BLANK. When an alternate result is passed in, it’s returned instead of BLANK.

Question 32

When is it recommended to use the DIVIDE function, and why?

Answer 32

We recommend that you use the DIVIDE function whenever the denominator is an expression that could return zero or BLANK.

This function is convenient because it saves your expression from having to first test the denominator value. The function is also better optimized for testing the denominator value than the IF function. The performance gain is significant because checking for division by zero is expensive. What’s more, using the DIVIDE function results in a more concise and elegant expression.

Question 33

When is it better to use the / operator instead of the DIVIDE function, and why?

Answer 33

In the case that the denominator is a constant value, we recommend that you use the divide operator (/). In this case, the division is guaranteed to succeed, and your expression will perform better because it will avoid unnecessary testing.

Question 34

Which are the arithmetic operators?

Answer 34

+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponentiation

Question 35

Which are the comparison operators?

Answer 35

The following table lists the comparison operators, which are used to compare two values. The result is either TRUE or FALSE.

= Equal to
== Strict equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to

Question 36

What is the use of the strict equal to operator (==)?

Answer 36

All comparison operators, except strict equal to (==), treat BLANK as equal to the number zero, an empty string (“”), the date December 30, 1899, or FALSE. It means that the expression [Revenue] = 0 will be TRUE when the value of [Revenue] is either zero or BLANK. In contrast, [Revenue] == 0 is TRUE only when the value of [Revenue] is zero.

Question 37

Which is the text concatenation operator?

Answer 37

Use the ampersand (&) character to connect, or concatenate, two text values to produce one continuous text value. For example, consider the following calculated column definition:

Model Color = ‘Product’[Model] & “-“ & ‘Product’[Color]

Question 38

Which are the logical operators?

Answer 38

&& Creates an AND condition between two expressions where each has a Boolean result. If both expressions return TRUE, the combination of the expressions also returns TRUE; otherwise the combination returns FALSE.

|| (double pipe) Creates an OR condition between two logical expressions. If either expression returns TRUE, the result is TRUE; only when both expressions are FALSE is the result FALSE.

IN Creates a logical OR condition between each row that is being compared to a table. Note: The table constructor syntax uses braces.

NOT Inverts the state of a Boolean expression (FALSE to TRUE, and vice versa).

Question 39

Give an example of how to use the IN operator.

Answer 39

An example that uses the IN logical operator is the ANZ Revenue measure definition, which uses the CALCULATE DAX function to enforce a specific filter of two countries: Australia and New Zealand.

ANZ Revenue =
CALCULATE(
[Revenue],
Customer[Country-Region] IN {
“Australia”,
“New Zealand”
}
)

Question 40

What is the operator precedence order?

Answer 40

^ Exponentiation
- Sign (as in -1)
* and / Multiplication and division
NOT NOT
+ and - Addition and subtraction
& Concatenation of two strings of text
=,==,<,>,<=,>=,<> Comparison

When the operators have equal precedence value, they’re ordered from left to right.

In general, operator precedence is the same as what’s found in Excel. If you need to override the evaluation order, then group operations within parentheses.

Question 41

How does DAX handle differences in data types, and potential compatibility issues?

Answer 41

When writing a DAX formula that uses operators to combine different data types, you don’t need to explicitly convert types. Usually, DAX automatically identifies the data types of referenced model objects and performs implicit conversions where necessary to complete the specified operation.

However, some limitations might exist on the values that can be successfully converted. If a value or a column has a data type that’s incompatible with the current operation, DAX returns an error. For example, the attempt to multiply a date value will create an error because it isn’t logical.

Question 42

How is BLANK treated in DAX formulas?

Answer 42

BLANK is handled differently, depending on the operator that is used. It’s handled similar to how Excel treats BLANK, but differently to how databases (SQL) treat NULL. BLANK is treated as zero when acted on by arithmetic operators and as an empty string when concatenated to a string.

Question 43

What are DAX variables?

Answer 43

You can declare DAX variables in your formula expressions. When you declare at least one variable, a RETURN clause is used to define the expression, which then refers to the variables.

Question 44

What are the benefits of using variables
?

Answer 44

We recommend that you use variables because they offer several benefits:

• Improving the readability and maintenance of your formulas.
• Improving performance because variables are evaluated once and only when or if they’re needed (even if referred to several times in the RETURN expression).
• Allowing (at design time) straightforward testing of a complex formula by returning the variable of interest.