DAX

Question 1

What is the full form of DAX?

Answer 1

Data Analysis Expressions

Question 2

What is DAX?

Answer 2

DAX is a collection of constants, operators, and functions that are used to build expressions that return one or more values.

DAX is not a programming language in the traditional sense but is instead a functional language, which means that it makes calls to a function as part of an expression. The result of an expression will, depending on the function, return either a single value or a table as output. The output from an expression can be used to nest functions, by using it as the input parameter to another function.

DAX can only be used to filter or query a physical table; it cannot add, delete, or update data in a table.

Question 3

What are the Function groups in DAX?

Answer 3

• Aggregate
• Count
• Date and Time
• Time intelligence
• Information
• Logical
• Mathematical
• Statistical
• Text
• Parent/Child

Question 4

What are the 4 Operator Groups in DAX?

Answer 4

1. Arithmetic
2. Comparison
3. Concatenation
4. Logical

Question 5

What are the different types of operator available within the arithmetic group?

Answer 5

1. Addition
2. Subtraction or sign
3. Multiplication
4. Division
5. Exponentiation

Question 6

What is the order of precedence for each of the different DAX arithmetic operators?

Answer 6

1. Exponentiation
2. Sign
3. Multiplication and division
4. Addition and subtraction

Question 7

What are the different types of comparison operators?

Answer 7

1. =
2. ==
3. >
4. <
5. >=
6. <=
7. <>

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.

Question 8

what is the Concatenation operator in DAX?

Answer 8

Details in Attached image

Question 9

what are the different types of logical operators?

Answer 9

1. &&
2. ||
3. IN

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”
)

Question 10

How are BLANK values handled in DAX?

Answer 10

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: (IN THE ATTACHED IMAGE)

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.

Question 11

Difference b\w calculated columns and measures

Answer 11

At first, they may seem very similar, and indeed there are some instances where both can be used to obtain the same result. However, they are different and serve different purposes. Likewise, they also impact resources in different ways. Calculated columns allow you to extend a table in your data model by creating additional columns. Measures allow you to aggregate the values of rows in a table and take into account any current filters or slicers that are applied.

Question 12

In DAX can the calulated columns be used in defining table relationships?

Answer 12

Calculated columns can be used in any part of a report and they can be used to define relationships.

Question 13

Give some details on caculated columns ? for eg…. when are the calculated columns populated, benefits and disadvantages of calculated columns etc

Answer 13

Calculated columns are computed during a data refresh and stored in memory with the rest of your data model. This is an important point to note when you are planning and building your data model. On the one hand, with complex expressions, the time taken to compute them is at the point you refresh the data and not when you are querying the data. This can improve the user experience, especially with complex expressions, but you need to remember that each calculated column will take up space in memory. Although this might not be an issue with a smaller table, it could have a significant impact on memory use when you are dealing with large tables. If you have complex expressions behind your calculated columns, then this could also slow down the time it takes to refresh the data in your data model.

You would be well advised not to have too many calculated columns in your data model and to consider whether it would be possible to use a measure instead, especially if it does not impact the user experience too adversely.

Question 14

What are Measures in DAX?

Answer 14

Unlike calculated columns, which are evaluated row by row using the context of the current row, measures are used to summarize data by aggregating the values of rows in a table. They work within the current filter context, which means they are affected by the current filters, slicers applied, and the highlighted sections of charts or visuals.

There are two types of measures: implicit and explicit. Implicit measures are created behind the scenes when you drag a field to the Values area of the PivotTable Fields list

An explicit measure, on the other hand, is a measure that is specifically created by you.

A measure must be created if you want to conduct an operation on aggregate values instead of values on a row-by-row basis. For example, if you need to calculate the percentage ratio of two columns, you will need to create a measure that calculates the ratio based on the sum of each column. The following measure calculates the percentage of returns to sales by dividing the sum of items returned by the sum of items sold:

Return % =
DIVIDE (
SUM ( Sales[ReturnQuantity] ),
SUM ( Sales[SalesQuantity] )
)

Question 15

Difference Between Calculated column vs Measures?

Answer 15

Although they may look similar, calculated columns and measures operate very differently. They both use DAX expressions, but they differ in the point at which they are calculated and in the context of their evaluation:

• The values of calculated columns are calculated during a data refresh and they are evaluated using the current row context. They also take up memory and disk space and can slow down data loading times during data refreshes. However, once loaded, they do not impact performance.
• A measure is executed every time a value uses it in a report or chart. Measures are re-calculated every time a page loads. They are also re-calculated when filters or slicers are changed or a user highlights different parts of a chart or visual. A measure does not add to the space used by a data model, but it may impact the speed of user interactions. Measures operate on aggregates that are defined by the current filter context.

It is sometimes possible to calculate the same value using either a calculated column or a measure, using different DAX expressions. In most cases, where this is possible, you should use a measure, as this will not increase the size of your data model and use extra memory or disk space. This is especially important if you are working with a table that contains a large number of records.

When naming measures, you should not include the table name in the measure name. Although a measure is created under a table, it does not strictly belong to that table. If you do not include the table name, it can easily be moved between tables if necessary. It also makes it easier to identify as a measure. On the other hand, calculated columns should include the table name.

Question 16

When do you use a calculated column?

Answer 16

You will need to use a calculated column whenever you want to do the following:

• Use the value in a slicer.
• Use the value in rows or columns of a pivot table.
• Use the value on the axes of a chart.
• Use the value as a filter condition in a DAX query.
• Define an expression that is bound to the current row.

Question 17

When do you use a Measure?

Answer 17

You will need to define a measure whenever you want to do the following:

• Use a value that reflects a user’s selection of filters, slicers, or highlighted visuals.
• Calculate a ratio.
• Calculate a percentage.

Question 18

What are the two types of evaluation contexts in DAX?

Answer 18

1. Row Contexts
2. Filter Contexts

Question 19

What is a ROW CONTEXT?

Answer 19

The easiest way to think of row context is as the current row in a table. It applies when you add a calculated column to a table. When you use an expression to define your calculated column, it is executed for every row in the table. For example, if you have a table with a thousand rows in it, the expression will be evaluated one thousand times, once for every row in the table, each with a different row context.

The row context can use values from the same row of the table or rows from related tables:

Question 20

If you have a relationship between tables, what function can you use in the expression used to define a calculated column to access the columns of a related table?

Answer 20

RELATED FUNCTION

Example Measure statement

=
IF (
ISBLANK ( RELATED ( ‘Product’[Weight] ) ),
0,
[Quantity] * RELATED ( ‘Product’[Weight] )
)

Question 21

What is a Filter context?

Answer 21

The filter context is more complex to understand than the row context, but it can be defined simply as the set of filters that are applied to a data model before the evaluation of a DAX expression begins, which will alter the value returned.

The filter context has the following sources of filter:

• Row selection
• Column selection
• Slicer selection
• Filter selection
• A PivotTable filter

Question 22

What is Strictly equal to (==) DAX Operator?

Answer 22

The “strictly equal to” operator == returns TRUE when the two arguments have the same value or are both BLANK.
A comparison between BLANK and any other value returns FALSE.
This operator does not perform any implicit conversion between strings and numbers.
This comparison differs from = only when at least one of the two terms is BLANK, empty string, or 0.

The following are valid expressions using the “strictly equal to” operator.

Product[Color] == "Red"

Product[Color] == BLANK()-- Returns TRUE if Product[Color] is BLANK, returns FALSE if it is "" or any other value

Product[Color] == ""-- Returns TRUE if Product[Color] is an empty string, returns FALSE if it is BLANK or any other value

Sales[Quantity] == 1

Sales[Quantity] == BLANK() -- Returns TRUE if Sales[Quantity] is BLANK, returns FALSE if it is 0 or any other value

Sales[Quantity] == 0-- Returns TRUE if Sales[Quantity] is 0, returns FALSE if it is BLANK or any other value

The following are equivalent expressions using different comparison operators.

– The following two expressions return the same result

AND ( = 0, NOT ISBLANK( ) )

== 0

– The following two expressions return the same result

AND ( = “”, NOT ISBLANK( ) )

== “”

There is no “strictly not equal to” operator (such as <> vs. ==), which can be obtained by using the NOT of the “strictly equal operator”:

== – strictly equal to

NOT ( == ) – strictly not equal to

Question 23

What are DAX Variables?

Answer 23

• Variables allow you to store hard coded values or the results that are returned by a DAX expression.
• They can store both scalar values and tables and can be used within the definitions of calculated columns, measures, and tables.
• A variable is declared using the VAR keyword, and the overall process of declaring and using a variable uses the following syntax:

VAR =
RETURN

• When defining an expression, you can use as many variables as you need; each variable is declared using its own VAR keyword.
• Variables can be used for declaration purposes when defining other variables, as well as to return values in the expression given after the RETURN keyword.
• The RETURN keyword is used to define the return expression, which is then used to define the calculated column, measure, or table.
• Once a variable has been declared and initialized, it cannot be assigned another value. So, for example, the following would produce an error:

VAR varOne = 1
VAR varOne = varOne + 1
RETURN varOne

• Variables in DAX are not declared with data types; they are automatically allocated to the type of the value being assigned to them.

In the following example, the varTextExample variable is being assigned with the data type of text:

VAR varTextExample = “Hello World”
RETURN varTextExample

In the next example, the varTableExample variable is being assigned as a table, which, in this case, is a copy of the Products table that has been filtered so that it only includes products where the ClassName is equal to Deluxe:

VAR varTableExample = FILTER (‘Product’, ‘Product’[ClassName] = “Deluxe”)
RETURN varTableExample

• When using variables of different types with an operator, they will follow the same rules of implicit data type assignment

In the following example, the first variable, varOne, is assigned the numeric data type, while the second variable, varTwo, is assigned the text data type. The third variable, varThree, is declared by combining the first two variables using the concatenate operator, and is automatically assigned with a data type of text:

NumberAndText1 =
VAR varOne = 1
VAR varTwo = “2”
VAR varThree = varOne & varTwo
RETURN varThree

Question 24

What are the important points to remember when naming DAX variables?

Answer 24

When it comes to naming variables, you cannot use names that are already in use by tables, or names that are used as DAX keywords. In addition, the following are the limitations when it comes to naming a variable:

• Variable names cannot contain blank spaces
• Delimiters such as square brackets and apostrophes are not allowed
• The supported characters are a-z, A-Z, 0-9
• 0-9 cannot be used as a first character
• A double underscore (__) is allowed as a prefix of a name

Question 25

What does the Data Model consists of ?

Answer 25

• Data
• Tables
• Relationships
• Hierarchies
• Calculated columns
• Measures