Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Microsoft Data Modeling in Power BI > DAX - CALCULATE > Flashcards

DAX - CALCULATE Flashcards

1
Q

What does the Calculate expression do?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

CALCULATE syntax

A 
CALCULATE
(
expression,
filter1,
filter2,
...
)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

Your filter in CALCULATE will override them

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

Use KEEPFILTERS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are the five CALCULATE modifiers?

A
  1. CROSSFILTER
  2. ALL
  3. USERELATIONSHIP
  4. KEEPFILTERS
  5. REMOVEFILTERS
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A
  1. Boolean filter expressions
  2. Table filter expressions
  3. Filter modification functions
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Which function modifies the existing evaluation context?

A

CALCULATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

How does the CALCULATE function alter filter context for measures?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What does the CROSSFILTER function allow you to do?

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

OR

  • Disable a relationship between tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

CROSSFILTER syntax

A 
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Where are filter functions normally used?

A

Within the CALCULATE function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A
  • None
  • Both
  • Oneway (Filters applied on one side)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

oneWay_rightfiltersleft
from right to the left side of the relationship

oneWay_leftfiltersright
from left side to the right side of the relationship

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

TRUE=1
FALSE=0

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Example of a Boolean filter used in CALCULATE

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

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

18
Q

Filter syntax

A

Filter(TableName, BooleanExpression)

19
Q

Filter Example

A

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

20
Q

Why would you use the FILTER function?

A

To apply complex filter conditions.

21
Q

What happens when you add a filter to CALCULATE?

A

It overrides any filters previously created in the column.

22
Q

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

A

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

23
Q

How does REMOVEFILTERS work?

A

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

24
Q

REMOVEFILTERS Example

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

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

25
Q

How does KEEPFILTERS work?

A

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

26
Q

KEEPFILTERS Example

A 
Blue Products Sale = 
CALCULATE 
( 
[Total Sales], 
KEEPFILTERS ( Products[Color] = "Blue" ) 
)

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.

27
Q

How does the ALL function work?

A

It removes filters from a table or columns

ALL(Table)
ALL(Column1, Column2, …)

28
Q

What is a difference between ALL and REMOVEFILTERS?

A
  • REMOVEFILTERS simply removes filters from the specified tables or columns
  • ALL returns the removes all the filters and returns a table or column
29
Q

ALLEXCEPT syntax

A

ALLEXCEPT(table, column1, column2, …)

30
Q

How does ALLEXCEPT work?

A

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

31
Q

How does ALLSELECTED work?

A

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

32
Q

ALLNOBLANKROW syntax

A

ALLNOBLANKROW(Table)

33
Q

How does ALLNOBLANKROW work?

A

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

34
Q

How does USERELATIONSHIP work?

A
  • Designates an inactive relationship to become active.
  • This automatically disables the currently active relationship.
35
Q

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

A

AND=&&
OR=||

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

36
Q

CALCULATE example Total Sales for Category Bikes

A

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

37
Q

CALCULATE example Total Sales for Product Color Blue

A

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

38
Q

Example Total Sales of Product Color Blue and Country United States

A

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

39
Q

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

A

Through the addition or removal of filter constraints.

40
Q

Example of changing the filter context using CALCULATE and PREVIOUSMONTH

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

Microsoft Data Modeling in Power BI (14 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions