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.
