DAX - CALCULATE Flashcards
What does the Calculate expression do?
Evaluates an expression in a context modified by the specified filters.
CALCULATE syntax
CALCULATE ( expression, filter1, filter2, ... )
What happens to the CALCULATE function if you already have filters on the columns you are using in the CALCULATE expression?
Your filter in CALCULATE will override them
How can you keep filters that already exist when using CALCULATE?
Use KEEPFILTERS
What are the five CALCULATE modifiers?
- CROSSFILTER
- ALL
- USERELATIONSHIP
- KEEPFILTERS
- REMOVEFILTERS
What are the three types of filters that can be used in CALCULATE?
- Boolean filter expressions
- Table filter expressions
- Filter modification functions
Which function modifies the existing evaluation context?
CALCULATE
How does the CALCULATE function alter filter context for measures?
It can change the context under which a calculation occurs, through the addition or removal of filter constraints.
What does the CROSSFILTER function allow you to do?
- Change cross-filter direction for a specific measure while maintaining the original settings.
OR
- Disable a relationship between tables
What is the only place a CROSSFILTER function can be used?
Within a DAX function that accepts a filter as an argument.
CROSSFILTER syntax
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.
Where are filter functions normally used?
Within the CALCULATE function
What are the FilterDirection arguments that can be used in CROSSFILTER?
- None
- Both
- Oneway (Filters applied on one side)
What are the FilterDirection arguments that can be used in CROSSFILTER for one to one relationships?
oneWay_rightfiltersleft
from right to the left side of the relationship
oneWay_leftfiltersright
from left side to the right side of the relationship
When working with Boolean data types, what represents TRUE or FALSE?
TRUE=1
FALSE=0
What are the five rules that Boolean filter expressions must abide by?
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
Example of a Boolean filter used in CALCULATE
CALCULATE(expression,filter1,filter2,...)
Sales of high-end products =
CALCULATE(
SUM(Sales[Total Sales]),
FILTER(Products, Products[Unit Price] >= 500) –Returns true or false
)
Filter syntax
Filter(TableName, BooleanExpression)
Filter Example
FILTER(Products, Products[Unit Price] >= 500
Why would you use the FILTER function?
To apply complex filter conditions.
What happens when you add a filter to CALCULATE?
It overrides any filters previously created in the column.
How can you add a new filter to any previous filters?
Use filter modifier functions such as:
- REMOVEFILTERS
- KEEPFILTERS
- ALL
- CROSSFILTER
- USERELATIONSHIP
How does REMOVEFILTERS work?
It removes filters from
- One or more columns of a table
OR
- All columns of a single table.
REMOVEFILTERS Example
Total Sales = CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ), REMOVEFILTERS ( Region) )
This example removes all filters from both the product and dimension tables
How does KEEPFILTERS work?
It adds a filter without removing existing filters on the same columns.
KEEPFILTERS Example
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.
How does the ALL function work?
It removes filters from a table or columns
ALL(Table)
ALL(Column1, Column2, …)
What is a difference between ALL and REMOVEFILTERS?
- REMOVEFILTERS simply removes filters from the specified tables or columns
- ALL returns the removes all the filters and returns a table or column
ALLEXCEPT syntax
ALLEXCEPT(table, column1, column2, …)
How does ALLEXCEPT work?
It removes all filters except for ones that have been applied to the columns you specify in the ALLEXCEPT function
How does ALLSELECTED work?
This is a very complex function. Need to research more.
ALLNOBLANKROW syntax
ALLNOBLANKROW(Table)
How does ALLNOBLANKROW work?
It returns all the rows in a table except for the blank rows.
How does USERELATIONSHIP work?
- Designates an inactive relationship to become active.
- This automatically disables the currently active relationship.
What logical operators can you use to execute multiple filters in the CALCULATE function?
AND=&&
OR=||
Use AND if all filters must be met.
Use OR if any of the filters can be met.
CALCULATE example Total Sales for Category Bikes
Bike Sales=CALCULATE
(
Total Sales //existing measure,
Category=”Bikes” //filter
)
CALCULATE example Total Sales for Product Color Blue
Bike Sales=CALCULATE
(
Total Sales //existing measure,
ProductColor=”Blue” //filter
)
Example Total Sales of Product Color Blue and Country United States
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
How does the CALCULATE function change the context under which a calculation occurs?
Through the addition or removal of filter constraints.
Example of changing the filter context using CALCULATE and PREVIOUSMONTH
Total Sales Last Month =
CALCULATE(
SUM(Sales[SalesAmount]),
PREVIOUSMONTH('Date'[Date])
)
