Power Pivot Principles: The A to Z of DAX Functions – CONVERT
26 July 2022
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 CONVERT.
The CONVERT function
The CONVERT function is used to convert an expression of one Data Type to another. It employs the following syntax to operate:
CONVERT(expression, datatype)
- expression: this is required. This is any valid DAX expression
- datatype: also required, this represents an enumeration that includes:
- INTEGER (Whole Number)
- DOUBLE (Decimal Number)
- STRING (Text)
- BOOLEAN (TRUE / FALSE)
- CURRENCY (Fixed Decimal Number)
- DATETIME (Date, Time, etc.).
It should be further noted that:
- the function returns an error when a value cannot be converted to the specified datatype
- DAX calculated columns must be of a single data type. Since MEDIAN and MEDIANX functions over an integer column return mixed data types, either integer or double, the following calculated column expression will return an error as a result:
MedianNumberCarsOwned = MEDIAN(DimCustomer[NumberCarsOwned])
- to avoid mixed datatypes, change the expression to always return the double Data Type, e.g.
MedianNumberCarsOwned = MEDIANX(DimCustomer, CONVERT([NumberCarsOwned], DOUBLE))
- this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
The following DAX query
EVALUATE { CONVERT(DATE(1900, 1, 1), INTEGER) }
returns
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.
