Power BI Report Server January 2023 Feature Summary
Welcome to the January 2023 Power BI Report Server release! This release has fun and exciting updates to features like the Report Server web portal, Modeling, and Reporting. Please read on for all the details, and feel free to leave your thoughts in the comments below. As always, we would love to hear from you!
Report Server
Power BI Report Server accent color update
Publish .rdl &.pbix files to Power BI from Power BI Report Server
With the announcement of support for paginated reports in Power BI Pro at the PASS Data Community Summit, we also announced as part of SQL Server 2022, a new tool to migrate .rdl reports to the Power BI service. The new migration tool is now available in the January 2023 release of Power BI Report Server.
You can migrate reports without Report Server downtime or disruption to your report users. It’s important to understand that you don’t need to remove any data or reports. You can keep your current environment in place until you’re ready for it to be retired.
Join our new .rdl report community!
Customers looking to migrate .rdl reports to Power BI but have legacy Crystal reports, Cognos, SSRS, etc., infrastructure, or complex queries and datasets? Have you inherited business intelligence as part of your role and are you new to SQL Server Reporting Services (SSRS) or Power BI Report Server? Or perhaps you’ve gone through the migration process and would like to share lessons learned or best practices? Please join our on-premises .rdl migration user group.
Reporting
Reverse stack order for stacked column charts
This release, you can now reverse the order in which we render segments in stacked column and bar charts. This can be particularly helpful to help users match the category groupings of strictly positive columns against vertically aligned legends. We usually draw column segments from the zero-line upwards, but we’ll list those categories in legends in left-to-right, top-to-bottom order. This can sometimes lead to the feeling that their orders are “reversed.”
With this release, you will find a new formatting pane option to reverse stack order of your stacked column and bar, area, ribbon, and combo charts.
The toggle will be off by default. Turning it on will flip the order in which your segments are stacked.
Query performance improvements
This month we have made the following query performance improvements.
Indexing imported string columns on-the-fly
We are building indexes on imported string columns to improve query performance of substring searches, i.e. when using ContainsString function, Search function or a filter to look for a string literal in a text column. Here’s an example of a text filter set using the filter pane:
Pushing Top N filter to DirectQuery sources
Up until now, when using the TOPN function or a top N filter on a column from a DirectQuery source all values of the column would be retrieved and then the top N filter would be applied within the DAX engine. Here’s an example of a top N filter set using the filter pane:
Unshared and unsynchronized axes for small multiples charts
Sometimes, when reading a small multiples visual, a user might not be too concerned in comparing the absolute values of the numbers displayed against each other, instead only interested in comparing the trends of each category across time. But when the ranges of the data for each small multiples category vary wildly, the charts with low maximums get pushed down compared to charts with high maximums. It becomes difficult to evaluate the sales trend for lower charts because it looks close to a horizontal line.
This month, we’ve introduced a couple of new options to help you accommodate for those cases, allowing you to plot each small multiples chart against separate y-axes and then change the automatic scaling of each axis. You’ll find the new options, “Shared y-axis” and “Scale to fit”, in the Y-axis card of the formatting pane.
Turning off the shared y-axis option will give each of your small multiples charts their own y-axis:
And turning on the scale to fit option will change their automatic scaling:
This new functionality will affect the auto minimum and maximum values. If the user has defined a min or max for the small multiples, the toggle won’t override that boundary. Any secondary y-axes will follow the settings from the main y-axis.
Slicer type formatting moved to Format Pane
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
DAX formula bar support for Desktop model view
The DAX formula bar is now supported in Desktop model view. You can now create and edit measures, calculated columns, and calculated tables in the model view on Desktop.
New DAX function: EVALUATEANDLOG
Have you ever wanted to see the intermediate results of a DAX expression? Have you ever wanted to debug your DAX by printing out values? If so, you’ll be excited about a new function called EVALUATEANDLOG! It takes any DAX expression, evaluates it and returns the result. But that’s not all: it also logs the result, hence the name EVALUATEANDLOG, to the DAX evaluation log that you can read out using tools like SQL Profiler. It’s very much like a “print debugging statement”. For example, consider the following measure that calculates the sales growth year over year, using a [Sales Amount] measure and the SAMEPERIODLASTYEAR function:
YoYGrowth := [Sales Amount] – CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR(’Date’[Date]))
To make sure this measure does what you need it to do you can now wrap the parts of the measure in EVALUATEANDLOG to see the intermediate result, like the example below. All the while, Profit will still return the same result as before:
YoYGrowth := EVALUATEANDLOG([Sales Amount]) – EVALUATEANDLOG(CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR(‘Date’[Date])))
However, additionally, the DAX evaluation log will now contain the result of [Sales Amount]) and CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR(’Date’[Date]))!
You can see the output using SQL Profiler by connecting to Desktop, starting a trace and subscribing to the DAX Evaluation Log event. Alternatively, you can use the free DAXDebugOutput tool. Please note that the DAXDebugOutput tool is not an official Microsoft tool and hence is not signed nor supported.
We are only scratching the surface of what is possible using EVALUATEANDLOG in this blog.
New DAX functions: TOCSV and TOJSON
These two very similar DAX functions are closely related to EVALUATEANDLOG but can also be separately. These functions convert the input Table to CSV or JSON, respectively. For example:
MyCSV = TOCSV(DimProduct)
Returns something like this (screenshot truncated):
You can change the delimiter (applicable to TOCSV only) and whether you want the headers to be included. On top of that you can specify the max number of rows to be returned.
Read more about TOCSV and TOJSON in our documentation.
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:
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 the 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.
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.
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.
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!
Thanks for your feedback.
ReplyDelete