/* Queries and annotations from SQL Saturday #714's DAX More than Measures
These queries were written against a companion .pbix file, named 'query
base.pbix'. This file is intended to be used in
[DAXStudio](http://daxstudio.org/). The materials were originally posted
to the [SQL Saturday #714
site](http://www.sqlsaturday.com/714/Sessions/Schedule.aspx).
This "presentation" is intended to walk through the basics of DAX
queries, through a series of illustrative examples.
*/
// EVALUATE is required before any query. A query is an expression that
// evaluates to a table.
EVALUATE
'Dim 1' // Table names are enclosed by single quotes. Table names
// evaluate to the full contents of the table
/* FILTER() is a general purpose filtering function. It takes a table expression
as its ARG1, and a boolean expression as its ARG2. It returns rows from
ARG1 for which ARG2 returns TRUE (or true-ish - DAX performs implicit
type casting).
FILTER() creates a row context for each row in the table defined by ARG1
and evaluates the expression in ARG2 for each row context. This is
performed in a single-threaded iteration of the row contexts.
*/
// This will return no rows. FALSE() is always false.
EVALUATE
FILTER(
'Dim 1',
FALSE()
)
// Double-pipe, '||' is the logical OR operator.
EVALUATE
FILTER(
'Dim 1',
'Dim 1'[Dim1Key] = 1
|| 'Dim 1'[Dim1Key] = 2
)
// This will randomly return all or no rows.
EVALUATE
FILTER(
'Dim 1',
RAND() > .5
)
// DAX does lots of implicit type casting. This will return all rows for which
// the expression evaluates to a non-0 value (implicit true value), in this
// case, 4 rows.
EVALUATE
FILTER(
'Dim 1',
'Dim 1'[Dim1Key] -2
)
// RELATEDTABLE() lets us refer to the set of rows in a table to which an active
// relationship exists in the current context, which are associated with the key
// value in the current table. The query below says "give all rows of 'Dim 1'
// which have associated rows in DumbFact".
// Note that tables without spaces in their names do not need single quotes.
// This is a semi-join.
EVALUATE
FILTER(
'Dim 1',
NOT( ISEMPTY( RELATEDTABLE( DumbFact ) ) )
)
// Here's the fact table.
EVALUATE
DumbFact
EVALUATE
FILTER(
DumbFact,
DumbFact[AmountValue] > 75
)
EVALUATE
'Dim Alpha'
// When operating in a row context of the "many" side of an N:1 relationship, we
// use RELATED() rather than RELATEDTABLE(). In this case, d/t the
// relationship's cardinality, we know that there will be a scalar
// result. RELATED() returns a scalar and RELATEDTABLE() returns a table.
EVALUATE
FILTER(
DumbFact
,RELATED( 'Dim Alpha'[DimA Category] ) = "Category A"
)
// CALCULATETABLE() takes a table expression as ARG1 and setfilter expressions
// as ARGs 2-N (it takes an arbitrary number of arguments). It evaluates
// ARG2-ARGN independently in the current filter context and combines them with
// a logical AND operation to define a new filter context in which to evaluate
// ARG1. All setfilters can be thought of as tables. The syntax below is
// syntactic sugar which will be defined later.
// Since we are operating in filter context, all relationships are enforced as
// logical semi-joins, and we do not have any need, or ability to use,
// RELATED[TABLE]() (these two only work in row contexts).
EVALUATE
CALCULATETABLE(
DumbFact
,'Dim Alpha'[DimA Category] = "Category A"
)
// This is the same semi-join as defined on lines 68-72.
EVALUATE
CALcULATETABLE(
'Dim 1'
,DumbFact
)
// This is an empty resultset, as ARG2 and ARG3 define disjoint sets.
EVALUATE
CALCULATETABLE(
DumbFact
,
// EVALUATE
FILTER(
'Dim Alpha'
,'Dim Alpha'[DimA Category] = "Category A"
|| 'Dim Alpha'[DimA Value] = "H"
),
'Dim Alpha'[DimA Value] = "g"
)
// The FILTER() and the boolean expression below are identical. The latter is
// syntactic sugar for the former. These two will always evaluate
// identically. Under the covers, the latter is rewritten to the former.
EVAlUATE
CALCuLATETABLE(
DumbFact,
// EVALUATE
FiLTER(
ALL( 'Dim Alpha'[DimA Value] ),
'Dim alpha'[Dima Value] = "g"
),
'Dim Alpha'[DimA Value] = "g"
)
//SELECT *
//FROM DumbFact
//WHERE EXISTS (SELECT * FROM 'Dim Alpha' WHERE [DimA Value] = 'g')
// We can define a query-local measure in the below format. This is a great
// method for testing out alternative query definitions
DEFINE
MEASURE 'DumbFact'[Amount] =
SUM ( DumbFact[AmountValue] )
EVALUATE
FILTER (
// ADDCOLUMNS() takes a table as ARG1 and an arbitrary odd number of
// arguments. Even-numbered arguments are strings that define the name
// of a new column in the table returned by ADDCOLUMNS(). Odd-numbered
// arguments (besides ARG1) are an expression to be evaluated in the row
// context of the table in ARG1 which will make up the values of the new
// column.
ADDCOLUMNS (
VALUES ( 'Dim 1'[Dim Value] ),
"ColName",
[Amount]
),
// The return of ADDCOLUMNS() is a nameless table that includes a column
// named [ColName] (its ARG2). We can use this in ARG2 of FILTER(). In
// this way we can nest arbitrary expressions to form complex queries.
[ColName] = 78
)
// Here we can define a table as a VAR. VARs are defined as
// VAR =
// RETURN
// 'Buckets' could just as easily be a physical table. We define as a variable
// for expedience here.
EVALUATE
VAR Buckets =
UNION(
// ROW() defines a one-row table with pairs of quoted column aliases and
// scalar expressions.
ROW( "Bucket", "Less than 100", "Lower Bound (inclusive)", 0, "Upper Bound (exclusive)", 100 ),
ROW( "Bucket", "Greater than or equal to 100", "Lower Bound (inclusive)", 100, "Upper Bound (exclusive)", 10000 )
)
RETURN
Buckets
// Here, we use DAX to build an association between two unrelated tables to
// return an amount bucket rather than the amount itself.
DEFINE
MEASURE 'DumbFact'[Amount] =
SUM ( DumbFact[AmountValue] )
EVALUATE
VAR Buckets =
UNION(
ROW( "Bucket", "Less than 100", "Lower Bound (inclusive)", 0, "Upper Bound (exclusive)", 100 ),
ROW( "Bucket", "Greater than or equal to 100", "Lower Bound (inclusive)", 100, "Upper Bound (exclusive)", 10000 )
)
RETURN
// SELECTCOLUMNS() takes a table as ARG1 and pairs of column aliases and
// column references as ARGs 2-N.
SELECTCOLUMNS(
// GENERATE() evaluates the second table in the row context of
// the first - this gives JOIN semantics.
GENERATE(
ADDCOLUMNS(
VALUES( 'Dim 1'[Dim Value] ),
"ColName",
[Amount]
),
// Easier to reference columns from ARG1 in a VAR than
// as column references. If we did not assign to a
// context-local VAR, we would need to use EARLIER().
// This VAR is scoped to the row context which ARG2 is
// evaluated in.
VAR amt = [ColName]
RETURN
FILTER(
Buckets,
[Lower Bound (inclusive)] <= amt
&& amt < [Upper Bound (exclusive)]
)
),
// SELECTCOLUMNS() alias and column reference ARGs
"Dim Value", 'Dim 1'[Dim Value],
"Bucket", [Bucket]
)