We are approaching the end of 2018, so it is a good time to review what happened in the DAX world this year, what we’ve been up to at SQLBI and what is going to happen in 2019.
New DAX functions in 2018
Microsoft released 14 new DAX functions in 2018. These functions are visible in the DAX Guide website, where you can see also the compatibility with different versions of the products supporting DAX. However, there are no news about the DAX syntax, we hope to see something coming in 2019.
- APPROXIMATEDISTINCTCOUNT: this function should be just a different option of DISTINCTCOUNT, because it only works for DirectQuery with Azure SQL Database and Azure SQL Data Warehouse. Because it is in preview, we hope that Microsoft will revert back to a better naming convention.
- ISINSCOPE: this function makes it easier to compute measure such as a ratio over parent.
- NONVISUAL: this is a new filter option for SUMMARIZECOLUMNS introduced to support the new features in Power BI matrix.
- UTCNOW: this is the current date and time in Coordinated Universal Time (UTC).
- UTCTODAY: this is the current date in Coordinated Universal Time (UTC).
- Normal distribution and Student’s T-distribution functions:
What SQLBI delivered in 2018
This year at SQLBI we delivered 33 public courses in three continents, several regular and preconference sessions in 5 major conferences, several SQL Saturdays, and other community events.
We have been writing a large part of the second edition of The Definitive Guide to DAX. We should complete this task in early 2019 and the book should be available by mid-2019. We do not have any idea why some websites announced a publishing date in 2018. We will not finalize the book until Microsoft will release certain new DAX features. Thus, we know how unreliable any expected date is here.
We launched one new video course: Data Modeling for Power BI. This video course teaches the data modeling fundamentals that are necessary to understand how to correctly shape and massage the data in any analytical data model. If you want to get good numbers with quick formulas using less and not complicated DAX expressions, this is what you need. If you are aware of star schemas and dimensional modeling, there is nothing new. But any new user of Power BI needs that. Too many times we have seen unnecessary complex DAX expressions just because of a wrong data model. The course is based on the book Analyzing Data with Power BI and Power Pivot for Excel, which despite its name is a book about… data modeling!
We went online with DAX Guide, an online guide about DAX that is improving every week also thanks to the contributions of many readers.
We started the preview of Smart Filter Pro, the first commercial custom visual that OKViz will release in January 2019. Pricing is already available and there is a FAQ page with more details about licensing and support.
What’s coming in 2019
As usual, we have some plans, but something new can always happen in one year:
- We will update our Mastering DAX video course. The updated videos should be available within April 2019.
- We should be publishing a new version of The Definitive Guide to DAX, mid-2019 is the current expected publishing date.
- We will be publishing a new version of the DAX Patterns updated for Power BI. We missed this goal in 2018, but now it will be a top priority in 2019 (after the second edition of the DAX book, of course).
- We will keep on producing content, custom visuals (both commercial and free!), and tools for the BI community.
If you want to receive updates promptly without missing any news, go ahead and register to our newsletter.
We have very much enjoyed serving you, our reader as well as the broader BI community.
Happy 2019!
Returns an estimated count of the unique values in a column. This function invokes a corresponding aggregation operation in the data source, optimized for query performance but with slightly reduced accuracy. You can use APPROXIMATEDISTINCTCOUNT with the following data sources: Azure SQL, Azure SQL Data Warehouse, BigQuery, Databricks, and Snowflake. Note that this function requires DirectQuery mode. Import mode and dual storage mode are not supported.
APPROXIMATEDISTINCTCOUNT ( <ColumnName> )
Counts the number of distinct values in a column.
DISTINCTCOUNT ( <ColumnName> )
Returns true when the specified column is the level in a hierarchy of levels.
ISINSCOPE ( <ColumnName> )
Mark the filter as NonVisual.
NONVISUAL ( <Expression> )
Create a summary table for the requested totals over set of groups.
SUMMARIZECOLUMNS ( [<GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] ] ] )
Returns the current date and time in datetime format expressed in Coordinated Universal Time (UTC).
UTCNOW ( )
Returns the current date in datetime format expressed in Coordinated Universal Time (UTC).
UTCTODAY ( )
Returns the normal distribution for the specified mean and standard deviation.
NORM.DIST ( <X>, <Mean>, <Standard_dev>, <Cumulative> )
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
NORM.INV ( <Probability>, <Mean>, <Standard_dev> )
Returns the standard normal distribution (has a mean of zero and a standard deviation of one).
NORM.S.DIST ( <Z>, <Cumulative> )
Returns the inverse of the standard normal cumulative distribution. The distribution has a mean of zero and a standard deviation of one.
NORM.S.INV ( <Probability> )
Returns the Student’s left-tailed t-distribution.
T.DIST ( <X>, <Deg_freedom>, <Cumulative> )
Returns the two-tailed Student’s t-distribution.
T.DIST.2T ( <X>, <Deg_freedom> )
Returns the right-tailed Student’s t-distribution.
T.DIST.RT ( <X>, <Deg_freedom> )
Returns the left-tailed inverse of the Student’s t-distribution.
T.INV ( <Probability>, <Deg_freedom> )
Returns the two-tailed inverse of the Student’s t-distribution.
T.INV.2T ( <Probability>, <Deg_freedom> )