New DAX comparison calculations: OFFSET & INDEX
Power BI Update - December 2022
New great DAX functions have been introduced with the new Power BI December 2022 update, designed to facilitate comparison calculations in Power BI which are: INDEX, OFFSET and WINDOW
INDEX allow to retrieve a result using absolute positioning, useful for comparing values vs a baseline or finding another specific entry
OFFSET allow to retrieve a result using absolute positioning, useful for comparing values against a previous value
WINDOW allow to retrieve a slice of results using absolute or relative positioning useful for running total calculation such as Moving Average that rely on selecting a range of values
Within these functions we can use the two new help functions "ORDERBY" and "PARTITIONBY'“
In this post I tested the two functions: OFFSET & INDEX.
OFFSET Example: comparison with previous row
✨In my example, I compare the Count products measure with the previous product category row, so I put -1 as the first parameter, and in the second parameter I specified the comparaison table which in my case is All (Product[Product Category]), then I specified the ascending order within the ORDERBY function, to define what the first row will be.
👉I have previously described this function which was introduced in advance:
INDEX Example:
In this example I have displayed the maximum product price for each category.
My goal was to display the category with the second most expensive price, for that I used the new INDEX function:
I specified in the INDEX function the following parameters:
The position: 2 to have the second category,
The table: I took both columns “category “ and “the maximum price by category”
The sort order: I sorted by “the max price by category” column using orderBy
The INDEX returns a row with specified elements, I used Selectcolumns DAX function to display the Category from the retrieved row.
Much simpler than RANKX and more flexiblr than PREVIOUSPERIOD Very nice