Data type inspection in semantic models

In DAX, we have had the following functions available to inspect the data type of a value, ever since the first version of Power Pivot and SSAS (on SQL Server 2012):

ISTEXT( <value> )
ISLOGICAL( <value> )
ISNONTEXT( <value> )
ISNUMBER( <value> )        

ISTEXT returns true if the value is a string.

ISLOGICAL returns true if value is TRUE / FALSE (also known as a boolean value).

ISNONTEXT returns the opposite of ISTEXT

ISNUMBER returns true if the value is numeric or represents a Date/Time value.

Data types in a semantic model

As a reminder, a semantic (aka. tabular) model supports three different types of numeric values:

• Decimal (fixed precision with 4 decimals. Aliases: Fixed decimal number, Currency)
• Double (floating precision. Aliases: Decimal number)
• Int64 (signed integer, i.e. fixed precision with 0 decimals. Aliases: Integer, Whole number)

In addition, semantic models support the following non-numeric data types:

• DateTime (internally represented as a floating-point number, where the integer portion represents the date, and the decimal portion the time of day)
• Boolean (a true/false value, Aliases: Logical, True/False)
• String (a string of unicode, UTF-16, characters. Aliases: Text)
• Binary (binary data, e.g. images, less commonly used)

Lastly, you'll sometimes see measures that have the Variant type. This is not an actual data type, but rather an indication that the type is not known statically (for example because of an IF function that returns values of different data types depending on the conditional). The actual data type will be determined at runtime (i.e. when measures are evaluated in a DAX query).

I deliberately used the names of the data types as they are listed in the Microsoft.AnalysisServices.Tabular namespace documentation, as these are the names you'll see if you work programmatically with semantic models.

Note, the documentation also lists the Automatic and Unknown data types, in addition to the data types I listed above. Automatic is typically seen in model metadata, before a model gets deployed and the engine has had a chance to (statically) infer what the type of a given expression is. Unknown is often seen when an expression has an error, and the resulting type cannot be determined.

But that's it. You won't encounter any other data types in a semantic model, than what's listed above. However, for various reasons, you may sometimes encounter different names for these types, which may cause some confusion.

For example, the CURRENCY function returns a Decimal value, the INT function returns an Int64 value, and the CONVERT and DATATABLE functions use the keywords INTEGER to mean Int64, and CURRENCY to mean Decimal.

Moreover, Power BI Desktop lists "Date" and "Time" as explicit data types in addition to "Date/time", but this is misleading as all three use the DateTime data type internally. Power BI simply applies different FormatStrings based on which of these "data types" you choose.

New data type inspection function

In the most recent version of Power BI Desktop, and on the Power BI Service, I noticed that a handful of new DAX functions were recently added. While these functions have not been officially announced or documented by Microsoft yet, their names provide a straightforward indication of what the purpose of them are:

ISBOOLEAN( <value> )
ISDATETIME( <value> )
ISDECIMAL( <value> )
ISDOUBLE( <value> )
ISINT64( <value> )
ISSTRING( <value> )
ISNUMERIC( <value> )        

As you can see, these functions use the same names as the data type names I listed above, with the exception of ISNUMERIC, but we'll get to that in a second. Good on Microsoft for being consistent, although I'm afraid much of the damage is already done because of the CURRENCY/DECIMAL, LOGICAL/BOOLEAN, and INT/INTEGER/INT64 confusion I mentioned above.

There is also a bit of overlap between these new functions, and the existing ones:

ISBOOLEAN is an alias for ISLOGICAL.

ISSTRING is an alias for ISTEXT.

ISNUMERIC returns true if the value is either Int64, Decimal, or Double (note, it does not return true if the value is a DateTime, so in this respect the function behaves differently from ISNUMBER).

Regrettably, there is no ISBINARY function, but you can obtain the same check by combining some of the existing functions:

// Returns true if <value> is a Binary:
NOT ( ISNUMBER( <value> ) || ISSTRING( <value> ) || ISBOOLEAN( <value> ) )        

To make sure our understanding is correct, let's run a DAX query to see which result each of the 11 functions mentioned in this article produces, when called on an input value of each of the 7 data types:

You can find the DAX query here if you want to test this yourself.

Conclusion

The main take away from this article is that:

• There are only 7 data types in a semantic model (8 if you include Variant).
• Be aware of differences in how data types are named internally vs. in client tools vs. in DAX functions and keywords.
• Be aware that Date and Time are just DateTime values with different format strings applied.
• New DAX functions are now available to obtain the exact data type of a value.