Dax Functions Flashcards
RELATEDTABLE
Returns a table with all the rows related to the current one. One to many.
NumOfStudents = Countrows (Relatedtable (Students))
Can be used to generate a row context through a one to many relationship.
“X” Aggregation functions
Evaluate an expression for each row in a table. Two parameters - table and expression. SUMX AVERAGEX MINX MAXX
IN
Check if the result of an expression is included in a list of values. Use curly brackets:
Student[State] IN {“NC”,”SC”,GA”}
SWITCH
Changes values, instead of nested IF().
SWITCH ( Product[Size], "S", "Small", "M", "Medium", "L", "Large", "XL", "Extra Large", "Other")
SWITCH(TRUE()…)
Can use for if then statements.
SWITCH (TRUE (), Product[Size] = "S", 0.5, AND ( Product[Size] = "L", Product[Price] < 100 ), 0.2, Product[Size] = "L", 0.35,0 )
MAX
Returns the largest value in a column
MAX ( Sales[SalesAmount] )
or between two columns
MAX ( Sales[Amount], Sales[ListPrice] )
IFERROR
Returns an alternative expression IFERROR ( Sales[GrossMargin] / Sales[Amount], BLANK () )
DIVIDE
Avoid using IF to check for errors. Returns an alternative value.
Variables
Calculate intermediate values in a measure or column.
VAR….
RETURN….
Variable values are assigned in the context they are defined, not in the context they are used. Calculate cannot modify a variable.
FILTER
FILTER •Adds a new condition •Restricts the number of rows of a table •Returns a table •Can be iterated by an «X» function Needs a table as input The input can be another FILTER
Filters on entire tables are bad. Narrow the table using ALL; add KEEPFILTERS if you need it to respond to other filters in the report.
ALL
ALL •Returns all the rows of a table •Ignores any filter •Returns a table that can be iterated by an «X» function Needs a table as input
Can be used with a single column
ALL ( Customers[CustomerName] )
•The result contains a table with one column that contains all unique values in the column
COUNTROWS ( ALL ( Orders[Channel], Orders[Color], Orders[Size] )) Returns a table with all the values of all the columns passed as parameters.
ALLEXCEPT
Removes all filters except those applied to the specified column.
Returns a table with all existing combinations of the given columns.
ALLEXCEPT(
Orders, Orders[City] )
{NEED MORE INFO HERE}
DISTINCT
Returns the unique values of a column, only the ones visible in the current filter context.
NumOfProducts := COUNTROWS ( DISTINCT ( Product[ProductCode] ) ) Compare to VALUES()
VALUES
Returns the unique values of a column, only the ones visible in the current filter context, including the additional blank row if it is visible in the filter context. NumOfProducts := COUNTROWS ( VALUES ( Product[ProductCode] ) )
ALLNOBLANKROW
ALL returns the additional blank row, if it exists. ALLNOBLANKROW omits it.—- Returns only the existing products
COUNTROWS (
ALLNOBLANKROW ( Products[ProductKey] )
)
Counting different values
DISTINCT and ALLNOBLANKROW won’t include blank row, VALUES and ALL will include blank row
ALLSELECTED
With the date filters, I needed to always be evaluating the actual dates that were being filtered. I didn’t want to have any additional information. I only wanted to focus on what was selected. This is in simplest form what ALLSELECTED does. It focuses on what you have selected within your report page.
(Need more here)
1 x 1 Tables
when a table contains one row and one column, can treat it as a scalar value. Use to display selected values as text, make visible to the user.
SELECTEDVALUE
Retrieves the value of a column when only one value is visible. Sel Category := "You selected: " & SELECTEDVALUE ( 'Product Category'[Category], "Multiple values")
ISEMPTY
Checks if a table is empty, Returns True or False.
ISEMPTY ( VALUES ( Product[Unit Price] ) )
is equivalent to
COUNTROWS ( VALUES ( Product[Unit Price] ) ) = 0
Table variables
Variables can contain a scalar value or a table. Using table variables greatly helps in splitting complex expressions.
Evaluation context
All of the filters etc. applied to a formula that affect its evaluation.
Slicers, visual filters, other graphs on page, etc.
Filter context
Defined by
Row selection, column selection, report filters, slicer selection.
By default, flows from the one side to many side of relationships.
Row context
Iterates. Needed to evaluate column values, it is the concept of “current row”. In calculated column, defined automatically for each row. In row iteration functions, it is defined by user formulas.
Does not propagate through relationships.
Context errors
Measures do not automatically have a row context. “X” iterating functions can create a row context.
Changing filter context
Filter context remains the same unless explicitly changed. FILTER functuion does not change filter context. ALL ignores the filter context, but does not change it.
RELATED
Row context only includes fields in the current table. Must use RELATED to access further tables. Must be a relationship between tables. Can go through multiple relationships. Many to one relationships.
Nested row contexts
Can use variables to grab value from current row, then use it in a calculation for all rows. Primarily calculated columns.
(Insert RankOnPrice formula from slides here)
KEEPFILTERS
Used within CALCULATE so that filters in the report are maintained. Result will be an intersection of CALCULATE filters and report filter context.
CALCULATE(Sales amount,
KEEPFILTERS(Product[Color] IN {‘Red’, ‘White’, ‘Blue’})
)
CALCULATE
Replaces / modifies filter context. Creates an implicit FILTER on an expression.
Can add multiple filters, result will be intersection of all the filters.
But nested CALCULATE functions overwrite each other. Innermost CALCULATE wins.
If need to work with two columns, use FILTER, ALL (to narrow down the number of columns in table), and KEEPFILTERS if needed.
Cannot use aggregators in compact CALCULATE statements, use a variable wherever possible to avoid using FILTER.
Calculate cannot modify a variable.
Can put ALL directly in a CALCULATE, don’t need to add a FILTER first.
Table Functions
Return a table, not a value.
FILTER, ALL, VALUES, DISTINCT, RELATEDTABLE
USERELATIONSHIP
Activates an inactive relationship. Used with Calculate. Relationship must already exist.
CROSSFILTER
Changes the filter status of a relationship. Single, none, both. Much better than making a relationship always bidirectional.
allows use of a value from the one side of one to many relationship.
CALCULATE(COUNTROWS(Customers),
CROSSFILTER(related field 1, related field 2, BOTH)
))
ALL + CALCULATE
When used with CALCULATE, ALL removes filters from the named table or field, respecting any other filters placed within CALCULATE. It does not necessarily overwrite. Should be called REMOVEFILTERS.
CALCULATE ( CALCULATE (ALL(Date[Year]) Date[Year] = 2007 ) Does not force the calculation to use all years, instead it removes any filters from Year.
ALLSELECTED + CALCULATE
Gives the “visual total”, respecting filters from slicers in the report. Can be used with table, column, or nothing at all.
ALLSELECTED(table[column]) - could be used to generate % of column or row totals, depending on matrix structure.
ALLSELECTED(table)
ALLSELECTED() - can be used to obtain % of grand totals, but respecting slicer selections
Context Transition
A byproduct of calculate.
Transforms row context into a filter context.
Can be used safely in tables with unique values, not in tables with duplicates.
Does not filter one row, filters all the identical rows.
Creates a filter context out of a row context.
Transforms all the row contexts, not just the last one.
Invoke by (calculated column):
Test =
CALCULATE(
SUM ('Product'[Unit Price]')
) < This will calculate the sum of all unique rows, deliver the value to each row
Circular dependency
Can be created by context transition if multiple columns use CALCULATE in a table that does not have unique values. Won’t happen if use ALLEXCEPT to remove offending columns.
CALCULATE execution order
1.It evaluates filter arguments, still in the original evaluation contexts (both row context and filter context)
2.It executes context transition
•Adds all the column filter to the new context
3.It evaluates the modifiers
•USERELATIONSHIP, CROSSFILTER, ALL
4.It applies the filter arguments evaluated in (1)
This code works: Delivered amount 2007: CALCULATE ( [Sales Amount], 'date'[Calendar Year] = "CY 2007", USERELATIONSHIP ( Sales[DeliveryDateKey], 'Date'[DateKey] ))
RANKX
o Table
•Evaluated in the external filter context
o Expression
•Evaluated in the row context of Table during the iteration
•Evaluated in the external filter context for the rank calculation
Using ALL, the lookup table contains all of the products and RANKX works as expected. ALLSELECTED is another good candidate (depending on requirements).
RankOnSales :=
RANKX (
ALL( Products ), [SumOfSales] )
RANKX somewhat relies on context transition.If the context transition is missing, then wrong values will be computed.
You need to force context transition, when needed, using CALCULATE. Using a measure reference does not have this requirement because it is implicit. RankOnSales := RANKX (ALL ( Products ), CALCULATE ( SUM ( Sales[Sales] ) ))
ISINSCOPE
ISINSCOPE lets you detect when a column is currently being grouped by.It is useful when you want to control the calculation in different levels of a hierarchy. RankOnSale = IF ( ISINSCOPE( Products[Product] ), RANKX ( ALLSELECTED ( Products ), [SumOfSales]))
Time intelligence functions
Table functions that simplify dealing with dates in DAX. They create a new table based on a few inputs.
DATESYTD
Filters a table to the first day of the year through the last visible day. Can set the last day of the year to set the fiscal year end date. Can use in CALCULATE.
TOTALYTD
Hides the use of CALCULATE. Can not accept additional filters. Not recommended to use.
DATEADD
Shifts dates by specified number of specified intervals. Can be used to calculate YOY (or any period) changes.
SAMEPERIODLASTYEAR
Simplifies DATEADD for YOY comparison.
PARALLELPERIOD
similar to DATEADD, but always returns the full period. Could use to compare YTD sales to the full previous year’s sales as a percentage, for example.
DATESINPERIOD
returns dates in a given number of periods. Used to calculate moving totals, averages.
CALCULATE ( SUM(Sales[SalesAmount]), DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date]), -1, YEAR ) )
LASTDATE
returns a single row and a single column with a date.
LASTNONBLANK
Returns last value in a column. Can be text or numeric.
(e.g., uses FIRST instead of last, but same principle:)
TopProduct :=
FIRSTNONBLANK (
TOPN (
1,
VALUES ( Product[Product Name] ),
[Internet Total Sales]
),
1
)
Semi-Additive measures
Finding last balance: LastBalance = VAR LastDayWithTransactions = CALCULATE ( MAX (Balances[Date]), ALL (Balances[Name]) )
RETURN CALCULATE ( SUM (Balances[Balance]), 'Date'[Date] = LastDayWithTranactions )
or
LastBalanceNonBlank = SUMX ( VALUES (Balances[Name]), CALCULATE ( SUM(Balances[Balance]), LASTNONBLANK ( 'Date'[Date], CALCULATE (COUNTROWS (Balances)) ) ) )
