Power Pivot Principles: The A to Z of DAX Functions – DEFINE

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DEFINE.

The DEFINE statement

The DEFINE statement is a statement with one or more entity definitions that can be applied to one [1] or more EVALUATE statements of a DAX query.  Its typical syntax is as follows:

There are three [3] main parameters in the syntax:

• entity: this can be MEASURE, VAR, TABLE, or COLUMN
• name: the name of a MEASURE, VAR, TABLE, or COLUMN definition.  This cannot be an expression.  It does not need to be unique as the name only exists for the period of the query
• expression: any DAX expression with a table or scalar value output.  Any of the defined entities may be used in the expression.  If you need to transform a scalar expression into a table expression, you may use the ROW() function to return a single row table or wrap the expression within a table constructor using curly braces {}.

There are a few key notes for this DEFINE statement:

• a DAX query can have multiple EVALUATE statements, but it can have only one DEFINE statement
• at least one definition is required in the DEFINE statement
• measure definitions for a query will override model measure of the same name
• VAR names have unique restrictions.

Let’s consider the following example, where we have the following DEFINE statement to declare a variable and then use the EVALUATE statement to export the result to the spreadsheet:

This will return us:

On the other hand, we can use the DEFINE statement to declare a measure.  Please consider the following example here where we have the following Sales table (not displayed in full):

The Customers table is as follows:

We can write the following DAX code to declare a measure named ‘Total Sales’ and summarise ‘Total Sales’ by the Country column in the Customers table: 

This code will give us the following Table:

Let’s have an example where we use the DEFINE statement to declare a table by using the DATATABLE function:

This DAX code will give us a brand new data table name History as in the following picture:

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.