No video :(

NEW OFFSET Function in DAX | Will It Change How You Write DAX?

Подписаться 227 тыс.

Просмотров 57 тыс.

50% 1

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.i...
--------------------------------
📊 TRAININGS 📊
---------------------------------
Power BI Design 4 Weeks Transformation Program my.datatrainin...
Power BI Essentials datatraining.i...
Business User Training datatraining.i...
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.ne...
Newsletter datatraining.i...
---------------------------------
👇 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.epidemicso...
🚀 For growing on RU-vid: www.tubebuddy....
🏄 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

Опубликовано:

17 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 108

@storybi4636 Год назад

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.

@ozansen_ozibaba Год назад

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!

@HowtoPowerBI Год назад

Glad to hear this Ozan, thx!

@andysuzhou7956 Год назад

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!

@vishaljhaveri7565 Год назад

One of the best explanation about OFFSET. Thank You.

@tibobago Год назад

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 🙂

@omariscoming5018 Год назад

this is huge, so many workaround needed before to this simple thing

@luisguardia5268 Месяц назад

You are saving my project legend, thanks a mill

@aqavi263 Год назад

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.

@HowtoPowerBI Год назад

Much appreciated! thanks for watching and the nice words 😊

@narendrababu2287 Год назад

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.

@HowtoPowerBI Год назад

lol 😂 my parents will be happy to hear they picked the right name for me 😁 thanks Narendra

@narendrababu2287 Год назад

@@HowtoPowerBI haha ❤

@dutchdatadude Год назад

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

@dutchdatadude Год назад

Mote == more

@HowtoPowerBI Год назад

The function turned my world upside down 😉 lol

@PeterKontogeorgis Год назад

@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 :)

@dutchdatadude Год назад

@@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.

@PeterKontogeorgis Год назад

@@dutchdatadude thanks for the reply ...was more referring to suggestions from previous videos. And half in jest :)

@MJ-gu6uu Год назад

Thank you very much. I really appreciate the way you teaching, and the level of dedication you make your videos with

@HowtoPowerBI Год назад

that's great to hear, happy it helps 😊

@casingena1985 Год назад

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 :)

@roverre61 Год назад

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 👏👍

@MatadorSoftware Год назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QYgi9Epzwsc.html

@nancylalicon4192 10 месяцев назад

Thanks so much for the clear explanation on how to use OFFSET.... I think you just changed my life!

@tjitzeweisma7033 Год назад

Thanks for sharing. It will work nicely for company specific period codes, as we now easily can calculate the deviation to the previous period.

@texmexdragon2160 Год назад

I was thinking the same thing. For accounting periods perhaps it would work well.

@sarveshdeshpande1772 Год назад

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

@HowtoPowerBI Год назад

thanks for watching Sarvesh! 😊yea, it's nice that we can play around with the function already before its official release

@vijayarjunwadkar Год назад

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!

@Drengen10 Год назад

Wonder if it can be used to calculate rolling totals that are "complex"

@fumaremiocazzo Год назад

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?

@hlee6239 Год назад

Lovely lecture! I found your channel is the most helpful! TYTY

@HowtoPowerBI Год назад

thank you so much Hosung 😊

@1yyymmmddd Год назад

Good function, but don't we lose possibility for customer to sort any column him/herself?

@dizzyharris2658 Год назад

For time intelligence, couldn't you sort by YearMonth Number? Then adjust the var to IF( [YYYYMM] > MIN( [YYYYMM] ), [Total Sales] - SalesOffset )

@HowtoPowerBI Год назад

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

@sherifffruitfly Год назад

Can the first param of OFFSET() be a scalar valued expression? Or does it have to be an explicit fixed constant?

@hadi11911 Год назад

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.

@MrSparkefrostie Год назад

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.

@Michella1393 10 месяцев назад

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?

@Unbox747 Год назад

Thank you for this great video ! Is there a way to use offset to return a text value instead of a numeric ??

@PeterKontogeorgis Год назад

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.

@muralimohan71 Год назад

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 😊😊

@Saif_Ali_Khan_1301 Год назад

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?

@dutchdatadude Год назад

Thanks for sharing please stay tuned for the official release and much more. We are just getting started!

@HowtoPowerBI Год назад

Looking forward to see what's coming! 😀

@umangdbz Год назад

Hey bro, for the multiple fields part, can we simply include the desired field in relation and orderby arguments instead of partition?

@narendrababu2287 Год назад

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.

@phanhuyhoang3720 Год назад

Yeah but it will slow power bi too😅

@ernests4066 Год назад

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!

@HowtoPowerBI Год назад

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)

@Tubasnot Год назад

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!

@braedonbrown2973 Год назад

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?

@HowtoPowerBI Год назад

would work I think as long as you don't have a hierarchy (year , month) on rows, but just the concatenated yyyymm

@juanpablovivar1088 Год назад

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

@YvdB Год назад

To do it over a longer period you could combine Year & Month in one column. "YYYY-MM"

@sroumi 14 дней назад

The Orderby now works on Total Sales : OFFSET Manufacturer = CALCULATE( [Total Sales], OFFSET( -1, ALLSELECTED(dimProduct[Manufacturer]), ORDERBY([Total Sales], ASC), ) ) So the last part of the video is obsolete.

@Negi_From_Pauri Год назад

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.

@habuiah Год назад

Finally! Use this kind of analysis in M for big data is terrible.

@BokangMaela Год назад

Will this offset function work if for example do something like CurrentColumn = var previousevalue = Calculate([CurrentColumn], Offset(-1)).

@bharathreddy4267 Год назад

Can any tell me, what is the use of this offset function in real time. Thanks

@unnikrishnansanthosh Год назад

another masterpiece from BAS, thank you for sharing

@HowtoPowerBI Год назад

thanks Santhosh! 😊

@rachelrdyer Год назад

great job, thanks very much! I appreciate the effort with the work arounds and also the candid assessment of it's usefulness.

@tapanakul 5 месяцев назад

Very clear explain, Thank you so much for your shown.👍👍

@nickcraft5552 Год назад

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

@raghavanp6730 Год назад

It's a super cool feature. Great explanation Bas!

@HowtoPowerBI Год назад

thx Raghavan

@XeniaD 5 месяцев назад

Can this happened with week as well?

@andrewcoventry7246 Год назад

should make calculating moving ranges a lot easier, so for XMR charts will help a lot!

@barttitulaerexcelbart9400 Год назад

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.

@HowtoPowerBI Год назад

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).

@karrekkagarianishreddy699 3 месяца назад

You made my day, thank you boss

@HowtoPowerBI 3 месяца назад

💻💪🎉

@normandtousignant1754 Год назад

Great presentation

@powerbinareal Год назад

TOP! #powerbinareal

@allyoucaneats Год назад

This does not work for multiple group bys

@FEBear1 Год назад

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.

@dutchdatadude Год назад

Yes you can

@rcb3921 Год назад

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

@normandass6053 Год назад

Absolutely amazing Mate 😄

@HowtoPowerBI Год назад

thank you Norman!! 😊🙂

@letbloodline Год назад

Can’t you solve easily by doing -1 on a column that is year *100 + month and not show that column?

@dutchdatadude Год назад

For a time based calculation, yes but for the second example (not time intelligence) you couldn't that.

@letbloodline Год назад

@@dutchdatadude yes, I actually posted the comment in the middle of the video before resuming play ^^’

@XopherA Год назад

Here is a possible practical use - offsetting weeks, since there's no native time intelligence for that.

@HowtoPowerBI Год назад

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

@inj1979 Год назад

Thanks. 🍓

@gagansingh3481 Год назад

I can't understand how it works for manufacturer column and not for month as both are text

@HowtoPowerBI Год назад

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)

@ivandariosuarezrodriguez4627 4 месяца назад

Fantastic, thanks

@ymohamed3154 Год назад

Great Vedio 👌

@HowtoPowerBI Год назад

thank you! 😊

@musl1618 Год назад

🔥🔥🔥🔥

@mubeenxp8502 Год назад

Great

@Phoenixspin Год назад

I clicked because I thought this was about Cardi B's husband, Offset.

@HowtoPowerBI Год назад

🤣bit different

@paulmfti Год назад

Already saw this….

@HowtoPowerBI Год назад

Ok..👌

@shahebazslifestyle9664 Год назад

Nice

@HowtoPowerBI Год назад

Thanks 😊!

@randika118 Год назад

❤

@Bomyism Год назад

Could be an easier solution for running totals, running averages

@remek5758 Год назад

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

@subhajyotibhattacharyya3416 Год назад

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🧐🧐🥸

@allyoucaneats Год назад

This doesn't work for multiple groupings