The second year of the pandemic is coming to a close and despite the many uncertainties, we are all striving to push forward and look ahead. For sure, it is a good moment to see what happened in the DAX world.
New DAX functions in 2021
Microsoft released 8 new DAX functions, plus several other undocumented functions to support composite models. The 8 documented functions are:
- ISAFTER : this is a small variation of the ISONORAFTER function.
- HASH : computes a hash over one or more expressions passed as arguments.
- NAMEOF : returns the fully-qualified name of a column or measure reference.
- BITAND : bitwise AND of two numbers.
- BITOR : bitwise OR of two numbers.
- BITXOR : bitwise XOR of two numbers.
- BITLSHIFT : returns a number shifted left by the specified number of bits.
- BITRSHIFT : returns a number shifted right by the specified number of bits.
There were another three important events for the DAX world:
- Daniel Otykier released Tabular Editor 3, which includes the most productive DAX editor in the world. Any professional developer should use a version of Tabular Editor for the reasons described in Development tools for Tabular models in 2021.
- Microsoft simplified the syntax required to create multi-column filters in CALCULATE, as described in Specifying multiple filter conditions in CALCULATE.
- Power BI Desktop now supports the Detail Rows Expression property required by the drillthrough feature in Excel PivotTables. You can see how to use this feature in Controlling drillthrough in Excel PivotTables connected to Power BI or Analysis Services.
After one year, the composite models – whose official name is DirectQuery for Power BI datasets and Analysis Services – are still in preview. There have been many improvements, but the feature is still not ready for production. In the meantime, we worked on content about composite models (and much else) that we will publish in 2022.
What SQLBI delivered in 2021
Being unable to plan in-person events, we redirected these resources towards long-term investments. Some have already been presented to you in 2021 and others will be delivered in the following years.
- We published the new Mastering Tabular video course, a massive 35 hours of training over 156 lectures with all you need to create an enterprise-level solution based on Power BI or Analysis Services. This video course has replaced the former SSAS Tabular video course, tripling the volume of content to now also cover Power BI Service and Azure Analysis Services.
- We released DAX.do, a playground to write, share, and run DAX queries over a sample Contoso database. More details in the Introducing DAX.do article.
- We launched Power BI Push Tools, a set of open-source tools to work with Power BI push datasets.
- The SQLBI Newsletter reached its 200th issue, a milestone that we celebrated with a vintage layout and by trolling the Marketing department.
- Our SQLBI YouTube channel surpassed 40,000 subscribers, more than double the subscribers we had at the end of 2020. We created an unplugged series and improved the connections between YouTube videos and related SQLBI articles.
- We added to DAX Guide a video for every DAX function. We have been releasing two of these videos every week on YouTube, but all the videos are already available at https://dax.guide.
- We improved the SQLBI Video Learning Platform by upgrading the features already available to you in 2020. These features are available for all the SQLBI video courses, including free courses.
- SQLBI became the first gold sponsor of DAX Studio. As we explained in Development tools for Tabular models in 2021, the Power BI ecosystem needs there to be a sustainable economic model for tools that enhance productivity. To keep DAX Studio free for everyone, you may want to also contribute with a small donation.
What’s coming in 2022
The best part is always what’s next:
- Courses: Our priority is a new version of Optimizing DAX, while keeping other courses updated when new features are released in general availability. We are also working on additional content for professional Tabular model authors – we cannot share many details yet, but you should see something new in the first months of 2022.
- DAX Debugger: Daniel Otykier announced a new DAX Debugger feature for Tabular Editor 3. This will be a game-changer also for people teaching or learning DAX. You can watch the presentation made a couple of weeks ago, or just wait for the first preview in January 2022.
- Tools: in 2020, SQLBI started investing in new tools that did not end up seeing the light in 2021. The only reason for this is that we never compromise on quality. However, 2022 is the year for these new tools from SQLBI. Stay tuned.
- Content: last but not least… More articles, more videos, more content.
If you want to receive regular updates about SQLBI, go ahead: register to our newsletter and subscribe to our YouTube channel.
We look forward to farther serving your development in 2022!
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>] [, … ] ] ] ] )
Compute hash over a variable number of input expressions and return a value.
HASH ( <Expression> [, <Expression> [, … ] ] )
Returns the name of a column or measure.
NAMEOF ( <Value> )
Returns a bitwise ‘AND’ of two numbers.
BITAND ( <Number1>, <Number2> )
Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE.
AND ( <Logical1>, <Logical2> )
Returns a bitwise ‘OR’ of two numbers.
BITOR ( <Number1>, <Number2> )
Returns TRUE if any of the arguments are TRUE, and returns FALSE if all arguments are FALSE.
OR ( <Logical1>, <Logical2> )
Returns a bitwise ‘XOR’ of two numbers.
BITXOR ( <Number1>, <Number2> )
Returns a number shifted left by the specified number of bits.
BITLSHIFT ( <Number>, <ShiftAmount> )
Returns a number shifted right by the specified number of bits.
BITRSHIFT ( <Number>, <ShiftAmount> )
Evaluates an expression in a context modified by filters.
CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )