DAX - CALCULATE

Question 1

What does the Calculate expression do?

Answer 1

Evaluates an expression in a context modified by the specified filters.

Question 2

CALCULATE syntax

Answer 2

CALCULATE
(
expression,
filter1,
filter2,
...
)

Question 3

What happens to the CALCULATE function if you already have filters on the columns you are using in the CALCULATE expression?

Answer 3

Your filter in CALCULATE will override them

Question 4

How can you keep filters that already exist when using CALCULATE?

Answer 4

Use KEEPFILTERS

Question 5

What are the five CALCULATE modifiers?

Answer 5

1. CROSSFILTER
2. ALL
3. USERELATIONSHIP
4. KEEPFILTERS
5. REMOVEFILTERS

Question 6

What are the three types of filters that can be used in CALCULATE?

Answer 6

1. Boolean filter expressions
2. Table filter expressions
3. Filter modification functions

Question 7

Which function modifies the existing evaluation context?

Answer 7

CALCULATE

Question 8

How does the CALCULATE function alter filter context for measures?

Answer 8

It can change the context under which a calculation occurs, through the addition or removal of filter constraints.

Question 9

What does the CROSSFILTER function allow you to do?

Answer 9

• Change cross-filter direction for a specific measure while maintaining the original settings.

OR

• Disable a relationship between tables

Question 10

What is the only place a CROSSFILTER function can be used?

Answer 10

Within a DAX function that accepts a filter as an argument.

Question 11

CROSSFILTER syntax

Answer 11

CROSSFILTER(
Table1[ColumnName1],
Table2[ColumnName2],
FilterDirection
)

The first column is typically the many side of the relationship and the second column is the one side.

Question 12

Where are filter functions normally used?

Answer 12

Within the CALCULATE function

Question 13

What are the FilterDirection arguments that can be used in CROSSFILTER?

Answer 13

• None
• Both
• Oneway (Filters applied on one side)

Question 14

What are the FilterDirection arguments that can be used in CROSSFILTER for one to one relationships?

Answer 14

oneWay_rightfiltersleft
from right to the left side of the relationship

oneWay_leftfiltersright
from left side to the right side of the relationship

Question 15

When working with Boolean data types, what represents TRUE or FALSE?

Answer 15

TRUE=1
FALSE=0

Question 16

What are the five rules that Boolean filter expressions must abide by?

Answer 16

They can:
- Reference columns from a single table.

They cannot:
- Reference measures
- Use a nested CALCULATE function
- Use functions that scan or return a table
- Use aggregate functions

Question 17

Example of a Boolean filter used in CALCULATE

Answer 17

CALCULATE(expression,filter1,filter2,...)

Sales of high-end products =
CALCULATE(
SUM(Sales[Total Sales]),
FILTER(Products, Products[Unit Price] >= 500) –Returns true or false
)

Question 18

Filter syntax

Answer 18

Filter(TableName, BooleanExpression)

Question 19

Filter Example

Answer 19

FILTER(Products, Products[Unit Price] >= 500

Question 20

Why would you use the FILTER function?

Answer 20

To apply complex filter conditions.

Question 21

What happens when you add a filter to CALCULATE?

Answer 21

It overrides any filters previously created in the column.

Question 22

How can you add a new filter to any previous filters?

Answer 22

Use filter modifier functions such as:
- REMOVEFILTERS
- KEEPFILTERS
- ALL
- CROSSFILTER
- USERELATIONSHIP

Question 23

How does REMOVEFILTERS work?

Answer 23

It removes filters from
- One or more columns of a table
OR
- All columns of a single table.

Question 24

REMOVEFILTERS Example

Answer 24

Total Sales = 
CALCULATE 
( 
[Total Sales], 
REMOVEFILTERS ( Product ), 
REMOVEFILTERS ( Region) 
)

This example removes all filters from both the product and dimension tables

Question 25

How does KEEPFILTERS work?

Answer 25

It adds a filter without removing existing filters on the same columns.

Question 26

KEEPFILTERS Example

Answer 26

``` Blue Products Sale = CALCULATE ( [Total Sales], KEEPFILTERS ( Products[Color] = "Blue" ) ) ``` ## Footnote In this example we wanted to calculate total sales while keeping the existing color filter on the Product table. If an existing active filter exists on Product[Color], then KEEPFILTERS ensures the filter is not overridden. Instead, it is applied in addition to the new filter.

Question 27

How does the ALL function work?

Answer 27

It removes filters from a table or columns ALL(Table) ALL(Column1, Column2, ...)

Question 28

What is a difference between ALL and REMOVEFILTERS?

Answer 28

- **REMOVEFILTERS** simply removes filters from the specified tables or columns - **ALL** returns the removes all the filters *and* returns a table or column

Question 29

ALLEXCEPT syntax

Answer 29

ALLEXCEPT(table, column1, column2, ...)

Question 30

How does ALLEXCEPT work?

Answer 30

It removes all filters except for ones that have been applied to the columns you specify in the ALLEXCEPT function

Question 31

How does ALLSELECTED work?

Answer 31

This is a very complex function. Need to research more.

Question 32

ALLNOBLANKROW syntax

Answer 32

ALLNOBLANKROW(Table)

Question 33

How does ALLNOBLANKROW work?

Answer 33

It returns all the rows in a table except for the blank rows.

Question 34

How does USERELATIONSHIP work?

Answer 34

- Designates an **inactive relationship to** become **active.** - This automatically **disables** the **currently active** relationship.

Question 35

What logical operators can you use to execute multiple filters in the CALCULATE function?

Answer 35

AND=&& OR=|| Use AND if all filters must be met. Use OR if any of the filters can be met.

Question 36

CALCULATE example Total Sales for Category Bikes

Answer 36

Bike Sales=CALCULATE ( Total Sales //existing measure, Category="Bikes" //filter )

Question 37

CALCULATE example Total Sales for Product Color Blue

Answer 37

Bike Sales=CALCULATE ( Total Sales //existing measure, ProductColor="Blue" //filter )

Question 38

Example Total Sales of Product Color Blue and Country United States

Answer 38

Bike Sales=CALCULATE ( Total Sales //existing measure, ProductColor="Blue" //filter 1, Country="United States" //filter 2 ) Just writing a really long sentence to left justify

Question 39

How does the CALCULATE function change the context under which a calculation occurs?

Answer 39

Through the addition or removal of filter constraints.

Question 40

Example of changing the filter context using CALCULATE and PREVIOUSMONTH

Answer 40

``` Total Sales Last Month = CALCULATE( SUM(Sales[SalesAmount]), PREVIOUSMONTH('Date'[Date]) ) ```