Full Free DAX Class: Fundamentals of DAX in Power BI & Power Pivot. 365 MECS Class 17
Вставка
- Опубліковано 29 чер 2024
- Download Zipped Folder with All Files: excelisfun.net/files/17-M365E...
50 pages of pdf notes: excelisfun.net/files/17-M365E...
This video teaches how the fundamentals of Columnar Database, DAX Calculated Columns, DAX Measures, Row Context, Filter Content, Context Transition, Overwrite Operation, DAX X Iterator functions, DAX CALCULATE function and much more!
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: • Microsoft 365 Excel & ...
This video covers.
1. (00:00) Introduction and video topics
2. (00:25) Topics in Video
3. (01:13) Why we use DAX, M Code & Worksheet Formulas: What makes Each Special?
4. (04:54) Review data, data structure, Excel files, Power BI Desktop files and pdf notes
5. (05:57) What is DAX?
6. (08:17) Comprehensive Discussion about Star Schema Data Model Components and how they interact with DAX: Columnar Database, Relationships, DAX Formulas, Hidden Columns
7. (14:49) Calculated Columns
8. (17:52) Row Context
9. (21:22) Measures
10. (23:28) Power BI Desktop Calculated Columns and Measures
11. (27:02) Data Model PivotTable
12. (28:05) Filter Context
13. (31:22) COUNTROWS Function (Super Charged COUNTIFS Function)
14. (32:15) Expanded Table Diagram
15. (34:49) Implicit Measures
16. (36:44) SUMX function and Iterator Functions to Replace Calculated Columns
17. (38:20) Compare SUMX One Step Method to Calculated Column SUM Two Step Method
18. (40:30) Row and Filter Context Work Together
19. (42:31) Using Measures in other Measures
20. (43:45) DIVIDE Function
21. (45:14) CALCULATE Function to Change Filter Context
22. (46:34) ALL Function to remove filters and get a Grand Total
23. (48:50) % of Grand Total Measure
24. (49:25) ALLSELECTED Function to get Filtered Grand Total and % of Filtered Grand Total
25. (51:55) % of Parent Total DAX Measure: 1) ALLEXCEPT Function and 2) ALLSELECTED & VALUES
26. (53:38) Compare ALL and VALUES Functions
27. (54:47) CONCATENATEX Function
28. (58:45) YOY Change Formula with SAMEPERIODLASTYEAR, CALCULATE, DIVIDE, HASONEVALUE and a special filtering Calculated Column in Data Table
29. (01:01:41) Variables in DAX using VAR and RETURN
30. (01:05:22) Calculated Helper Column to make Measure less complicated
31. (01:10:30) Boolean Filters
32. (01:12:45) Second Look at Filter Context
33. (01:14:00) Overwrite Operation
34. (01:15:50) FILTER & ALL for Boolean Filter
35. (01:17:18) Logical Tests in FILTER and CALCULATETABLE Functions
36. (01:19:34) FILTER & VALUES for Boolean Filter
37. (01:21:26) KEEPFILTER to Convert Overwrite Operations to an AND Logical Tests
38. (01:23:08) Boolean OR Logical Test with Double Vertical Bar Operator
39. (01:24:31) Self Filtering Report with KEEPFILTER
40. (01:25:00) Filter Context with KEEPFILTTERS
41. (01:25:45) Boolean OR Logical Test with IN Operator
42. (01:26:40) NOT Logical Test
43. (01:27:42) Context Transition in Calculated Columns
44. (01:31:42) Hidden CALCULATE in Measure
45. (01:33:50) Context Transition in Iterator like AVERAGEX. Calculate Average Monthly Sales.
46. (01:38:54) Context Transition and Filter Context
47. (01:40:27) Context Transition Error: Iterate Over Table with Duplicate Errors
48. (01:43:10) Context Transition: Correct Formulas and Incorrect Formulas
49. (01:45:47) Context Transition Error: Iterate over Fact Table
50. (01:46:52) Grain of Calculation & Iterator Functions for Transactional, Daily and Monthly Averages
51. (01:49:10) Average using DISTINCTCOUNT to make a faster formula
52. (01:51:13) Cardinality and Iterator Functions. See five examples of howto reduce cardinality and increase formula calculation speed
53. (01:57:11) DAX Studio to time formulas. EVALUATE Command.
54. (01:59:27) Complex Filter and Complex Filter Reduction Error (from Overwrite process): KEEPFILTERS or Data Modeling?
55. (02:02:01) KEEPFILTERS in Power BI Quick Measure
56. (02:03:22) 12 Month Moving Average DAX Measure: CALCULATE, AVERAGEX, DATESINPERIOD, IF, MAX Functions
57. (02:07:42) Table Filters in CALCULATE to go backwards across a Many-To-One Relationship
58. (02:10:52) Unmatched Items in a Relationship
59. (02:12:27) DAX Approximate Match Lookup
60. (02:17:07) DAX to create Date Tables in Power BI using GENERATE, ROW, CALENDAR and more
61. (02:19:08) Extract Data From Power Pivot Data Model using Existing Connections
62. (02:25:01) Video Summary and Conclusions
63. (02:25:48) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/... . Artist: audionautix.com/
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #datamodel #DAX
Well Mike. It is obvious that you love what you do and this must be a big payoff for all these years of free sharing a huge load of excel knowledge with us. Nonetheless, let me express my sheer gratitude for the unparalleled level of your teaching, the excellence of videos and supporting materials, the exhaustiveness of the topics you present. What can we say? You have managed to create a global online community and inspire us to improve daily. My respect.
Thank you for your kind words! I am happy to help the Team. I am less of a teacher or trainer or book writer or video creator, and more of a crazy artist that wants to make a compltete story and stories that try to make the complicated less complicated.
Gratitude, Mike! Your presentation on Power BI transcended mere instruction; it was an odyssey into comprehension. Your adeptness in unraveling intricate formulas and abstract notions bestowed upon me the clarity of vision, revealing the inner workings of Power BI's essence.
Thank you very much for your kind words. I think about teaching as story telling. It's all about the careful details that go into the story : ) : )
Important Notes (including error correction):
1)
This video is not for the faint of heart because it is 2.5 hours in length, it has 50 pages of pdf notes and provides 10 files for you to use and follow along. This DAX Flix shows 67 different DAX formulas with all the basic to advanced topics and concepts such as Row Context, Filter Context, Context Transition, Overwrite Operator, DAX Queries, SUMX and CALCULATE and ALL and ALLSELECT and AVERAGEX/VALUES and KEEPFILTERS and SELECTCOLUMNS and so many other powerful functions!!! You can call this video hard core because all in one video I will cover the many invisible and difficult topics in DAX, but I will try to visualize and describe those topics and illuminate the unique and spectacular power of DAX as compared to Worksheet Formulas and Power Query M Code! If you are a beginner, this is your ticket to DAX mastery, but get ready to study the video and pdf notes and examples many times to truly absorb and assimilate the many concepts! If you are already good with DAX, the video and pdf notes should put all the pieces together into a cohesive understanding so that you can deploy DAX to solve your calculation and data analysis tasks with easy and fun! DAXisfun!! Go Team!!
2)
12 month moving average formula in Power BI Desktop file is not correct. I incorrectly added the filter dDate[Year]>2017 to CALCULATE. I fixed the downloaded files, but not the video. This is correct:
12MonthMovingAverage =
VAR Move12MonthAve =
CALCULATE(
AVERAGEX(VALUES(dDate[EOMonth]),[TotalSales($)]),
DATESINPERIOD(dDate[Date],MAX(dDate[Date]),-12,MONTH))
RETURN
IF(MAX(fSales[Date]),Move12MonthAve)
3)
I added Homework and Homework Solutions to the kipped folder as of 01:16 PM Pacifc Standard Time May 18, 2023
This Is the only way....no shortcuts
@@lucaviglio1206 100% TRUE. The only way to get knew knowledge into our heads: work hard. That is why all of this is so much fun ; )
@@irfanabbassi9202 The weekend with be as much fun as DAX Context Transition used over a Unique List : ): ) : ) : ) : )
@@bandini1978 That is a beautiful simile!!! I love health food!!!!!
Speechless! If there is one Excel/PBI source in the world that every one should learn, this channel is.
Thank you, sir!
You are welcome, Ivan!!!!!!
Hello Mike, it's been a long time since I've been actively following you on UA-cam. Don't ask me why. Because if I learned a lot of Excel from one person, it's you! Since a few weeks, I started to get into PBI, PQ and DAX. And of course I end up on your channel. Where again I come across hundreds of videos on this subject. This small contribution does not cover the efforts you make to teach the whole world Excel, PBI and much more for free but is the least I can do to "express" my appreciation.
PS: Slaying Excel Dragons is still the best Excel book for beginners as far as I am concerned. Even though this one is now about 12 years old. The principles are still the same.
Thank you very much for your kind donation, long time Teammate!!! I am glad that you have boomeranged back and are discovering the power and beauty of Power Query, DAX, Power BI and more. Thanks for the kind words about Slaying Excel Dragons too!
Thanks! Lifetime of expertise distilled into 2.5 hours. Just wow. Genius 🙏🎇
Thank you for your kind donation!!! And the good news is: the video is always here for you when you need a refresher!!
I don't know which I am more in awe of, the Knowledge or the Generosity
They go together cuz my job is to make the world a better place : ) : ) I am glad that this is useful!
Thanks for all your efforts all this while. My excel journey starts from your basic excel video. Way back from yesteryears. Thanks 3000.
You are so welcome for all the videos since Excel Basics!!!! Thank you so much for your kind donation : ) : ) : ) It really helps, BabyDollStroller!
thank you sir! Finally I will get the chance to work on PBI projects as Business Analyst thanks to your unreserved and inexhaustible support!
Thank you very much for all the help you provide. Back in 2016 I passed the Microsoft Certification test and could not have done it without your help. I'm now working on enhancing my DAX skills and your site is amazing. Thanks again.
Incredible course. I have watched / tried out so many courses and methods to get where I need to be with PBI/DAX and I just wasn't finding what I needed, until now. This is the one. You go into explanations and show examples of so many things that I feel are often brushed over. What a gift.
Legendary video from a legendary man....... Sooo much is changing every day.... It is becoming very tough to keep updated
This video should help with the foundational DAX!! You will have great fun learning DAX, RRR!!!!!!
Epic? I would rather say it´s legendary! Thank you so much Mike for sharing your God level knowledge with rest of us mortals.
You are welcome!!! daxisfun
2hrs 26 minutes off pure heaven. Looking forward to watching your tutorial over the weekend. 👍
Yes!!!! A DAX Weekend is a Great Weekend : ) : )
¡Thanks Amigo , the effor that you've done by preparing all this material deserves at least a this tiny recognition from my side ...Please keep this kind of outstanding videos+ materials with all your foreing students!!! regards from Uruguay!!
Thank you for your awesome donation!!!! For 15 years here at UA-cam, I just keep posting so people like you can learn for free : ) This is video #17 in a full class. The whole class has everything that you need.
Minute 10:00 is EYE - OPENING. Thank you Mike! I will watch this completely. Up to minute 10, for now.
Rad, Skate-Punk-BMX Brother!!!!! Fun with big data : ) I can't wait to some day ride and get rad with you : )
Hi Mike: this was awesome!! 🙏🙏👍👍. Actually I have seen all those DAX formulas a 100 times, but with your explanation it all comes together. I love it that you compare Power Pivot and Power BI. One advantage of Power Pivot is that you can filter the fact table and the measure updates. Mike you can really be proud on yourself creating this masterpiece. I did not do research but I am convinced the Excel community is also for sure proud on you... Thanks again!!
Thank you very much for your kind words, Teammate Bart!!! As I often say, the story is the key to try to bring light to difficult topics. DAX is very hard. It is hard for me. That is why for this video and notes, it took me longer than any other video I have ever posted. But since my goal and significant source of happiness is to try and help the Team, BAM: I am stoked too. Go Team!!!
This is absolutely amazing. I've watched so many videos regarding DAX, but your explanations are so thorough and clear; I am so thankful that you have decided to share this! I know I'll watch it at least one more time, along with all the other videos in this series. Thank you so much for sharing your experience and knowledge!
Hey mike, i remember back in the day i sincerely hated your channel, well not your channel but rather excel, it was a scary tool that made me feel like dumb, today your channel is one of my favourite and god knows there are many good quality excel channel outthere. Can't wait to finish this one. Let's go team 😀
Yes!!!! Glad to help with the DAX. Go Team!!!!!
We are truly grateful for your kindness and generosity. 🎉❤
Glad to help : ) Go Team!!!!!!!
Heart filled, thanks for the efforts put in to make this video......❤ Excel is always fun with you.
I am so glad that Excel cab be fun, Santosh!!!! Thanks you for your kind donation and your continues support, Teammate!!!!
After so many years I just can't get enough.. what a brush-up of MSPTDA 18 and 19 and as always top-notch ...
Fan for ever 👍
Nice remembering!!!!! It is exactly MSPTDA #15, 18 and 19, but with a few new examples and a better order, and told with a better story line this time!
As always, the ONE WHO DELIVERS, delivers again. And; as always Mike, I am in your debt. Coffee fund on it's way
Always happy to help deliver knowledge and fun!!!!! Thank you for the coffee : )
3:20 Avg = Sum/Count, so you can calc a month avg with 1 sheet formula =SUM(sales_column)/COUNTA(UNIQUE(eomonth_column)). Not the right solution everywhere, but is cool that you can do it. Appreciate these videos!
Edit: the point of this formula being that no intermediate group by pivot table is required! You can work directly from the raw fact table.
I never tried that formula. I am not sure it works in DAX unless there is an attribute column in fact table. But maybe you know a formula? : )
@@excelisfunDAX would be =DIVIDE([TotalSales($)], DISTINCTCOUNT(dDate[EOMonth])). This formula should give the same results as your [AveMonthSales] AVERAGEX( ) measure (1:35:23). Unsure whether one or the other is slightly more preformant / readable? At any rate, the Excel sheet version was the special one that I thought was worth sharing.
The logic being, if you look at the 3:20 intermediate table (group by EOMonth), the avg of those TotalSales($) cells is sum/count. Here, the sum of the intermediate table is the same as the sum of the ungrouped fSales table. Here, the count of cells equals the distinct count of EOMonth.
@@benrogers9092 I tend to not show that formula because if there are missing months, then it gives wrong answer. I have shown that formula in other videos. But to be safe, I just stick to DISTINCTCOUNT on fact table attributes. But if there are all months, it would be a faster formula.
Your videos are such a blessing.
Glad they help!!! This vid is a good one!
Thank you so much for making this great effort and excellent resource sharing and inspiring us to compare Excel, power pivot, and the Dax formula from power bi.
You are welcome for the inspiration!
What an unbelievably useful "summary" (it is condensed but at the same time has loads of detailed examples). This is the art of teaching at play from the "grand master of data".
Like Grand Master Flash and Parliament Funkadelic in the 1970s, but with data : ) : ) Thanks for the kind words!!!!
@@excelisfun who told you about the 1970s? surely, you were not borne then ... ;)
@@hthienel Yes, of course, I was born before then. I started skateboarding in 1973 (11 years old), started BMX in 1974 and was dancing to that rad music in the mid to late 1970s : ) I did not start Excel until the early 1990s ; )
@@excelisfun started with Lotus Symphony on the 3270, then Lotus 123, then WIngz in Unix (1m rows x 1m columns ... still unrivaled) ... Excel since the 90's too ... must have missed the memo on skateboards and BMX ... probably because the dogs and kids got in the way ...
@@hthienel Cool: Lotus! I did not use that. I started with Quatro Pro, then Mac Excel, then the real PC Excel.
Superb, I cannot wait to dig into this one - you are the best, can’t thank you enough!
You are welcome, and have fun digging!!!
Another Masterpiece !! Really Mike, I'm impressed how you keep going improving more and more the quality of your videos. Such detailed information and the way how is represented the concepts and all the integration behind scenes for better understanding, just amazing... Thanks dude, the level of effort for produce this must have been huge.
Yes, Huge!!! Most time ever spent on a video : ) Thanks for your kind words! And you are welcome!
A huge video. And a huge thank you. A difficult topic clearly explained and demonstrated. As always. Thanks!
You are hugely welcome!!!!!
Thanks!
Thank you so very much, Daniel!!!!! Your kind donation really helps : ) : ) : ) : ) : ) Go Team!!!!
Just watched the video Mike. EPIC DAX video. This one is a keeper for future reference, for sure. Thanks for the amazing video!
You are welcome, Chris M!!! I did make a mistake in the 12 Month Moving Ave formula in the Power BI Desktop file, dDate[Year]>2017 might get rid of the unwanted year, but then it made 2018 incorrect because the DATEINPERIOD dates then got run in an AND Logical Test... But the part in Power Pivot did not have an error : )
Thanks Mike. No. 61 is something I have not seen anywhere else. I have always felt "unfair" that I can create a table from DM in BI and not in Excel when dataset is beyond Excel's capacities. You solved it for me. Reference to DAX Studio definitely helps too... :) Thanks again.
Glad that pulling data from Power Pivot Data Model was helpful. It has been there since the beginning, but boy is it clunky... I have this trick buried in other videos going back about a decade. Maybe I should make a stand alone video about it...
@@excelisfun I think stand alone video is a good idea. I am sure many people will benefit. Thanks
Love u mike.....u are always an inspiration for me
Glad to help inspire!!!!!
Absolutely brilliant I can't thank you enough for this stellar video. Awesome Mike 👌
You are welcome, Long Time Teammate Nader!!!! : ) : )
Nice work Mike....You are the best...ExcelMan....Thanks a lot!!!!....
You are welcome a lot!!!!
Thanks Mike for all you do!
You are welcome!!!
Best teacher. Thank you
You are welcome!!!
Amazing, Mike. Thanks for sharing this super video!!😃
You are welcome for the video!!!
Your knowledge bank..Amazing
I am glad that you can make withdrawals from the excelisfun bank : )
One more masterpiece from Excel Guru ❤
I hope you will like it!!!! : )
Super great! Irarely use spreadsheet Pivot Tables any more but I often use the Data Model and find it really difficult to keep up with growing list of DAX capabilities. Its been hard to find good help with DAX so tis video is especially helpfull. Obviouly I haven't progressed too far (about 45 minutes) but so far so great!! Thanks.
DAX is harder than any other Power BI/Excel tool. But this foundational video should help : ) I hope it helps...
@ExcelIsFun i dont find the DAX funtions and modeling particularly difficult. But I do have a hard time finding the new fuctions and features and keeping up with the DAX rapidly expanding domain. I have not found a comprehensive list of all DAX functions and features. To me the most useful and intriguing aspect of DAX is the ability to efficiently change (row and Filter, etc.) context without modifying or supplementing the underlying data set.
@ExcelIsFun I believe that so far DAX has been caught in a circular trap: not enough users to incentivize a significant number of videos and other instructional aids but not enough instructional materials to significantly expand the number of users. Mabe this video will help break this cycle.
Thankyou for such good content specially Excel and now dax.
You are welcome for the context!!!!!
Quality lesson. Wonderful sir 🎉🎉🎉
Glad you like it, Prateekgupta!!!!
Hi Mike! Long time watcher, first time commenter :) Would appreciate it you could do a video on how to speed up processing time. That problem I'm trying to work out is why my Power Query files can take just 1 min to refresh all queries and load results (I have a file with about 20 queries), but in the PQ Editor window can often take many minutes (so far waiting for 25mins) to preview just 1 query. It seems very counter-intuitive! Would be very grateful if you can shed some light on this phenomenon!
Power Query cannot do iterating in a table that is inside another formula very well. The rule is to try and push those types of actions to DAX or sometimes even the worksheet. In general, both DAX and Worksheet can do those types of things more quickly. For example, in my book and this video, I mention that although we can make a formula to do approximate match lookup in Power Query, it is MUCH faster in DAX or Worksheet.
finally what i was waiting
Thanks Mike...
You are welcome, The Always Goel!!!
what a great video, thanks a lot!!!
You are welcome a lot!!!!
Here it is: your largest video to date! This is massive! And we know it’s gonna be good. :-)
The work of a Legend! :-)
(I hope you used the long outro ;-)
I hope it will be good : ) This was the hardest video I ever made, too many problems... Including that I forgot to have the correct long outro... I wish I did use it.
@@excelisfun No worries. Minor issue - nice to have. It’s the contents that counts. :-)
Great service indeed sir.
I hope you will like it : ) : )
Thanks Mike for the video. !!! This is GREAT!!!
A Formula Guy like you would have to love it : )
Do you use DAX a lot, John?
@@excelisfun i am retired now, but i still learn and practice on your data. Excel for me is a passion.
@@johnborg5419 Cool that you get to follow your passion!!! Me Too : ) : )
What a treat ❤
Glad it is a treat for you!!!!
Very useful video and clear explanation ❤😊
Glad it helps!!! DAX is fun : )
Glad this helps!!!!!
Thank you so much mike ..I owe you lot of thank you to getting me good in excel..I am really looking forward to watch this 2 hours video to master Dax ..
I would request you to kindly make more videos on m code as well ..
Lots of love from india
I have many videos about M Code. Did you see them? In this class videos 12 -16 are all M Code: ua-cam.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
I have others too, if that is not what you want.
Amazing video!
Glad you like it!!!!
Nice Mike...another great tutorial to study during the weekend 😂
Yes: DAX Study Weekend sounds fun : )
@@excelisfun Always fun
@@lucaviglio1206 : ) : ) : )
number one
♥
First Place Trophy for your comment ; )
Excel's World Champion
DAX gets the trophy ; )
@@excelisfun Actually, Number one pound for pound
Great lesson so far for me. Thank you very much for sharing you experience and knowledge. I follow the video and start doing the HW but cannot connect to the SQL database because of error "Cannot authenticate with the credentials to access this database". How can I fix this and finish the HW?
DAX by MECS => Done Again Xcelent by Mike Excel's Coolest Scientist 😉✌
Excellambda!!! : ) : ) : ) : ) Cool on the shoulders of masters like you!
Hi First of all thanks for sharing a great vdo on DAX/PBI, i am stuck at one point, at the Boolean filter point, am not able to filter down products for the regions. I am stuck here and can't any relationship to filter products based on Regions. Pls guide.
Hi Mike, for leap year, how do I exclude 2/29 in calculating Previous Period sales?
Thank you for the video. For the content at 1:17:17, how can we do the same stuff in Power Pivot or Power Query in Excel but not Power BI? It seems that there is no way to create a new table by FILTER in Excel.
❤❤❤❤❤❤❤❤❤
: ) : ) : ) : ) : ) : ) : ) : ) : )
Hi thanks a lot for the video, Do you know how we can make a relation when the key is compossed for more than one column?
You can join in a Calcuated Column or use the transform column merge feature in Power Query.
Thanks a ton, its really very helpful, I have a question!
I have one data table in which I have employee data along with Date of Joining and I created a calendar table and calculated the EOMDATE and trying to calculate employee ID for whom the Date of Joining is less than equal to the EOMDATE column created in the calendar table but it is throwing error...Please help...below is the formula
Headcount:=CALCULATE(COUNT(Data[EMPL ID]),Data[DOJ/Curr Hire Date]
Thank you Very Much Sir ....
I am getting error when closing ALL without anything GrandTotalSales:=CALCULATE([TotalSales],ALL()).
"Semantic Error : Too few arguments were passed to the ALL function. The Minimum argument count for function is 1"
Maybe you are in an older version of Excel" You can try to post the fact table inside ALL and that will accomplish the same thing.
@@excelisfun Thank you I have done the same
solved♥@@excelisfun
Hi Mike, I have no knowledge of the power query, or Power bi or power pivot. Can i start with this video? Or should I first watch the power query videos and then come to this? I want to know the correct sequence which I should follow.
Absolutely not. This is the last video in a sequence of videos for data analysis, Power Query, Power Pivot and Power BI lol This class teaches all of Excel and Power BI together, intermingled. For example, when I teach lookup formulas, I teach Worksheet Excel Formulas, Power Query M Code formulas and Power Pivot/ Power BI DAX all at same time. However, if you just want the data analysis tools (Power Query, Power Pivot, Power BI, M Code and DAX), you can do this same class but just study videos #4, 11-17:
ua-cam.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
If you want it all, study from beginning. Also, the classes that I offer for free here at UA-cam are different than most other because they are very detailed and filled with concepts rather than just solutions. My classes are long and detailed and take a long time to study. But they are amongst the very few that can make you a true master of the concepts, solutions and model building.
@@excelisfun thanks for your reply. Did you mean video # 14-17?
@@snjpverma No, 11, 12 and 13 are crucial to data analysis tools. #12 and #17 are just about the most important. #4 sets up all the tools and how they work together, then 11-17 are more detail.
@@excelisfun thanks a ton for the detailed response. Also, your videos are so informative, they are unmatched to any other channel. Huge respect.
@@snjpverma Glad you like them. Remember, they are not free, I charge a Thumbs Up ; )
Hi Mike, I'm a follower for a decade now but somehow missed (or have forgotten) some helpful basics of Excel. Which video you have that I can paste a Business Card in a cell so I don't have to key-in all other information in it? If I click the cell, the Business Card would automatically be enlarged for easy viewing. Any tip?
I have no idea whatsoever how to paste a business card into Excel... : ( Sorry
@@excelisfun I mean the Business Card as an image file.. Thanks just the same
@@DanAlvard Insert tab, insert picture from device.
Hi Sir, if there is a set of different Excel files and there are steps applied to them, like merge, combine and group, and there is an update in one/many Excel files in terms of values and it is not possible to update each and every column value, in this cane how can we replace the Excel file/files (keeping the same file name) so that the all the steps can/will be applied as it is after file replacement
I am sorry, I have no idea... : ( You can try posting your question to a form where you can have back and forth dialog to get Excel solutions, but you better provide a more precise question: mrexcel.com/board
Hi Mike, I am following along with both Power Bi and the excel starter files, I am very familiar with DAX and using your course to improve my knowledge. However, I have hit a problem. When doing the excel file and copying your tutorial word for word, I am not getting the fSales in the pivot data model. I m getting an fSales with a sigma sign at the beginning and just the TwoStepSales measure within that. Any ideas? I can send you a screenshot but dont know your email
It took me a while to figure out what you are asking because you did not say where this was happening. If it is the PivotTable Fields list in the worksheet area, then that is what it looks all fields are hidden in the data model: Just Sigma to indicate that the items in the list are Measures. Is this is relation to trying to do the Implicit Measure?
I have the same problem, any solution?
@@itamarmaron3062 If it is the PivotTable Fields list in the worksheet area, then that is what it looks like when all fields are hidden in the data model: Just Sigma to indicate that the items in the list are Measures. If you unhide fields "unhide from client tool", the sigma will go away and fields will be listed.
@@itamarmaron3062 I ended up using the final version and deleting the whole steps previous to where the problem arose
hi sir how i can downlaod fsale, dproduct, ddate, Dsalesreps, data sheets for practice.
The link is below the video.
dam i love you
Thanks for the love!!!!
Hi . How can download just excel file of this video?
Below video in show more area.
youranswer:=VAR Ave12MonthBack = CALCULATE([AveMonthlySalesEOM],DATESINPERIOD(dDate[Date],MAX(dDate[Date]),-1,MONTH))
RETURN
IF(MAX(fTransactions[Date]),Ave12MonthBack)
(1) I changed -12 to -1 and I did not get the monthly average as I expected.
(2) I changed MAX(dDate[Date]) to MIN(dDate[Date]) and -12 to 1. I did not get the monthly average as I expected.
That is not the formula I showed and it is not the formula in the solution file. The above formula looks like it fails because of Context Transition. This is the formula I showed (with formula rather than Measure in first argument of CALCULATE in both the PowerBI and Excel solution files):
=VAR Move12MonthAve =
CALCULATE(
AVERAGEX(VALUES(dDate[EOMonth]),[TotalSales($)]),
DATESINPERIOD(dDate[Date],MAX(dDate[Date]),-12,MONTH))
RETURN
IF(MAX(fSales[Date]),Move12MonthAve)
This is not my formula:
youranswer:=VAR Ave12MonthBack = CALCULATE([AveMonthlySalesEOM],DATESINPERIOD(dDate[Date],MAX(dDate[Date]),-1,MONTH))
RETURN
IF(MAX(fTransactions[Date]),Ave12MonthBack)
Where did you get this?
Video 14 is missing from the playlist.. Is it not available or available for any paid course or anything?
#14 us there. I just checked. NONE of the over 3,500 videos and 100 playlist classes that I have posted over the last 15 years costs money. All free : ) Let me know if you still can't find it.
@@excelisfun is video 14 of 3-4 mins??.. One in playlist I have seen.. But video 14 is not written and if it's lengthy video.. I want that you please share that🙏🙏🙏🙏
@@aniketsingh3364 Are you subscribed? And have you seen my intro video that shows how to use my channel. Here it is:
ua-cam.com/video/GNhN1Zw8oM0/v-deo.html
@@aniketsingh3364 Here is the MECS class playlist: ua-cam.com/play/PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW.html
#14 is in this class. Can you see it?
@@excelisfun yes yes.. Ty so much
Thank you for your work. I couldn't say more.
Thank you sir!🥰🥰🥰
You are welcome!!!
how do you combine obsolete sku and new sku for aggerated view?
Thanks!
Thank you very much : ) : ) : ) : ) Your donations help me to keep making videos for the world : ) : ) : )
Thanks!
Thank you very much for your kind donation! It helps a lot : ) : )
Thanks!
Thank you very much for the kind donation!!!! It helps : ) : ) : ) : )