Super ❤ I had the decision not to even try window functions until I see your video about them. I cannot wait to see more specially the performances related video. I will start using them today itself. Thank you Alberto for everything.
Muchas Gracias Alberto, es maravilloso todo lo que va mejorando power Bi y sus funciones , con tantas actualizaciones simplemente tocara solo dedicarse a ello , excelente explicacion como siempre!!!
Thank you very much, @SQLBI! Watching Alberto , materializing his ideas is always a great pleasure to me. I have a little question / consideration about the syntax used in OFFSET() demo -- [20:55] -- using of ALL() function to get Dates. I mean that ALL() returns additional blank row, so it will add a blank row in the head of the 'DatesAndSales' table, that will yeild one false positive to our results set.
@@SQLBI I am using your dynamic approach from your dax patterns course as I segment on brand instead of category. Doing the snapshot approach with over 500 brands isn't suitable. I was hoping window functions may have some gains, that's a shame.
great video as always, thanks. The INDEX function would have been incredibly useful when i once had to make a cash flow analysis based on a table from an accounting ERP system that would create 13 records with cash balances each year - one on the last day of each month what the cash balance is but one additional record on the first day of the year each year
In the rolling average graph during window function, I can see the rolling average value is extending to 5 months in future. How can I prevent that from hapenning?
It is possible to use WINDOW function and sort by calculated column [@Sales]. The from_type/to_type parameters in that case must be specified as 'ABS'.
I was going to ask if this would make the old GuyInAQube question/collab video easier, and I'm happy to see that the offset example is exactly that case scenario
Great video, thank you. Your content has had a profound impact on my DAX journey. I was attempting to pass a filtered table (topn) into the relation parameter of INDEX function but I kept getting an error. "Relation parameter may contain duplicate rows. This is not allowed." The table didn't have duplicates however. I know I am missing something.
Thanks for the amazing fresh vedios for window functions!!! Can't wait for it to get generally available. Also wondering when Bravo's Time Intelligence Measures are going to update with the new window functions 🤭
They don't provide performance improvements over other techniques. The code could be shorter in a few cases, but for Bravo it doesn't matter as the code is generated by the template. And it's too early to use these new functions in production.
@@SQLBI that’s reassuring! Thanks for the explanation ;) Bravo is amazing by the way. I’m using it on my current work project, and they are working PERFECTLY! I’m very surprised how fast and easy it is. My teammates and manager are also constantly getting surprised by the model I build with Bravo 😝
Is my understanding correct that a limitation of window functions, particularly from a visualisation standpoint, is the necessity of including a specific column (e.g., ReportingDate) in the visual in order to obtain a value for a previous date? I aim to avoid including that column. Are there any potential workarounds for this? To illustrate, let's consider a straightforward report featuring a table visual with columns sourced from a fact table: ProjectID, ActualDate, and PreviousActualDate (a measure). The report includes a dropdown filter (ReportingDate) derived from a dimension reporting date table, establishing a one-to-many relationship with the fact table, filtered to a single date. It seems challenging to utilise a window function to retrieve the PreviousActualDate in the table visual without having to include the reporting date column. I have attempted to do so, but it consistently displays as blank unless the reporting date is included in the visual. Are there any strategies or alternatives to address this issue? I've reverted to using a window function in a calculated column in the fact table for now.
Awesome. No half-baked, rushed demos just to get something out because it got released 10 seconds ago 👏🙂 Can I ask why you alternate between using ADDCOLUMNS+SUMMARIZE in some demos and SUMMARIZECOLUMNS in others? I read on a forum that SUMMARIZECOLUMNS should not be used in measures since it does not enable context transition but you seem to use it in your demos within DAX studio. Would it not cause less confusion for students if you stuck with ADDCOLUMNS + SUMMARIZE? Thank you.
We use SUMMARIZECOLUMNS to show the result of a query (Power BI uses SUMMARIZECOLUMNS for that) and we use ADDCOLUMNS + SUMMARIZE in code that can be embedded in a measure. Thanks for the comment, we'll try to make this clearer in the future!
Excellent video as alwasy, very informative and insightful. I was wondering though, in the last example for WINDOW, why do you add the second ORDERBY column? Shouldn't YearMonthNumber be enough to sort the table, what additional need is there to add YearMonth to the sorting? Thanks!
I always do axchange curancy. 1.8 milion sometimes 2 excel books rows in my excel . this technic is very usefull. Great video, I tried it in my own table, but i get blanck rows. Checked all formats and so on....
Yes, but only once they settle down - they are still in preview. We are working on a new version of Optimizing DAX, we'll still be busy for the next few months. A new edition of Mastering DAX will be published much later, not in 2023.
In pure SQL window(analytics) function can be used on table and on results (agregate). How can be achived in DAX? Make windowed calculation on result of group of calculations.
Each one of your videos is a pure goldmine of information. I wanted to check if this can be implemented in the following scenarios: If you want to calculate the maximum value of a measure for the relative 12 months, can the WINDOW function be utilized? I tried it but I just get the MAX of each specific year-month combination. but I need the single MAX of those 12 months to appear in the full selection. So something like: P3-2021 = 15 P4-2021 = 8 P5-2021 = 10 P6-2021 = 12 ... P2 -2022 =7 If within those 12 relative months, the highest value is 15 then for all those periods to return that dynamic MAX value, so the end result should be: P3-2021 = 15 P4-2021 = 15 P5-2021 = 15 P6-2021 = 15 ... P2 -2022 = 15 Can this be achieved with WINDOW or would you recommend a different function? I tried with calculatetable and previous date calendar but for some reason I get only the last period value as the MAX :( in this example that would be: P3-2021 = 7 P4-2021 = 7 P5-2021 = 7 P6-2021 = 7 ... P2 -2022 = 7
Whilst it's not always a strict improvement on the existing methods, would you say, if one is the in the process of "Mastering DAX" to opt towards these rather than the incumbent functions? In a vacuum, for the usual day-to-day use cases.
Don't be in a hurry to use a new function unless you have a specific use case where it's a clear advantage! Nobody has enough experience today to say whether in the long term they are better or not than similar solutions.
Why does one need 40 min videos for something that should be so simple, yet powerful such as Window Functions? This is all you need to know about the issues with DAX. Way more powerful: produce a series of videos that describes WHY one would need to invest time in DAX, as apposed just writing code in SQL.
Because in DAX you write a measure that works in a semantic model (potentially in multiple reports), whereas in SQL you write a query for a specific report. They have different purposes.
Thank you Alberto. Great work as usual from SQLBI.
The best syntax for window functions is best described by Jeffrey Wang.
Function ( , , , , , )
Super ❤
I had the decision not to even try window functions until I see your video about them. I cannot wait to see more specially the performances related video. I will start using them today itself. Thank you Alberto for everything.
Phenomenal intro and demonstration, sir!
Amazing, Alberto! 🤗😍
Wonderful as always. I have always learned a lot from you. I use Dax's book as my holy bible.
Hugs from Brazil, Victor.
Muchas Gracias Alberto, es maravilloso todo lo que va mejorando power Bi y sus funciones , con tantas actualizaciones simplemente tocara solo dedicarse a ello , excelente explicacion como siempre!!!
Thank you very much, @SQLBI!
Watching Alberto , materializing his ideas is always a great pleasure to me.
I have a little question / consideration about the syntax used in OFFSET() demo -- [20:55] -- using of ALL() function to get Dates.
I mean that ALL() returns additional blank row, so it will add a blank row in the head of the 'DatesAndSales' table, that will yeild one false positive to our results set.
Thank you very much for the clear explanation
All right. Need to use these new functions.
Sqlbi, please do your customer retention piece using win functions! Keen to see if there are performance improvements. Love your work 👏
Apparently, it doesn't provide a performance benefits if you use the more optimized code we used on daxpatterns.com
@@SQLBI I am using your dynamic approach from your dax patterns course as I segment on brand instead of category. Doing the snapshot approach with over 500 brands isn't suitable. I was hoping window functions may have some gains, that's a shame.
Please update us if the apply semantics white papers are completed for us to read. Thanks for this fantastic video.
Very useful video. Thank you very much
😍
great video as always, thanks. The INDEX function would have been incredibly useful when i once had to make a cash flow analysis based on a table from an accounting ERP system that would create 13 records with cash balances each year - one on the last day of each month what the cash balance is but one additional record on the first day of the year each year
In the rolling average graph during window function, I can see the rolling average value is extending to 5 months in future. How can I prevent that from hapenning?
It is possible to use WINDOW function and sort by calculated column [@Sales]. The from_type/to_type parameters in that case must be specified as 'ABS'.
I was going to ask if this would make the old GuyInAQube question/collab video easier, and I'm happy to see that the offset example is exactly that case scenario
Great video, thank you. Your content has had a profound impact on my DAX journey.
I was attempting to pass a filtered table (topn) into the relation parameter of INDEX function but I kept getting an error. "Relation parameter may contain duplicate rows. This is not allowed."
The table didn't have duplicates however.
I know I am missing something.
Thanks for the amazing fresh vedios for window functions!!! Can't wait for it to get generally available.
Also wondering when Bravo's Time Intelligence Measures are going to update with the new window functions 🤭
They don't provide performance improvements over other techniques. The code could be shorter in a few cases, but for Bravo it doesn't matter as the code is generated by the template. And it's too early to use these new functions in production.
@@SQLBI that’s reassuring! Thanks for the explanation ;)
Bravo is amazing by the way. I’m using it on my current work project, and they are working PERFECTLY! I’m very surprised how fast and easy it is. My teammates and manager are also constantly getting surprised by the model I build with Bravo 😝
Great video. Thank you :)
Is my understanding correct that a limitation of window functions, particularly from a visualisation standpoint, is the necessity of including a specific column (e.g., ReportingDate) in the visual in order to obtain a value for a previous date? I aim to avoid including that column. Are there any potential workarounds for this?
To illustrate, let's consider a straightforward report featuring a table visual with columns sourced from a fact table: ProjectID, ActualDate, and PreviousActualDate (a measure).
The report includes a dropdown filter (ReportingDate) derived from a dimension reporting date table, establishing a one-to-many relationship with the fact table, filtered to a single date.
It seems challenging to utilise a window function to retrieve the PreviousActualDate in the table visual without having to include the reporting date column. I have attempted to do so, but it consistently displays as blank unless the reporting date is included in the visual. Are there any strategies or alternatives to address this issue? I've reverted to using a window function in a calculated column in the fact table for now.
Awesome. No half-baked, rushed demos just to get something out because it got released 10 seconds ago 👏🙂
Can I ask why you alternate between using ADDCOLUMNS+SUMMARIZE in some demos and SUMMARIZECOLUMNS in others?
I read on a forum that SUMMARIZECOLUMNS should not be used in measures since it does not enable context transition but you seem to use it in your demos within DAX studio.
Would it not cause less confusion for students if you stuck with ADDCOLUMNS + SUMMARIZE? Thank you.
We use SUMMARIZECOLUMNS to show the result of a query (Power BI uses SUMMARIZECOLUMNS for that) and we use ADDCOLUMNS + SUMMARIZE in code that can be embedded in a measure.
Thanks for the comment, we'll try to make this clearer in the future!
hello,
can windowing function on another type from date ?like i want to winfow sales by products
Excellent video as alwasy, very informative and insightful. I was wondering though, in the last example for WINDOW, why do you add the second ORDERBY column? Shouldn't YearMonthNumber be enough to sort the table, what additional need is there to add YearMonth to the sorting? Thanks!
I always do axchange curancy. 1.8 milion sometimes 2 excel books rows in my excel . this technic is very usefull.
Great video, I tried it in my own table, but i get blanck rows. Checked all formats and so on....
Hi, can you tell me what kind of camera and lens you are using for your recording? Looks really good. thanks
It's all here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/
@@SQLBI thank you
Hello! Will you Update Mastering Dax Course with this all new features??
Yes, but only once they settle down - they are still in preview. We are working on a new version of Optimizing DAX, we'll still be busy for the next few months. A new edition of Mastering DAX will be published much later, not in 2023.
In pure SQL window(analytics) function can be used on table and on results (agregate). How can be achived in DAX? Make windowed calculation on result of group of calculations.
How can we use the Relation arguments in all these functions?
It's explained in the article and in the video!
Each one of your videos is a pure goldmine of information. I wanted to check if this can be implemented in the following scenarios: If you want to calculate the maximum value of a measure for the relative 12 months, can the WINDOW function be utilized? I tried it but I just get the MAX of each specific year-month combination. but I need the single MAX of those 12 months to appear in the full selection. So something like:
P3-2021 = 15
P4-2021 = 8
P5-2021 = 10
P6-2021 = 12
... P2 -2022 =7
If within those 12 relative months, the highest value is 15 then for all those periods to return that dynamic MAX value, so the end result should be:
P3-2021 = 15
P4-2021 = 15
P5-2021 = 15
P6-2021 = 15
... P2 -2022 = 15
Can this be achieved with WINDOW or would you recommend a different function? I tried with calculatetable and previous date calendar but for some reason I get only the last period value as the MAX :( in this example that would be:
P3-2021 = 7
P4-2021 = 7
P5-2021 = 7
P6-2021 = 7
... P2 -2022 = 7
Try to formulate the question in the articles' comments and by using the model of the sample file - it's easier to get an answer that way!
Thank you ❤
🔥🔥🔥
Whilst it's not always a strict improvement on the existing methods, would you say, if one is the in the process of "Mastering DAX" to opt towards these rather than the incumbent functions? In a vacuum, for the usual day-to-day use cases.
Don't be in a hurry to use a new function unless you have a specific use case where it's a clear advantage! Nobody has enough experience today to say whether in the long term they are better or not than similar solutions.
1:59 INDEX
18:03 OFFSET
29:48 WINDOW
Hello Dear, Can you share the data you used for this example? pls
You can find the example in the related article (the download was missing previously but now it's there).
Do you have any courses on Udemy?
Why does one need 40 min videos for something that should be so simple, yet powerful such as Window Functions? This is all you need to know about the issues with DAX.
Way more powerful: produce a series of videos that describes WHY one would need to invest time in DAX, as apposed just writing code in SQL.
Because in DAX you write a measure that works in a semantic model (potentially in multiple reports), whereas in SQL you write a query for a specific report. They have different purposes.
For a moment I thought FUILKTER() was a version of FILTER() in German. 😀
Sir, I'm Unable to calculate moving/running average using
Average Cost moving =
AVERAGEX(
WINDOW(
1,ABS,
0,REL,
SUMMARIZE(ALLSELECTED('Custom Calendar Table'), 'Custom Calendar Table'[FY], 'Custom Calendar Table'[Month Name]),
ORDERBY('Custom Calendar Table'[FY], ASC, 'Custom Calendar Table'[Month Name], ASC)),
[Actual Cost]
)
Results is
Average Cost moving Actual Cost Month Name FY
220599.2747 220599.2747 Dec FY 22-23
219440.6214 219440.6214 Jan FY 22-23
216470.4219 216470.4219 Feb FY 22-23
BTW This Thumbnail so cute
🎉
You are looking more handsome with your beards
Do you have any courses on Udemy?