Over the past few days, I attended the Power BI Next Step conference in Lego land – Denmark. During the keynote, Will Thompson – PM on the Power BI team, showed a new DAX function that is available to all of us already, but was very well hidden in the latest builds of Power BI Desktop. This new function, called OFFSET, allows us to do in context comparisons between two values, without writing extremely lengthy and complex DAX.
I gave it a go and in this post I share my first experiences with this new function and how I think this will make our life easier!
How I got to know about OFFSET
As mentioned in the introduction of this blog, I attended the Power BI Next Step conference! A great experience where I learned a lot, engaged with likeminded people in the community and above all had an awesome time! (Big credits to the organizers!)
As part of this conference, Will Thompson – Program Manager at Microsoft, together with Ewan Tinitali – Designer in the Power BI team, came over from the US to run an amazing Keynote to kick-off the conference. During this keynote they shared many insights into where Power BI is headed towards in the future. As part of that, Will showed a new DAX function called OFFSET.
Besides all the other cool announcements, I will only talk about OFFSET now. In the replies of above tweet, Jeroen ter Heerdt (Jay) – Program Manager at the Power BI team also confirmed that this new DAX function is available in the most recent versions of Power BI Desktop and allows you to calculate “relative movement in a result set”.
Use case for OFFSET
So, this new thing, called OFFSET, what is it and is there anything similar already? The best comparison that I could find, is what we used to do with time intelligence. There are many DAX functions to calculate in the context of time, like DATESMTD or DATESYTD. If we build a visual in Power BI, we can easily calculate the differences by using these functions. However, in case you want to do something similar by calculating the difference with the previous row in a visual, this will be much harder and you must write some complex DAX. Well, not anymore!
Another easy comparison that can be made, is what we used to do in a Excel spreadsheet for example. In Excel it is pretty simple to just calculate the difference between two cells like the example below.
Doing something similar in Power BI, now just got a lot easier with OFFSET!
OFFSET, is not fully implemented in the recent builds of Power BI yet, however you can start using it already. IntelliSense does not work yet, so while you’re writing the DAX expression, it will show you an error and red-underlined items. But if you just continue building the DAX expression using this function, it just works!
OFFSET uses three parameters to work.
- Direction – number (-1 means go up, 1 means go down etc.)
- Column specification on which you want to calculate the difference – table specification
- Order direction, also partitioned by parameter (optional)
In the order direction there is another new DAX Function introduced, being ORDERBY. The ORDER BY function has two parameters, being:
- Column reference
- Order direction – ASC or DESC
In the example I setup, I calculate the difference in number of orders between the product in the current row context and the row above. To do this, I setup the following DAX expression where I used a variable to first calculate the value from the previous row and finally compare that with the current row.
$ Orders difference previous row = VAR PreviousRow = CALCULATE( [# Orders], OFFSET( -1, ALLSELECTED('Product'[Product]), ORDERBY('Product'[Product], ASC) ) ) RETURN [# Orders] - PreviousRow
In above example, I compare with the previous row, therefore I set the direction parameters to -1. In the column specification, I only wanted to use the products in the current selection. If other filters are applied, this set will change, therefore I used the ALLSELECTED ( ‘Product'[Product] ). Finally, in the order direction, I define the column used in the visualization and in which direction the products in this example should be ordered to define what the first row will be. In the ORDERBY function, I specified once more the Product table and column and order it ascending by using ASC.
As a result, I can now create the following visualization where I can do a row-over-row comparison.
I set the first parameter in the OFFSET function to -1, but you can obviously play around a bit by changing it to a positive number or even a higher number to compare to a few rows up/down.
In my example, I compared different products against each other to keep it simple, but of course there are much more valuable and relevant use cases. You can specify the column easily in the OFFSET function to calculate over a different visual. I think this is a huge step forward in the ease of using DAX, where it will be much easier to build complex calculations like these in a simple way! For your reference, I have uploaded a sample Power BI desktop file to my GitHub repository for Power BI Samples.
As a final and important note, since this DAX function is officially not out yet (September 2022), there won’t be official support if things break due to using this function. Also, the way how this function works might still be subject to change. So keep an eye out on the official Power BI blog for the announcement once it will be released and supported.
21 thoughts on “How OFFSET in DAX will make your life easier”
Reblogged this on ERP and BI.
I like the idea. How does the OrderBy function interact with any ordering that a user will do on a visual? Is this a gotcha waiting for the unwary?
Hmm… interesting case indeed. ORDERBY is optional, so I guess if you leave out this part, it will not be fixed in your DAX expression. Though, I did not test this case specifically yet.
Do you think this new function will be optimised to run much faster than other options of getting the same result with more complicated DAX?
It will 🙂
For what I understood and a little bird told me, this calculation will be performed on the output of previpus calculations (pre aggregated) instead of directly on the raw data. So this should perform much faster 🙃
That is fantastic. In the UK, Healthcare (NHS) are obsessed with Statistical Process Charts whereby certain rules are derived by checking x number of values before or after a point. This will really help with creating this using only native visualisations.
Great! If you want to go the extra mile, make the integer dynamic by using a What If parameter, so the report user can define themselves how many rows the want to go up or down to compare to 🙂
Pingback: DAX OFFSET – Curated SQL
Excelente articulo, desde que versión podríamos ya usar esta función
Hi! How can I add a “partition by” parameter?
The examples I’ve used so far have a order by, not partition by.
Thanks for the great explanation of this new function. Do you think returning 2 as the difference on the first row is misleading? I think it would be good to have the option of BLANK or 0. In your Excel example cell C2 is left empty.
Good one Jason. I think it should at least be an option. I’ll pass the feedback forward.
So how do you get it to work if the Matrix is sorted by orders in Descending order.
What exactly do you mean? When the sort order in the visual is different than the expression?
The orderby clause in the expression is optional. So leave out that one 🙂
Pingback: Analysis services 2022 new features deep dive part 1: Composite Model and DAX - Ben's Blog
Hi Marc, it also seems that the new offset function is part of SSAS 2022!
I referenced your article in my post with regard to the offset function.
Awesome! Thanks for sharing further 🙃
OFFSET function is not active , even if i finish the dax and run it as you suggested , it doesn’t work .
Please check which version of Power BI desktop you’re running.
In the top ribbon > help > about.
Must be December 2022 or later.
Thank you for this clear explanation