Welcome to the December 2022 update! This month, we’ve added a variety of new DAX functions, updated slicer type formatting, and brought Metrics to the Windows app. There is more to explore, so please read on!
Data connectivity and preparation
- Anaplan (Connector update)
- Azure Databricks, Databricks (Connector update)
- CData Connect Cloud (New Connector)
- Cosmos DB V2 (New Connector)
- Dremio Cloud (Connector update)
- Google BigQuery (Azure AD) (New Connector)
- Power BI org app Multiple Audiences will be generally available next year
- Announcing the deprecation of ‘Getting Started’ in the Expanded View of the Power BI Service
- Upcoming changes to the ‘Get Data’ experience in the Power BI Service
Check out the video below for this month’s summary:
Reporting
Slicer type formatting moved to Format Pane
Previously, to change a slicer’s type, for example changing from relative date to a slider, these settings were only available in the visual header and only on hover. In addition, to change a slicer to “horizontal” required users to first choose “list” from the visual header to see the option in the format pane and then use the format pane to swap the orientation.
Now, these settings live in 1 place in the format pane making it easier to discover and change between slicer types consistently. Note, horizontal has now been renamed to “tile” based on user feedback.
| Before | After |
 |
 |
Another added benefit with this change is the new mobile formatting options now have access to this setting too! Users can quickly update their mobile layout slicers to use tile to be more mobile friendly.
Modeling
Making it easier to do comparison calculations
This month we are introducing multiple new functions for DAX, targeted at making it easier to do comparison calculations in Power BI. The new functions we are introducing are: INDEX, OFFSET and WINDOW:
- INDEX retrieves a result using absolute positioning.
- OFFSET retrieves a result using relative positioning.
- WINDOW retrieves a slice of results using absolute or relative positioning.
These functions also come with two helper functions called ORDERBY and PARTITIONBY.
These functions will make it easier to perform calculations such as:
- Comparing values vs a baseline or finding another specific entry (using INDEX)
- Comparing values against a previous value (using OFFSET)
- Adding a running total, moving average or similar calculations that rely on selecting a range of values (using WINDOW).
If you are familiar with the SQL language, you can see that these functions are very similar to SQL window functions. The functions we are releasing today perform a calculation across a set of table rows that are in one way or another related to the current row. These functions are different from SQL window functions, because of the DAX evaluation context concept, which will determine what is the “current row”. Moreover, the functions we are introducing today don’t return a value but rather a set of rows which can be used together with CALCULATE or an aggregation function like SUMX to calculate a value.
Note that this group of functions is not pushed to the data source, but rather they are executed in the DAX engine. Additionally, we have seen much better performance using these functions compared to existing DAX expression to achieve the same result, especially when the calculation requires sorting by non-continuous columns.
As you can see below, the DAX required to perform these calculations is indeed easier than the DAX required without them. However, while these new functions are very powerful and flexible, they still require a fair amount of complexity to make them work correctly. That is because we opted for high flexibility for these functions. We do recognize there is a need for easier to use functions that sacrifice some of the flexibility in favor of easier DAX. The functions we release today are just a stepping stone, a building block if you will towards our goal to make DAX easier – they are the foundation of things to come! If after reading this section you don’t feel like these functions are for you because of the complexity, rest assured that we are aware and are working on making DAX easier for you as well!
INDEX
INDEX allows you to perform comparison calculations by retrieving a row that is in an absolute position.
This will be most useful for comparing values against a certain baseline or another specific entry.
Here’s an example of how to use INDEX. Below is a table of customer names and birth dates whose last name is ‘Garcia’:
Now, let’s say you wanted to find the oldest customer for each last name. So for the last name ‘Garcia’ that would be Adriana Garcia, born December 4th, 1957. You can add the following calculated column on the DimCustomer table to achieve this goal and return the name:
Oldest Customer of LastName = SELECTCOLUMNS(INDEX(1,DimCustomer, ORDERBY([BirthDate]), PARTITIONBY([LastName])), [FullName])
This returns the following result:
In the example above we showed only customers whose last name is ‘Garcia’. However, the same calculated column works on a set that has more than one last name:
As you can see in the screenshots above, the full name of the oldest person with that last name is returned. That’s because we instructed INDEX to retrieve the first result when ordering by birth date, by specifying 1. By default, the ordering for the columns passed into OrderBy is ascending. If we would have specified 2, we should have retrieved the name of the second oldest person with th last name instead, and so on.
Had we specified -1 or changed the sort order we would have returned the youngest person instead:
Youngest Customer of LastName = SELECTCOLUMNS(index(1,DimCustomer, orderBy([BirthDate], DESC), partitionBy([LastName])), [FullName])
Is equivalent to:
Youngest Customer of LastName = SELECTCOLUMNS(index(-1,DimCustomer, orderBy([BirthDate]), partitionBy([LastName])), [FullName])
Read more about INDEX in our documentation. Notice that INDEX relies on two other new helper functions called ORDERBY and PARTITIONBY.
OFFSET
This month we are officially shipping OFFSET. OFFSET allows you to perform comparison calculations more easily by retrieving a row that is in a relative position from your current position. This will be most useful for comparing across something else than time, for example across Regions, Cities or Products. For date comparisons, for example comparing the Sales for this quarter vs the same quarter last year we already have dedicated Time Intelligence functions in DAX. That doesn’t mean you cannot use OFFSET to do the same, but it is not the immediate scenario.
So, what is the scenario for OFFSET? Well, let’s look at an example. Here’s a bar chart that shows total sales by product color:
Now, let’s say you wanted to compare how well each color is doing against the color above it in the chart. You could write a complicated DAX statement for that, or you can now use OFFSET to accomplish this goal:
TotalSalesDiff = IF(NOT ISBLANK([TotalSales]), [TotalSales] - CALCULATE([TotalSales], OFFSET(-1, FILTER(ALLSELECTED(DimProduct[Color]),NOT ISBLANK([TotalSales])))))
This will return the following result:
As you can see the newly added bars calculate the difference for each color compared to the one just above it in the chart. That’s because we specified -1 for the first parameter to OFFSET. If we had specified -2 we would have made the comparison against the color above each color, but skipping the one right above it, so effectively the sales for the grey color would have been compared against the sales for products that were black.
Read more about OFFSET in our documentation.
WINDOW
WINDOW allows you to perform calculations that rely on ranges of results (“windows”), such as a moving average or a running sum.
Here’s an example of how to use WINDOW. The below column chart shows total sales by year and month:
Now, let’s say you wanted to add a moving average for the last three months of sales including the current. For example, for September 2017, we expect the result to be the average sales of July, August and September in 2017 and for February 2018, we expect the result to be the average sales for December 2017, January 2018 and February 2018.
To meet this requirement, you could write a complicated DAX statement, or you can now use WINDOW to accomplish this goal using a simpler DAX statement:
MovingAverageThreeMonths = AVERAGEX(WINDOW(-2, 0, ALLSELECTED(DimDate[CalendarYear],DimDate[MonthName],DimDate[MonthNumberOfYear]), ORDERBY(DimDate[CalendarYear],ASC,DimDate[MonthNumberOfYear],ASC)), [TotalSales])
This will return the following result:
As you can see the newly added line correctly calculates the average sales over three months (including the current month). This release on a so-called ‘relative window’: the first parameter to WINDOW is set to -2, which means that the start of the range is set two months before to the current month (if that exists). The end of the range is inclusive and set to 0, which means the current month. Absolute windows are available as well, as both the start and end of the range can be defined in relative or absolute terms. Notice that WINDOW relies on two other new functions called ORDERBY and PARTITIONBY.
Read more about WINDOW in our documentation.
ORDERBY and PARTITIONBY
These helper functions can only be used in functions that accept an orderBy or partitionBy parameter, which are the functions introduced above. The PARTITIONBY function defines the columns that will be used to partition the rows on which these functions operate.
The ORDERBY function defines the columns that determine the sort order within each of a window function’s partitions specified by PARTITIONBY.
Data connectivity and preparation
Anaplan (Connector update)
This version of Power BI connector for Anaplan includes backend changes for compatibility with ongoing Anaplan infrastructure updates. There is no change to user facing connector features.
Azure Databricks, Databricks (Connector update)
The Azure Databricks and Databricks connectors now support native queries.
CData Connect Cloud (New Connector)
We are excited to announce the release of the new CData Connect Cloud connector! Here are notes from the CData team.
CData Connect Cloud brings real-time data access to hundreds of new cloud applications, databases, and APIs from within Power BI. CData Connect Cloud ensures that everyone can access the data they need, whenever and wherever they need it. Real-time data connectivity in the cloud means no installation, delays, or complex data pipelines. This frictionless solution allows customers to take advantage of the most current data available to make real-time data driven decisions using the tool they know and love – Power BI. Get started for today with CData Connect Cloud!
Cosmos DB V2 (New Connector)
We are excited to announce the release of the Azure Cosmos DB V2 connector in the upcoming December 2022 update!
The new V2 connector will support querying the Cosmos DB transactional store in both DirectQuery and Import modes. The DirectQuery mode will enable query pushdown, including aggregations to the Cosmos DB container when a filter on partition key is specified.
The DirectQuery mode in the V2 connector will be helpful in scenarios where Cosmos DB Container data is large and it is not feasible to import it all to Power BI cache in the Import mode. It will also be helpful in user scenarios where real-time reporting with the latest Cosmos DB data is a requirement. In addition to supporting DirectQuery mode, the V2 connector includes performance optimizations related to query pushdown and data serialization.
Note that due to a known issue that is being fixed and deployed, support for this feature in Premium and end-to-end cloud refresh may not work until mid-January.
Dremio Cloud (Connector update)
This release contains a fix that allows query folding on data containing DECIMAL data types to be pushed down to Dremio.
This improves performance by reducing the volume of data Power BI is required to read.
Google BigQuery (Azure AD) (New Connector)
We are excited to announce the release of a new Google BigQuery connector leveraging Azure Active Directory authentication. Users can use Azure Active Directory-based Single Sign-On through Power BI Service and Gateway using this connector. Learn more at https://aka.ms/GoogleBigQueryAADSSO
Service
Power BI org app Multiple Audiences will be generally available next year
Following our last announcement in August of launching the public preview of Multiple Audiences for Power BI org apps, we are excited to share that this feature will be generally available by early next year! As stated, with multiple audiences for an App Power BI App author can create multiple audience groups within the same App and assign different permissions to each group. Learn more Publishing an org app in Power BI
Announcing the deprecation of ‘Getting Started’ in the Expanded View of the Power BI Service
The ability to view ‘Getting Started’ content through the expanded view from the Power BI service will be retired starting mid-December.
The Power BI Service’s expanded view currently gives you the option to view “Getting Started” content at the end of your Home Page. From the “Getting Started” section, you can utilize information on how to get started using Power BI, tips and tricks on how to create/utilize reports and dashboards, etc. Due to low usage from Power BI users, support for the “Getting Started” section will be removed and you will no longer be able to access the content above through this area. Retirement of the “Getting Started” section will open the Power BI service’s expanded view Homepage for new additions in the future.
Though you will not be able to view the ‘Getting Started’ content directly through the Power BI service, the content is still publicly available to you! If you are a new Power BI user, some of the content from the “Getting Started” section will be available to you through your “Recommended” carousel.
If you are not a new user, the “Getting Started” content is still publicly available to you through the links below:
Power BI Basics
- Explore basic Power BI concepts – The Power BI service – basic concepts for beginners – Power BI | Microsoft Learn
- Intro: What is Power BI? – Get started building with Power BI – Training | Microsoft Learn
- Quick Start: Getting around in the Service – Getting around in the Power BI service – Power BI | Microsoft Learn
- View and understand a Power BI report – View a report – Power BI | Microsoft Learn
- How to: Know your Power BI content is up to date – How to be sure that content is up to date – Power BI | Microsoft Learn
Sample Reports
- All Sample reports are viewable in the ‘Learn’ center (Located on the left navigation pane within the Power BI Service).
How to create reports
- Learn how: Connect to data sources – Quickstart Connect to data in Power BI Desktop – Power BI | Microsoft Learn
- Learn how: Read reports in the Service – Part 1, Add visualizations to a Power BI report – Power BI | Microsoft Learn
Upcoming changes to the ‘Get Data’ experience in the Power BI Service
As called out in the November blog, we’re in the process of removing the older ‘Get Data’ page in the Power BI service in favorite of new, comparable features available within workspaces. The change that will remove the entry points to the old ‘Get Data’ page, which is shown before reference, will be slowly rolling out during the month of January.
Going forward, you’ll be able to access comparable features within workspaces. If you want to upload a file to Power BI, such as a .pbix, .xlsx, or .rdl file to your workspace, you can use the Upload option that was released in November. This option lets you upload files from your local computer or connect to files on OneDrive or a SharePoint site. With this change, you’ll no longer be able to connect to files on personal OneDrive accounts.
If instead you want to create a dataset from Excel or CSV data, you can now access that functionality through the New > Dataset option in the workspace you want to create the dataset in.
At the same time as we’re rolling out the change to remove the older ‘Get Data’ experience, we’ll also be updating this Dataset option to take you to a new page with options to create a dataset off an Excel, CSV, or pasting in data. Once you select the file, the behavior used to generate the dataset is the same as previously used on the ‘Get Data’ page. Once the dataset is created, you’ll be taken to the dataset’s details page in the Data hub.
As mentioned earlier, we’ll be rolling out this change slowly over the month of January, so you may not see the update immediately. In the meantime, if you have any questions about the upcoming changes, let us know.
Mobile
Track your Metrics on the Windows app
Now you can monitor your Power BI metrics and scorecards and even check in progress, directly from the Windows app!
Developers
Dynamically setting data chunk size
We have enhanced the fetch more data API to allow report authors to set the data chunk size dynamically by using the new property dataReductionCustomization.
This is available with the new 5.2 API release. For more details please check out this article.
Visualizations
New visuals in AppSource
The following are new visuals this update:
- kpi emoji
- GANTT by Lingaro
- Aimplan Planning and Reporting Visual
- Excalibur
- 3DBI
- verticalText by sio2Graphs
- Definitive Logic Advanced Gantt Chart
- swColorMap_twoLevels
- 100% Clustered Stacked Bar Chart (Standard)
- 100% Clustered Stacked Column Chart (Standard)
- Advanced Line Chart (Standard)
- Bubble Chart with Categorical Data (Standard)
- Dual X-axis Bar Chart (Standard)
- Dual Y-Axis Column Chart (Standard)
- Likert Scale (Standard)
- Lollipop Bar Chart (Standard)
- Overlapping Column (Standard)
- Pie Chart with Full Legend Label (Standard)
- Lollipop Column Chart (Standard)
- Population Pyramid (Standard)
- Dual X-Axis Combo Chart (Standard)
- Dual Y-Axis Combo Chart (Standard)
- Horizontal Bullet Chart (Standard)
- Merged Bar Chart (Standard)
- Overlapping Bar (Standard)
- Vertical Bullet Chart (Standard)
- Multiple Vertical Line Chart (Standard)
- OneTax-PowerBIvisual
Update on Charticulator status
We’ve heard some of you asking, so we would also like to give you a status update on the Charticulator custom visual and the visual creation tool it’s built on. The Microsoft Research team which built the tool has moved on to new projects, and ownership of the technology is being transitioned to a team in Power BI. We will continue to fix major bugs and actively maintain the quality of the current tool, though feature-level development has been put on hold for now. We appreciate the passionate community that has grown around Charticulator, and we look forward to continuing to see the beautiful and powerful visuals you all put together!
And that’s all for this month! Please continue sending us your feedback, and don’t forget to vote for other features you would like to see in Power BI! We hope that you enjoy the update!
A few quick reminders:
If you installed Power BI Desktop from the Microsoft Store, please leave us a review.
Also, don’t forget to vote on your favorite feature this month over on our community website.
And as always, keep voting on Ideas to help us determine what to build next.
We look forward to hearing from you soon!