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:

  1. 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.
  2. 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.

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!

 

 

 

IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.

IF ( <LogicalTest>, <ResultIfTrue> [, <ResultIfFalse>] )

COALESCE

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> [, … ] ] )

USERCULTURE

Returns the culture code for the user, based on their operating system or browser settings.

USERCULTURE ( )

ISAFTER

Returns true if the list of Value1 parameters compares strictly after the list of Value2 parameters.

ISAFTER ( <Value1>, <Value2> [, [<Order>] [, <Value1>, <Value2> [, [<Order>] [, … ] ] ] ] )

ISONORAFTER

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>] [, … ] ] ] ] )

XIRR

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

XIRR ( <Table>, <Values>, <Dates> [, <Guess>] )

XNPV

Returns the net present value for a schedule of cash flows.

XNPV ( <Table>, <Values>, <Dates>, <Rate> )