Here we are, at the end of another year, to see what happened in the DAX world.
New DAX functions in 2022
Microsoft released eight new DAX functions:
- NETWORKDAYS: returns the number of working days between two dates.
- TOJSON: converts the records of a table into JSON text.
- TOCSV: converts the records of a table into a CSV text.
- EVALUATEANDLOG: logs the value of an expression in the DAX evaluation log.
- USERCULTURE: returns the culture code for the report user.
- INDEX: retrieves a row in a table within the specified partition and sort order.
- OFFSET: retrieves a row in a table by moving a number of rows within the specified partition and sort order.
- WINDOW: retrieves a range of rows within the specified partition and sort order.
Another important event for the DAX world has been the release of DAX Studio 3, with an important restyling of the user interface and user experience.
After two years, the composite models – whose official name is DirectQuery for Power BI datasets and Analysis Services – are still in preview. General availability should probably happen in 2023. In the meantime, the feature has been refined and improved. At SQLBI we also released new content about this topic.
What SQLBI delivered in 2022
This year we restarted delivering in-person courses and speaking at public events. As we continue to produce new content in textual and video form, we have a limit to the number of in-person events we can deliver, but we are excited about meeting people again!
Besides public events, we worked on many other things:
- We released SQLBI+, a new subscription service for advanced content that supports professional model authors who create semantic models for Power BI and Analysis Services. This subscription already includes two courses (Writing DAX Queries and Composite Models), and we will add at least two other courses and four sessions every year.
- We released Bravo for Power BI, a new free, open-source external tool for Power BI that helps you analyze a model, export data, format DAX, and create a Date table and time intelligence measures.
- Our SQLBI YouTube channel surpassed 65,000 subscribers. We introduced a new series (The Whiteboard) and added a few other videos to the unplugged series. We also continued to publish a video for each corresponding article every other week.
- We published more than 30 new articles and blog posts at sqlbi.com.
- We released Contoso Data Generator, a free and open-source tool to generate sample databases on SQL Server. We use these sample databases in all our articles, books, and courses.
- We released DaxTemplate, an open-source library that Bravo currently uses for Power BI to create the tables and measures based on JSON templates that can be edited by using the Bravo Template Editor extension for Visual Studio Code.
Well, this year we also must disclose what we did not deliver on time. We are still working on the new version of the Optimizing DAX video course, which was expected by the end of 2022. More about this in the next section!
What’s coming in 2023
The best part is always what’s next:
- SQLBI Courses: We will release the new version of Optimizing DAX in 2023 – the video course. Hopefully, in the first few months. We started this project in 2021, and we have not completed it yet! We are close but not there yet. We will also produce new content for the SQLBI+ subscription.
- New features in DAX: The new filter functions (INDEX, OFFSET, WINDOW) that appeared at the end of 2022 seem to be just the first step of a set of new features for DAX. It seems we’ll see the biggest change in DAX since calculation groups!
- Tools: expect more news in this area. Is there something we can do after Bravo for Power BI? Yes, we are already working on new features for Bravo and something else, too. Stay tuned!
- Content: as usual… More articles, more videos, more content.
Enjoy DAX with us also in 2023!
Returns the number of whole workdays between two dates (inclusive) using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.
NETWORKDAYS ( <start_date>, <end_date> [, <weekend>] [, <holidays>] )
Converts the records of a table into a JSON text.
TOJSON ( <Table> [, <MaxRows>] )
Converts the records of a table into a CSV (comma-separated values) text.
TOCSV ( <Table> [, <MaxRows>] [, <Delimiter>] [, <IncludeHeaders>] )
Return the value of the first argument and also log the value in DAX evaluation log.
EVALUATEANDLOG ( <Value> [, <Label>] [, <MaxRows>] )
Returns the culture code for the user, based on their operating system or browser settings.
USERCULTURE ( )
Retrieves a row at an absolute position (specified by the position parameter) within the specified partition sorted by the specified order or on the axis specified.
INDEX ( <Position> [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] )
Retrieves a single row from a relation by moving a number of rows within the specified partition, sorted by the specified order or on the axis specified.
OFFSET ( <Delta> [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] )
Retrieves a range of rows within the specified partition, sorted by the specified order or on the axis specified.
WINDOW ( <From> [, <FromType>], <To> [, <ToType>] [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] )
The columns used to determine how to partition the data. Can only be used within a Window function.
PARTITIONBY ( [<PartitionBy_ColumnName> [, <PartitionBy_ColumnName> [, … ] ] ] )
The expressions and order directions used to determine the sort order within each partition. Can only be used within a Window function.
ORDERBY ( [<OrderBy_Expression> [, [<OrderBy_Direction>] [, <OrderBy_Expression> [, [<OrderBy_Direction>] [, … ] ] ] ] ] )