NEW OFFSET Function in DAX | Will It Change How You Write DAX?
HTML-код
- Опубликовано: 25 июл 2024
- Introducing Power BI's BRAND NEW function: OFFSET in DAX. While it officially not released just yet (september 2022), you can already use it!
OFFSET enables you to do visual level calculations by pointing to other cells / datapoints in your visual (kinda like in Excel) - something that was not possible before.
In this video I explore how to use the OFFSET function and whether it will change how you write your DAX measures.
Enjoy this video and subscribe to always stay updated on my favorite Power BI tricks :)
Download file here datatraining.io/powerbi-how-to
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatraining.io/pages/powe...
Power BI Essentials datatraining.io/powerbilearni...
Business User Training datatraining.io/powerbi-busin...
For Custom Trainings and Consulting email directly support@datatraining.io
---------------------------------
⏱️ TIMESTAMPS ⏱️
---------------------------------
00:00 Intro
00:43 OFFSET example in Excel
03:05 OFFSET example in Power BI
07:05 OFFSET with multiple fields
08:22 OFFSET for time intelligence
12:02 Implementing a meaningful sorting order
18:20 End
---------------------------------
😍 JOIN 😍
----------------------------------
Join bit.ly/4b453bi
Subscribe bit.ly/31MnQGO
Insta / howtopowerbi
LinkedIn / basdohmen
TikTok / how.to.power.bi
X / howtopowerbi
fb / howtopowerbi
Threads www.threads.net/@howtopowerbi
Newsletter datatraining.io/newsletter
---------------------------------
👇 CHECK THIS OUT! 👇
---------------------------------
💻 My gear amzn.to/47F21Yc
📚 Power BI books MUST READ! amzn.to/3tUfFcj
💡 General books I recommend amzn.to/48YNo33
🎶 Music for my videos www.epidemicsound.com/referra...
🚀 For growing on RUclips: www.tubebuddy.com/bas
🏄 Stuff I use daily amzn.to/3HqfMQ2
* Above are affiliate links, which means at no additional cost to you, if you make a purchase using these links we will receive a small commission. It supports us and helps us to continue making more How to Power BI videos!
Thanks for being a part of this channel and all your support! 💪 🙏
#HowToPowerBI #PowerBI #DataTraining
#powerbidesktop #powerbitraining #powerbideveloper #DAX 
Наука
One of the best explanation about OFFSET. Thank You.
I love how you explain things by showing the Excel equivalent, then showing how the function works in Power BI. I tried the OFFSET function and it worked great! Seems like an alternative to the prior pattern, FILTER(ALL(Time.
This is by far the best explanation about offset! It reminds us of window functions in SQL (LAG and LEAD) as somebody wrote it! I also liked your workarounds!
Glad to hear this Ozan, thx!
It reminds me of the infamous EARLIER function, when we try to get the previous row in a dataset; an impossible task in direct query mode. Now it's possible, even it's not the most performant solution. Power Bi is getting better!
this is huge, so many workaround needed before to this simple thing
Good work Bas, appreciate your in-depth efforts. I like how you played around with workarounds and explored all tricks to make this work in preview.
Demonstrating what does not work, to understand its best use case, is also very important.
Much appreciated! thanks for watching and the nice words 😊
Thanks Bas. Love the examples. I would just call out that while adding the total sales by manufacturer work in your example, it's not dynamic anymore and if a filter was to change the rank of the manufaturers in that filter context, it won't take that context in consideration in the result as it's pulling from the column value. So by example if a manufature ranks 2nd overall, as filters get applied, it could change to 3rd rank but would still be considered as 2nd in your measure... would love to find another workaround that would keep it fully dynamic! I think there's great potential to have fun with that new function, maybe squeezing some field parameters, calculation groups and a slicer to select the offset value on top 🙂
Interesting function to know! Thank you Bas for covering in detail, but will require a bit more time and efforts to understand and use it better. Of course, as it is yet to be released, we might see the functionality that you wished for and demonstrated with work arounds actually appear, fingers crossed!
You are saving my project legend, thanks a mill
Thanks Bas, it's always good to also show what doesn't work and why, so from me thanks for including these bits rather than leaving them on the cutting room floor :)
Thank you very much. I really appreciate the way you teaching, and the level of dedication you make your videos with
that's great to hear, happy it helps 😊
Nice analysis Bas. Was quite intrigued when I read about this a few days ago. Potentially could be great if they can get make it easy enough to use.
Thank you for this great video !
Is there a way to use offset to return a text value instead of a numeric ??
Wow …. Understanding dax before release. Great Vedio Bas and use case was also great. Kudos to your hard work 😊 I eagerly wait for your vedios
thanks for watching Sarvesh! 😊yea, it's nice that we can play around with the function already before its official release
Dear Buz, I am very fascinated by the way you manage to explain in such a simple and clear way, thousands of pages of Microsoft instructions .... !!! 😄 congratulations...!!
It would also be very interesting if you could dedicate some video tutorials about the "Power BI report builder" application to make structured reports that can be printed...Can you do It for us?
Many thanks and Bravissimooooooo 👏👍
As your name reminds me of a bus that makes to touch each stop while travelling. Your videos also touches every problem in that solution. That's a great thing in your videos.
lol 😂 my parents will be happy to hear they picked the right name for me 😁 thanks Narendra
@@HowtoPowerBI haha ❤
Thanks for sharing. It will work nicely for company specific period codes, as we now easily can calculate the deviation to the previous period.
I was thinking the same thing. For accounting periods perhaps it would work well.
Thanks so much for the clear explanation on how to use OFFSET.... I think you just changed my life!
great job, thanks very much! I appreciate the effort with the work arounds and also the candid assessment of it's usefulness.
Lovely lecture! I found your channel is the most helpful! TYTY
thank you so much Hosung 😊
Great presentation
another masterpiece from BAS, thank you for sharing
thanks Santhosh! 😊
It's a super cool feature. Great explanation Bas!
thx Raghavan
Hey bro, for the multiple fields part, can we simply include the desired field in relation and orderby arguments instead of partition?
Very clear explain, Thank you so much for your shown.👍👍
You made my day, thank you boss
💻💪🎉
Thank you for sharing and providing examples and solid explansions on the usage of the new OFFSET. Totally agree that it might not be super useful currently, but shows that there is potential once it gets refined a bit. Cheers!
Absolutely amazing Mate 😄
thank you Norman!! 😊🙂
I think this is going to help witg the super conplex dax i put together a while back. I need to calculate the time difference between 2 time stamps. The durations are milliseconds but the difference could be seconds to days.
Thanks for sharing please stay tuned for the official release and much more. We are just getting started!
Looking forward to see what's coming! 😀
Thank you for this video, this function is so helpful (to be honest in Tableau different to the next value is super easy, but in PBI it was more challenging), now delta to the next is also easy in PBI, 5!
thanks for watching Ernest! 😊 my hope was that it would be similar to the lookup function in tableau. At the moment it is not quite there yet though as you miss the flexibility of the table calculations that tableau has (adjusting how you compute + ability to use measures for the sort)
Great video! Time intelligence calculations are still frustration. I recently found a solution to such a problem like the one in your example using TREATAS and braces{} to define a table within a table. cc.Previous_Year_Patients =
VAR __previous_Year = DATE( [YEAR], 1, 1 )-1
VAR __previous_Patients =
CALCULATE(
SUM ( TB_POP[Patients]),
TREATAS( {__previous_YEAR} , TB_POP[YEAR] ),
ALLEXCEPT( TB_POP, TB_POP[Zip Code] )
)
RETURN
__previous_Patients
Wonder if it can be used to calculate rolling totals that are "complex"
Oh and one mote: - 1 actually moves up one. Not down as you said in your first Power BI demo. Negative is above, positive is below
Mote == more
The function turned my world upside down 😉 lol
@Dutch Data Dude Now you need to go back through Bas's previous video's and create dev tasks for all the suggestions he's made :)
@@PeterKontogeorgis well, some of them are not where we want to take this, but it will make sense soon enough. What you are seeing is a first building block for a huge development which has not been revealed.
@@dutchdatadude thanks for the reply ...was more referring to suggestions from previous videos. And half in jest :)
Hi, thanks for the amazing tutorial! I am trying to use the offset function to calculate the previous calendar week in a calculation group. It does not work, do you have an idea on how it works?
Thanks. 🍓
Great Vedio 👌
thank you! 😊
Can the first param of OFFSET() be a scalar valued expression? Or does it have to be an explicit fixed constant?
Is there any other option for gaining the same result, especially for the month/total sales example, beside the offset - orderby functions, since my version of power BI can't recognize it?
Fantastic, thanks
Hey pal i am big fan of yours, "POWER BI"☺ i have a question about power query we can undo(means delete) the step, So is there any way we can redu(means to get last deleted step) the step. Deleted step could be in any line in applied steps. I am asking because sometimes the step is big we write m query so that we just can redu the step instead of to write m query again.
Good function, but don't we lose possibility for customer to sort any column him/herself?
should make calculating moving ranges a lot easier, so for XMR charts will help a lot!
Would concatenating year and month in a “yyyymm” format and sorting by that value for month name theoretically work for the JAN values when using the offset and orderby functions together?
would work I think as long as you don't have a hierarchy (year , month) on rows, but just the concatenated yyyymm
So can you use this to evaluate a result in a row based on the result in the row above, of the same column. practical example case is when you are calculating projected stock, you need to refer the calculation to the projected stock of the previous month to evaluate the projected stock of the month. WOULD APPRECIATE YOUR ASSIST.
For time intelligence, couldn't you sort by YearMonth Number? Then adjust the var to IF( [YYYYMM] > MIN( [YYYYMM] ), [Total Sales] - SalesOffset )
logic is fine, but you would first have to calculate the min YYYYMM of the year correctly .. MIN[YYYYMM) returns the current month, [YYYYMM] will give an error as there is no row context .. you can adjust it so that this logic works, I think is just a bit more complex
TOP! #powerbinareal
Is it possible to perform similar operations in matrix visual. I want to compare the value from previous cell in same row with current row to calculate difference and highlight the current cell value as green if difference is positive and red if difference is negative.
I have a matrix visual where I need to highlight the cells in a Matrix visual when value is increased or decreased compared to previous column value in same row.
Please help me if it is possible. Thank you 😊😊
Will this offset function work if for example do something like CurrentColumn = var previousevalue = Calculate([CurrentColumn], Offset(-1)).
Can this happened with week as well?
I still used to think, what makes Microsoft so complicated for not allowing us to use direct column reference in measures and values section, which will makes dax super easier.
Yeah but it will slow power bi too😅
you are genius, how do you decide, when we should select All, SelectedAll, SelectedValue, Summarize, or Summarize COlumns
do you have case studies for these?
Finally! Use this kind of analysis in M for big data is terrible.
Well explained Bas, the OFFSET is not an easy function in Excel. For time intelligence: would not DATEADD be a lot more easier? I think you get the same result. As for the manufacturers example, I do not see (yet?) a practical business use.
thanks Bart! 🙂 definitely would stick to using dateadd for now and I'm also struggling to find good use cases ( that's also my conlusion in the video ;) ) . If you could use measures for the orderby function that would make it very useful though. Below in the comments a suggested use case is pareto analysis (didnt try it yet).
Can any tell me, what is the use of this offset function in real time. Thanks
Great
Excellent, this is something I need. Can it be used in a calculated column on a table, I wonder? That'll make my life a lot easier if so.
Yes you can
It can be used in a calculated column, but I don't think it can be used to reference previous values of that same calculated column. So it won't help produce a running / cumulative total
🔥🔥🔥🔥
Hello, thanks for your video, please i have a doubt, i try to create measure, all is good but the total is empty, this the code with offset, thanks very much
TEXTO_ANTERIOR =
VAR FILA =
SUM(ejemplo[TEXTO])
VAR fila_anterior =
CALCULATE(
sum(ejemplo[TEXTO]),
OFFSET(
-1,
ALL(ejemplo),
ORDERBY(ejemplo[FECHA_EVENTO_DATE_HORA], ASC)
)
)
VAR previous_corregida=
IF(fila_anterior=fila,0,FILA)
RETURN
previous_corregida
Nice
Thanks 😊!
To do it over a longer period you could combine Year & Month in one column. "YYYY-MM"
This does not work for multiple group bys
Here is a possible practical use - offsetting weeks, since there's no native time intelligence for that.
I was thinking about that one as well, but the thing is that you would still need an weekid field (that would continue in the second year with week 53, 54, 55 etc) and offset it. This you could also do without it and using the offset function would be worse when you have a hierarchy (year and week on axis) as the first value would be blank
❤
Could be an easier solution for running totals, running averages
Can’t you solve easily by doing -1 on a column that is year *100 + month and not show that column?
For a time based calculation, yes but for the second example (not time intelligence) you couldn't that.
@@dutchdatadude yes, I actually posted the comment in the middle of the video before resuming play ^^’
I can't understand how it works for manufacturer column and not for month as both are text
it works for both .. it is just that the month column has a "sort by" column and therefore I had to include the monthnr (the column that is use for the sortby)
The OFFSET function will likely be a solid candidate for calculating delta values in previous rows, like we would in SQL with LAG(). If you want to see a demonstration of the similarities, here’s my latest video on the functionality:
ruclips.net/video/QYgi9Epzwsc/видео.html
Already saw this….
Ok..👌
I clicked because I thought this was about Cardi B's husband, Offset.
🤣bit different
Thats great that they bring it in, but i Think Old Dev will anyways use old Time Int functions are just dateadd for custom ones, it's not to big deal I think, this may be goof for new power bi users who knows offset from excel
Hi I'm having a error after using offset saying "It is not a valid table, variable, or function name." . Can you please tell me why this is happening🧐🧐🥸
This doesn't work for multiple groupings