DAX Functions Flashcards
ALL ( )
What does ALL ( ) return outside of a CALCULATE function?
ALL returns a table including all rows, ignoring any filters that might have been applied. Includes blank rows.
(Use ALLNOBLANKROW if you don’t want blanks).
ALL ( )
When used with a column that has duplicate values, what does ALL do with the duplicates?
Used with a column that has duplicate values, ALL will collapse them all into a single value before returning the table. For example, ALL( [Product[UnitPrice] ) will return a table that lists the multiple values into one unique value.
ALL ( )
What does ALL return when acting as a CALCULATE modifier?
As a CALCULATE modifier, ALL removes filters from the table and does not return a table. It becomes a CALCULATE modifier, not a table function.
ALLSELECTED ( )
What is the common use of ALLSELECTED as it relates to the current visual?
ALLSELECTED will allow you to get the “visual totals.”
Filter Intersection
What survives when filters intersect (e.g. within nested CALCULATE statements)?
When filters intersect, only the common element will survive.
So for example only black will survive the intersection between these inner and outer CALCULATE filter contexts:
Tables
What is an “anonymous table” and how do you indicate it?
Anonymous tables are created “in-line” within code. They can be created within curly brackets:
{ “James”, “David”, “Leah”, “Hannah” }
IN
What is the IN function used for?
What is it syntax sugar for?
IN simplifies logical conditions checking whether a certain value is included in a list of values or expressions.
IN is syntax sugar for the CONTAINSROWS function.
KEEPFILTERS ( )
Within what function does KEEPFILTERS have to be used?
KEEPFILTERS can only be used within a CALCULATE function as a filter modifier (not a CALCULATE modifer).
What is the difference between a CALCULATE modifier and a filter modifier?
A filter modifier changes the behaviour of only one filter argument within CALCULATE.
A CALCULATE modifier changes the global behaviour of CALCULATE.
KEEPFILTERS ( )
What does KEEPFILTERS do?
Provide three instances in which you want to use it.
KEEPFILTERS combines the new filter applied by CALCULATE with the existing external filter.
Use it when you:
- Do not want to overwrite existing filters — wrap it in this function.
- Want to return a blank if your visual is not sliced by the dimension wrapped in the function.
- You can also use this to intersect between filter context external to the measure and the filter of the CALCULATE statement.
For example: KEEPFILTERS (Product[Color] IN {“Red”, “White”, “Blue”} Will return a blank if the visual’s slicer elements don’t include one of the three colours.
IN
What is the syntax of IN with both one column and multiple columns.
One column:
Products [Color] IN { “Red”, “Black” }
Two columns have to be in brackets:
( ‘Product’[Color], ‘Product’[Brand] ) IN
{
( “Red”, “Litware” ),
( “Blue”, “Contoso” )
}
)
REMOVEFILTERS ( )
When used within a CALCULATE function, what does this function behave like?
When used in a CALCULATE expression, REMOVEFILTERS behaves exactly like ALL.
FILTER ( )
When would you use FILTER within the CALCULATE function?
If you’re retrenching two or more different columns in a single CALCULATE filter argument, you have to use FILTER. (Multiple values for a single column can be used as a CALCULATE filter argument predicate without this function).
However, only do it when you know what you’re doing. Expensive in both performance and accuracy.
VALUES ( )
What does VALUES return and what are two examples in which you would use it?
VALUES returns the list of values of a column visible in the current filter context.
- Because it returns a table, VALUES can be used as a filter argument in CALCULATE.
- Can be used in CALCULATE to restore a specific filter after removing everything with ALL.
EXAMPLE:
CALCULATE (
[Sales Amount],
ALL ( Sales ),
VALUES ( ‘Date’[Calendar Year] )
)
This removes all the filters off the fact table (and off any table filtering the fact table because of relationships and expanded tables) and then reapplies any filter visible in the current filter context
This is a common technique to use VALUES after ALL.
Filters
How do I know when to keep or remove filters?
What function will be a frequent choice when calculating percentages?
Think about the calculation - for example, do I want a % of a category or of a whole?
- If you remove the filter from category you’d end up the % being of the whole.
- If you keep filter on the category the % will be of the category.
- Returning the filter to Year makes the year % of the year.
KEEPFILTERS may often be your tool of choice.
SUM and SUMX
What is the difference between these two functions?
SUM requires a single column. You cannot sum two separate columns together.
SUMX will allow you to evaluate an expression with more than one column.
LASTNONBLANK ( )
What kind of function is LASTNONBLANK and what does it do?
LASTNONBLANK is an iterator.
It returns the last date in the date table for which it’s second argument does not produce a blank result.
The first argument is probably your Date column - remember you want it to return a date.
ISINSCOPE ( )
What does ISINSCOPE do?
When is it commonly used?
ISINSCOPE is similar to ISFILTERED but also checks that the column is being used as a Group-By column. Returns TRUE when the specified column is the level in a hierarchy.
Useful to avoid detecting slicers as filters.
Commonly used in DAX to compute percentages that adapt to hierarchy groups in a visual or matrix so that it automatically adapts to what the user selects.
Eg: if a hierarchy is applied to category - subcategory- product then you would want the percentages to show at different levels:
Category as a percentage of the grand total
Subcategories as a percentage of category
Product as a percentage of category
SUMMARIZE
What does SUMMARIZE do?
SUMMARIZE performs GROUP BY in DAX and optionally calculates subtotals.
Never use this function to computer calculated columns - you may get unexpected results. Instead use a mix of SUMMARIZE and ADDCOLUMNS.
You can include columns of other tables as long as there is a many to one relationship between the two tables, starting from the table you provide in the first argument.
To be in full control, use only for SELECT DISTINCT purposes and then wrap it in ADDCOLUMNS.
SUMMARIZECOLUMNS What does this function do and why would it not be used often in measures?
SUMMARIZECOLUMNS is kind of a SUMMARIZE 2.0 and can be used in a query with EVALUATE, but it can hardly be used in a measure because it doesn’t suppport context transition.
It’s more important to be able to read this function but you likely won’t use it much in Power BI.
Note, however, that the function is used by Power BI itself all the time. You’ll see it when you use DAX studio to read the queries produced by the engine.
CROSSJOIN
What does this function do?
CROSSJOIN creates the cartesian product of two tables.
Won’t be used often in measures, but could be useful to solve certain performance issues.
Iterators
When would you use double iteration functions and what does it look like?
If you’re iterating over a column in one table but you want to do it based on values in a column in another table, you can introduce double iterations.
SUMX (
‘Product’,
SUMX (
FILTER (
RELATEDTABLE( Sales ),
Sales[Order Date] >= ‘Product’[FirstSaleDate] &&
Sales[Order Date] < ‘Product’[FirstSaleDate] + 7
),
Sales[Quantity] * Sales[Net Price]
)
)
What is the difference between Filters and Row Context?
A filter always filters and has no row context. Think of it as building a table of values that meet the conditions.
A row context always iterates. It does something to each line within a table.
“They have nothing to share.”
How does Row Context relate to measures vs calculated columns?
A measure does not have a row context by default. If you need one, you must introduce one with an iterator.
A calculated column already has a row context by default, so you don’t need to use an iterator.
How does Row Context within one table interact with other tables?
The row context does not interact in any way with relationships in other tables (unless you are excplicit about it and use RELATED or RELATEDTABLE).
RELATED ( )
In what type of table relationship would you use RELATED?
RELATED: Useful when you have a row context on the Many side of a relationship and you want to follow the relationship to the One side.
RELATEDTABLE ( )
In what type of table relationship would you use RELATEDTABLE?
RELATEDTABLE: Useful when you start on the One side to the Many side
This makes sense when you think about it - if there could be more than one value returned from the relationship you’re reaching out to, then it has to be returned as a table.
Filter Context
What direction does filter context move from in table relationships?
Filter context automatically moves from the One side to the Many side of the relationship.
What is Context Transition?
When would you not use it?
Context transition is an operation performed by CALCULATE and CALCULATETABLE in the definition of the new filter context, under which it evaluates its expression.
CALCULATE invalidates any row context. It automatically adds as filter arguments all the columns that are currently being iterated in any row context — filtering their actual value in the row being iterated.”
As a result it is expensive - one filter for every single column in the table, repeated for every row in the table. You have to use context transition carefully.
** important note: context transition should not be used over tables that might contain duplicates rows. It will inflate the results!
Why should you never call CALCULATE when iterating over a table that does not have all unique rows (doesn’t have a primary key, i.e. a fact table)?
Because it will inflate the results.
This includes when you call another measure within a measure based on an iterator (e.g. SUMX). When you do this, DAX automatically wraps the measure inside of a hidden CALCULATE function! Inside an iterator, the hidden CALCULATE function invokes context transition. This works fine if there are unique rows (where it can identify the only row being duplicated). But if not then it collapses all the duplicate values into one!! In such cases, instead of calling the measure within the measure, write out the equation found in the measure being called.
Filters
What is the general rule when filtering tables to protect performance?
When filtering tables, always choose to do so in the smallest number of columns possible needed for the calculation. This maintains performance. Don’t just filter the entire table.
MAXX ( )
What does this function do?
What does it look like?
How does Context Transition happen?
MAXX sums at a row level and aggregates the max.
Example :=
MAXX (
Date,
[Sales Amount]
)
If the filter context is year it still only shows the highest daily max for the year.
In this code the context transition happens from the date table to sales because when you call a measure in an iterator it’s wrapped in a hidden CALCULATE statement and therefore invokes context transition. In this example the row context is daily therefore carries through into the year and month level with the daily max.
