In a bit of a surprise twist the Power BI team have published a December update for 2022, containing a variety of new DAX functions designed to make it easier to do comparative calculations and updates to slicer type formatting!
The Power BI team have also confirmed that the first update for 2023 will be in February.
Read on to see what's been added in this update:
Slicer type formatting moved to Format Pane
Previously, to change a slicer’s type - for example changing from a relative date type slicer to a slider, settings were only available in the visual header. Additionally, to change a slicer to “horizontal” users had to first choose “list” from the visual header to see the option in the Format pane before adjusting the orientation.
Now, these settings live in one place - the Format pane, making it easier to discover and change between slicer types.
Used with permission from Microsoft.
Used with permission from Microsoft.
This change is also available in the mobile formatting options as well! Users can quickly update their mobile layout slicers to be more mobile-friendly.
Making it easier to do comparison calculations
In this release, the Power BI team have introduced multiple new DAX functions designed to make it easier to do comparison calculations in Power BI. The new functions available in this release 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).
INDEX performs comparison calculations by retrieving a row that is in an absolute position.
This is 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’:
Imagine trying 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:
The example above only showed customers whose last name is ‘Garcia’. However, the same calculated column works on a set that has more than one last name:
As shown in the screenshots above, the full name of the oldest person with that last name is returned. That’s because INDEX was told 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 2 was specified, the second oldest person would have been retrieved.
Specifying -1 or changing the sort order 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 the official Microsoft documentation. Notice that INDEX relies on two other new helper functions - ORDERBY and PARTITIONBY.
OFFSET performs comparison calculations more easily by retrieving a row that is in a relative position from the current row. This is useful for comparing across something else than time, such as across Regions, Cities or Products. For date comparisons, like comparing the Sales for this quarter vs the same quarter last year there are already dedicated Time Intelligence functions in DAX.
Below is a bar chart that shows total sales by product colour:
Imagine comparing how well each colour is doing against the colour above it in the chart. 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:
The newly added bars calculate the difference for each colour compared to the one just above it in the chart. That’s because -1 was set for the first parameter to OFFSET. If -2 was specified, the comparison would be against the colour above each colour, but skipping the one right above it, so effectively the sales for the grey colour would have been compared against the sales for products that were black.
Read more about OFFSET in the official Microsoft documentation.
WINDOW can perform calculations that rely on ranges of results (“windows”), such as a moving average or a running sum.
Below is an example of how to use WINDOW. The below column chart shows total sales by year and month:
Want to add a moving average for the last three months of sales including the current month? 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:
The newly added line correctly calculates the average sales over three months (including the current month). The first parameter of WINDOW is set to -2, which means that the start of the range is set two months before 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 the official Microsoft 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.