Topics: 1. (00:15) Introduction 2. (00:38) Open Power BI Desktop File and create Matrix 3. (02:37) Create First Two DAX Average Formulas 4. (06:20) Parallel Excel Worksheet example to understand the DAX averages and what happens if there is a Blank or empty cell. 5. (7:52) DAX Studio to time formulas. 6. (12:44) Look at four other DAX average formulas and timing results. 7. (14:36 ) Conclusion
Mike - thanks for this course. I've been going through it for two weeks (taking my time), and the information you convey in such a short time is amazing! I wish I had looked at this and Power Query years ago!
This was an interesting video; what I got from the video was that the DAX engine efficiency can be increased by improving the data modeling on the front end by using primary and foreign keys. Very cool stuff.
the good thing, is the power bi interface replacing the black with the white I think is much better:) Great video mike, the most important series, and complete about data analysis and with E-DAB you gate all the knowledge good job Mike good job!!👍👍👍 Note: up date your dax studio 🙂
Mike, thank you so much for your great videos. But this time, I somehow cannot obtain the files from your link. I get a message "That page can't be found." from the both links. I can connect to the pdf note though... It will be very nice if you can help me on this matter.
Thanks Mike i have a question: in the measure : Averagex(Values(date[Year-Month]),[Total Sales]), is it necessary to use values function even if the column date[year-month] has a unique list ?
Hi Mike Many thanks for this video..great as usual ;) I have a question please if you could help me .. Is there any dax formula for Half Year To date ? For one semester cumulative dynamic total If not how do you think we can get it ? Thank you so much
Wow!!! I had no idea. Then you asked and I figured out this fun way to accomlich your Half Year Goal: Here is the normal Measure for Yearly Running Total: Running Total YOY:=CALCULATE([Total Sales],FILTER(ALL(dDate[Date]),dDate[Date]6) Then we need this Measure: Running Total YO-half-Y:=CALCULATE([Total Sales],FILTER(ALL(dDate[Date]),dDate[Date]
Mike, that’s a very beautiful measure, right there... ...now, let’s take it up a notch: imagine you calculate Measure M1 which is the difference of two running totals (ins and outs) yielding some “net capacity” of ‘something’ (could be: current liquidity, number of people inside a building, etc.) at any given time. Imagine you calculate this with a certain granularity over some time period. Now you want to calculate the MAX of M1 over some (larger) period (day, week,...). How would you do that? I can’t seem to get it working even though I replaced the MAX-function by EARLIER inside a MAXX-context...
Forget about this question: I gave the correct answer (I made a mistake in the ALL-argument - it’s best to put in the entire table there and not just one particular column). :-)
@@excelisfunMerci... Thank you so much Mike....I appreciate a lot your complete and quick answer.. Just one precision to understand well your solution before testing it : may be I was not enough clear in my question : my Financial year is divided into 2 financial exercices .. from jan to jun and from jul to dec. I would like to add a dynamic cumulative measures called Term To Date within the semester depending on the month selected ( May 19 is selected : TTD = total from jan 19 to may 19 Sep 19 is selected : TTD = total from jul 19 to sep 19 ) Does your solution will work for this case ? Thanks a lot/ Merci beaucoup ;)
Mike, this video teaches a best practice to optimize DAX, which is the subject of an advanced workshop by Marco and Alberto. Their tool enables users to compare DAX in terms of efficiency. Is there such a tool to optimize Excel formulas? Thanks.
Hi! I have a table that contains different performance values for a group of 20 events. I need to calculate what is the difference between the average of each of those events and the global average. Can you advise on the best measures, please?
Wow! That is some good insight into DAX optimization, right there! I did not expect that - I have the greatest respect for the iterators, but as it turns out: other functions can be more efficient. Now, all of this concerns AVERAGEX, is there an equivalent optimization trick for MAXX? (I doubt it...)
BTW, Awesome Teammate Geert, the iterator functions are really awesome and have so many uses. I use a formulas like AVERAGEX(dDate,[Total Sales]) all the time because it is elegant and easy and the benefits of a different, more efficient method are not necessary. But it does pay to understand this all because when we need to tighten things up, it is nice to know this and apply it : ) But you knew that, Teammate : )
Mike, don’t worry: I haven’t lost my respect for the iterators :-). That will never happen. Furthermore, because of that other conversation we’re having, my respect for the aggregators has increased. Not so much because of their primary functions, but because of their capacity of context modifiers. Using the line: dData[date]
Hi sir I have a doubt. Here's how to find out how many birthdays are in total Count? 1. 02-Sep-1992 2. 03- Nov-1993 3. 04- oct-1994 4. 02-Sep-1992 5. 03- Nov-1993 6. 04- oct-1994
Topics:
1. (00:15) Introduction
2. (00:38) Open Power BI Desktop File and create Matrix
3. (02:37) Create First Two DAX Average Formulas
4. (06:20) Parallel Excel Worksheet example to understand the DAX averages and what happens if there is a Blank or empty cell.
5. (7:52) DAX Studio to time formulas.
6. (12:44) Look at four other DAX average formulas and timing results.
7. (14:36 ) Conclusion
Really great job Mike, well done. The team gets better every time you release a video!
You are welcome, Chris : ) : ) Go Team!!!!
Wake up.... Eat... Excel.... Sleep... Repeat..
Another good video in the series
Me too: Wake up.... Eat... Excel.... Sleep... Repeat.. Glad you like the video RRR!!!!
Mike - thanks for this course. I've been going through it for two weeks (taking my time), and the information you convey in such a short time is amazing! I wish I had looked at this and Power Query years ago!
Yes, Power Query changes EVERYTHING : )
Thanks for your support, Jerry : )
This was an interesting video; what I got from the video was that the DAX engine efficiency can be increased by improving the data modeling on the front end by using primary and foreign keys. Very cool stuff.
Glad it is cool for you, TP : )
Dax optimization! Thanks Mike :)
You are welcome, Teammate!!!!
Efficiency of formulae in DAX ....great ❤️
I am impressed and inspired. Thanks heaps for your video, Mike!!!
Thanks Mike.. always something new and interesting coming from EIF. Thumbs up!
Thanks for the Thumbs Up and Comment, Wayne : ) : )
the good thing, is the power bi interface replacing the black with the white I think is much better:)
Great video mike, the most important series, and complete about data analysis and with E-DAB you gate all the knowledge good job Mike good job!!👍👍👍
Note: up date your dax studio 🙂
Yes, Thanks, I will update soon : )
this video I need to watch it over and over to get all the info on it.
The good news is that there is a rewind button : )
AMAZING SIR!
Another great video from Mike
Glad you enjoyed it : )
Incredible :) Go Team!!!!
Go Team!!!!
Dax Dax Dax. I desperately miss Excel Magic Tricks !!
Yes, but DAX can make complex Worksheet Formulas less complicated... Okay, Okay... More EMT soon : )
@@excelisfun EMT's yes, I miss them too.
@@johnborg6005 , Me too! Then it is settled: I will have to do some cool EMT, coming right up !!!!
thanks for this great video..as usual
You are welcome, Syed : )
Thanks Mike very interesting.
You are welcome, John!!!!
Mike, thank you so much for your great videos. But this time, I somehow cannot obtain the files from your link. I get a message "That page can't be found." from the both links. I can connect to the pdf note though... It will be very nice if you can help me on this matter.
Thanks Mike
i have a question: in the measure : Averagex(Values(date[Year-Month]),[Total Sales]), is it necessary to use values function even if the column date[year-month] has a unique list ?
Hi Mike
Many thanks for this video..great as usual ;)
I have a question please if you could help me ..
Is there any dax formula for Half Year To date ? For one semester cumulative dynamic total
If not how do you think we can get it ?
Thank you so much
Wow!!! I had no idea. Then you asked and I figured out this fun way to accomlich your Half Year Goal:
Here is the normal Measure for Yearly Running Total: Running Total YOY:=CALCULATE([Total Sales],FILTER(ALL(dDate[Date]),dDate[Date]6)
Then we need this Measure: Running Total YO-half-Y:=CALCULATE([Total Sales],FILTER(ALL(dDate[Date]),dDate[Date]
Please help support the excelisfun channel with your comments and thumbs ups on each video that you watch, Wajdi : )
Mike, that’s a very beautiful measure, right there...
...now, let’s take it up a notch: imagine you calculate Measure M1 which is the difference of two running totals (ins and outs) yielding some “net capacity” of ‘something’ (could be: current liquidity, number of people inside a building, etc.) at any given time.
Imagine you calculate this with a certain granularity over some time period.
Now you want to calculate the MAX of M1 over some (larger) period (day, week,...). How would you do that?
I can’t seem to get it working even though I replaced the MAX-function by EARLIER inside a MAXX-context...
Forget about this question: I gave the correct answer (I made a mistake in the ALL-argument - it’s best to put in the entire table there and not just one particular column). :-)
@@excelisfunMerci... Thank you so much Mike....I appreciate a lot your complete and quick answer..
Just one precision to understand well your solution before testing it : may be I was not enough clear in my question : my Financial year is divided into 2 financial exercices .. from jan to jun and from jul to dec.
I would like to add a dynamic cumulative measures called Term To Date within the semester depending on the month selected ( May 19 is selected : TTD = total from jan 19 to may 19
Sep 19 is selected : TTD = total from jul 19 to sep 19 )
Does your solution will work for this case ?
Thanks a lot/ Merci beaucoup ;)
Mike, this video teaches a best practice to optimize DAX, which is the subject of an advanced workshop by Marco and Alberto. Their tool enables users to compare DAX in terms of efficiency. Is there such a tool to optimize Excel formulas? Thanks.
Yes, many of us have timed Excel Worksheet Formulas for decades with Excel MVP Charles Williams tool : www.decisionmodels.com/FastExcelV4.htm
Hi! I have a table that contains different performance values for a group of 20 events. I need to calculate what is the difference between the average of each of those events and the global average. Can you advise on the best measures, please?
Good stuff! Love the videos!
Glad it is good for you, Joseph!!!
Thanks Mike
You are welcome, Dave : )
Wow! That is some good insight into DAX optimization, right there!
I did not expect that - I have the greatest respect for the iterators, but as it turns out: other functions can be more efficient.
Now, all of this concerns AVERAGEX, is there an equivalent optimization trick for MAXX? (I doubt it...)
BTW: great video format: show it once, then only show the results: avoids tedious repetition and makes it even more action packed.
I do not know : (
BTW, Awesome Teammate Geert, the iterator functions are really awesome and have so many uses. I use a formulas like AVERAGEX(dDate,[Total Sales]) all the time because it is elegant and easy and the benefits of a different, more efficient method are not necessary. But it does pay to understand this all because when we need to tighten things up, it is nice to know this and apply it : ) But you knew that, Teammate : )
I mean, I can't think of an alternative for Max at this point : )
Mike, don’t worry: I haven’t lost my respect for the iterators :-). That will never happen.
Furthermore, because of that other conversation we’re having, my respect for the aggregators has increased. Not so much because of their primary functions, but because of their capacity of context modifiers. Using the line: dData[date]
Excellent video Mike, thaks a lot !!! My inital guess with regards to the performance was wrong.... :-(
What was your initial guess?
top
Hi sir I have a doubt.
Here's how to find out how many birthdays are in total Count?
1. 02-Sep-1992
2. 03- Nov-1993
3. 04- oct-1994
4. 02-Sep-1992
5. 03- Nov-1993
6. 04- oct-1994
My MSPTDA query almost shut down the company server! :)
Sorry about that... MSPTDA is usually much nicer than that!!!!