August 2019 saw the introduction of two new DAX functions: CONVERT and REMOVEFILTERS. They are so new that at the time of writing this blog post (23 August 2019), they are only available in Azure Analysis Services and Power BI service, and even DAX Guide does not list them. In this blog post I’m showing what they are doing.
CONVERT
The function allows you to explicitly convert the data type of an expression to the one you specify. It takes two parameters:
- Scalar expression
- Data type
The second parameter, data type, can be one of the following:
- BOOLEAN
- CURRENCY
- DATETIME
- DOUBLE
- INTEGER
- STRING
While we could do data type conversions before with functions like CURRENCY and INT, this function is universal and easier to remember. If you want to convert a value to text formatted in a certain way, you’ll still have to use the FORMAT function.
REMOVEFILTERS
This is our old friend, ALL, with a new name — almost. This function can only be used as a filter in CALCULATE — it cannot be used as a table expression.
As a CALCULATE filter, the function works exactly like ALL and accepts the following arguments:
- Single table name
- Single column name
- Multiple columns from the same table
- No arguments
When you use REMOVEFILTERS with no arguments, you ignore filters from the entire data model.
In a sense, REMOVEFILTERS is to ALL what RELATEDTABLE is to CALCULATETABLE: an alias with reduced functionality that can make your code more readable, which is always a good thing.
From The Definitive Guide to DAX (2019):
When used as a table function, ALL is a simple function. It returns all the distinct values of one or more columns, or all the values of a table. When used as a CALCULATE modifier, it acts as a hypothetical REMOVEFILTER function.
The function is not so hypothetical anymore 🙂
You can already try the new functions by connecting to a dataset published to Power BI service!