Welcome to the Power Bi Blog Summary December 2022!

This month, Microsoft 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!

AGENDA

Reporting

Slicer type formatting moved to Format Pane

Modeling

Making it easier to do comparison calculations

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)

Service

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

Mobile

Track your Metrics on the Windows app

Developers

Dynamically setting data chunk size

Visualizations

New visuals in AppSource
Update on Charticulator status

 

 

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
Diagram Description automatically generated Graphical user interface, application Description automatically generated

 

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.

Graphical user interface, application Description automatically generated

 

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’:

 

Table Description automatically generated with medium confidence

 

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:

Table Description automatically generated

 

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:
Table Description automatically generated

 

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:

Chart, bar chart Description automatically generated

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:
Chart, bar chart Description automatically generated

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:

 

Chart, bar chart Description automatically generated

 

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:

Chart, bar chart, histogram Description automatically generated

 

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.

Graphical user interface, website Description automatically generated
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

 

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

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.

 

Image of the old Get Data page in the Power BI service

 

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.

 

Image of the new File Upload button in workspaces in the Power BI service

 

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.

 

Image of the New Dataset button in workspaces in the Power BI service

 

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.

 

Image of the Excel, CSV, and Enter Data options on the new page you will be able to access from the New Dataset option in workspaces.

 

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!

Graphical user interface, text, application, email Description automatically generated

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:

Update on Charticulator status

Micorosoft have heard some of you asking, so they 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. Microsoft 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. Microsoft appreciate the passionate community that has grown around Charticulator, and look forward to continuing to see the beautiful and powerful visuals you all put together!

 

Source: Rien Hu, Power BI Blog

Posted in DatenvisualisierungTags

About Author: Florian Grell

 Managing Director