Power BI Update
By Luke Sisley, Senior Business Intelligence Analyst, CSL
As expected, this month’s feature summary was relatively light on new reporting and modelling features compared to previous editions. However, the MS Power BI team have given us a handful of exciting gifts to end the year, including new functions to make it easier to do comparison calculations.
Modelling
New functions to support comparison calculations
INDEX allows you to retrieve a row using its absolute position, allowing you to compare values against a baseline or specific entry. Microsoft provides the below example, using the function to return the name of the eldest person by each surname:
Further information on this function can be found here.
Unlike INDEX, OFFSET allows you to retrieve a row using its relative position, allowing a dynamic comparison. Here Microsoft provides the example of comparing a sales measure across a colour attribute – comparing the sales of each colour vs the previous colour:
Full documentation for OFFSET can be found here.
Lastly, WINDOW retrieves an aggregate of data using absolute or relative position – useful for producing a running total, moving average, or another dynamic aggregation based on a changing selection of rows. Microsoft provides another example here, producing a 3-month moving average of sales:
Click here to read the full documentation on the WINDOW function.
These 3 new functions are supported by 2 additional functions – ORDERBY and PARTITION BY.
ORDERBY modifies the INDEX and OFFSET functions by allowing the designation of an ordering column, and the selection of an order for retrieval. Further documentation can be found here.
PARTITIONBY is used to define the columns that will be used to partition the rows referenced in the function. Further info is available here.
All images and DAX in this article are sourced from Microsoft via the December feature summary
To Wrap It Up
That wraps up our summary on three of the interesting new features delivered with this update. The full feature summary for December can be found here.
For support in getting the most out of Power BI, please call us on 01483 528302 or email info@csl-uk.com, or read more about how our Power BI experts can support you here.
