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

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 DATATABLE.  

The DATATABLE function

The DATATABLE function provides a mechanism for declaring an inline set of data values and employs the following syntax:

There are three [3] main arguments in this function:

• ColumnName: this argument is required, and it is column name to be defined
• DataType: this argument is required, and it is an enumeration that includes INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY and DATETIME
• value: a single argument using Excel syntax for a one-dimensional array constant, nested to provide an array of arrays.  This argument represents the set of data values that will be in the table.

As an example, consider the following DAX query:

The result is as follows:

It should be noted that:

• the value arguments in the syntax of the DATATABLE function cannot be expressions; they need to be constant.  The following syntax will generate an error:

After pressing OK the following error dialog box will appear:

• like the DATATABLE function, Table Constructor also allows users to create tables, but Table Constructor allows any scalar expressions as an input value while the DATATABLE function does not
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

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.