At the end of a year marked by the pandemic, it is time for our usual review of what happened in the DAX realm. The enthusiasm we typically demonstrate in this traditional end-of -year post is tainted this year by thoughts about the people who were impacted more or less deeply by the pandemic. We feel for those who lost a loved one, lost their job, lost their business. We realize that we were blessed to remain healthy and safe, and to remain able to continue doing what we love.
There was nothing normal about this year, and we acknowledge that. Not the most fun way to start a piece, but we did want to acknowledge the reality of a lot of people in our audience.
Now, let’s take a close look at the DAX world.
New DAX functions in 2020
Microsoft released 4 new DAX functions, and added 49 financial functions to DAX which correspond to identical Excel functions:
- IF.EAGER : This special version of the IF function enforces eager evaluation. It was publicly visible in a few versions of Power BI Desktop and Analysis Services, but was then hidden by Microsoft. It should not be used unless you face particular optimization requirements.
- COALESCE : Returns the first argument that is not blank, similar to the same function in SQL. It is just syntax sugar, but it makes the code more readable.
- USERCULTURE : Returns the culture code for the user, allowing DAX expressions to return localized values depending on the user.
- ISAFTER : Close companion to ISONORAFTER, with a different result when the values being compared are identical.
- Financial functions: DAX now has 51 financial functions, thanks to the 49 functions added to the existing XIRR and XNPV functions. The latter are still the only functions that work as iterators in the financial group.
There were another two important events for DAX:
- The release of the External Tools feature in July allowed tools like Tabular Editor and DAX Studio to interoperate with Power BI Desktop. The more important consequence was the possibility to now create Calculation Groups and KPIs in all the Power BI models.
- “DirectQuery for Power BI datasets and Analysis Services” released in December is a huge feature we had been waiting for years, which also introduces new features in DAX – not documented yet. We will talk more about this feature in 2021. For now, we only know that we will continue to call it “composite models” more often than other marketing names.
What SQLBI delivered in 2020
This year we suspended most of the classroom course activity and on-site training, all the while accelerating other projects and investments that were originally planned over a longer period.
- SQLBI YouTube channel: we increased the production of videos related to SQLBI articles, and included videos that do not have a corresponding textual content on the web.
- We released the second edition of DAX Patterns, including a new book and a set of videos to explain how the formulas work in more detail.
- We added new content to the Mastering DAX video course, with 5 new exercises in the calculation groups section, and 48 new videos explaining the solution for each exercise of the entire course.
- We delivered new features in the SQLBI Video Learning Platform: complete transcripts synchronized with the video, the option to highlight parts of the transcript and save them as a bookmark, new customized layout options including dark mode, and more. The new features are available in all the video courses, including the free ones.
- We published the free DAX Tools video course, which teaches you how to use three popular tools to write and optimize DAX: DAX Studio, VertiPaq Analyzer, and Analyze in Excel for Power BI Desktop.
- We shipped the first release of the free, open-source Analyze in Excel for Power BI Desktop
- We improved the reliability and scalability of the DAX Formatter
- As part of the Power BI contributor program, we helped Microsoft adopt standard DAX separators in Power BI Desktop and implement the Shadow property in Power BI.
What’s coming in 2021
We are focusing our efforts on these areas:
- Courses: we plan on refreshing several courses, in order to keep the content up to date. The priorities are SSAS Tabular Workshop and Optimizing DAX, even though Mastering DAX will get some additional content if required. The new composite models are going to introduce new DAX features; we will cover them.
- Tools: we will continue to contribute to tools that help write and optimize DAX. While we are glad about the success of existing tools (DAX Tools, Tabular Editor 2) and excited for the latest announcements – Tabular Editor 3 is entering the preview phase – there is at least one more thing coming in 2021. Unfortunately, it is still under NDA.
- DAX Guide: we are working on several improvements for DAX Guide. We cannot share the details yet, but the result should include more content and additional help to DAX users.
- Content: it is simple, content is king. We will act accordingly. Expect more articles, more videos, more content.
If you want to receive updates promptly about SQLBI, go ahead, register to our newsletter and subscribe to our YouTube channel.
We look forward to a bright and more stable new year 2021!
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
IF ( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )
Returns the first argument that does not evaluate to a blank value. If all arguments evaluate to blank values, BLANK is returned.
COALESCE ( <Value1>, <Value2> [, <Value2> [, … ] ] )
Returns the culture code for the user, based on their operating system or browser settings.
USERCULTURE ( )
Returns true if the list of Value1 parameters compares strictly after the list of Value2 parameters.
ISAFTER ( <Value1>, <Value2> [, [<Order>] [, <Value1>, <Value2> [, [<Order>] [, … ] ] ] ] )
The IsOnOrAfter function is a boolean function that emulates the behavior of Start At clause and returns true for a row that meets all the conditions mentioned as parameters in this function.
ISONORAFTER ( <Value1>, <Value2> [, [<Order>] [, <Value1>, <Value2> [, [<Order>] [, … ] ] ] ] )
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
XIRR ( <Table>, <Values>, <Dates> [, <Guess>] [, <AlternateResult>] )
Returns the net present value for a schedule of cash flows.
XNPV ( <Table>, <Values>, <Dates>, <Rate> )