DAME 06: DAX Fundamentals in Power BI & Power Pivot: 2.5 Hour Free Class
HTML-код
- Опубликовано: 15 июл 2024
- Download files: excelisfun.net/files/DAMEwith..., pdf notes: excelisfun.net/files/06-DAMEM...
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!
Free RUclips Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about
This video covers.
1. (00:00:00) Introduction and video topics
2. (00:00:25) Topics in Video
3. (00:01:49) Why we use DAX, M Code & Worksheet Formulas: What makes Each Special?
4. (00:05:30) Review data, data structure, Excel files, Power BI Desktop files and pdf notes
5. (00:06:33) What is DAX?
6. (00:08:53) Comprehensive Discussion about Star Schema Data Model Components and how they interact with DAX: Columnar Database, Relationships, DAX Formulas, Hidden Columns
7. (00:15:25) Calculated Columns
8. (00:18:28) Row Context
9. (00:21:58) Measures
10. (00:24:04) Power BI Desktop Calculated Columns and Measures
11. (00:27:38) Data Model PivotTable
12. (00:28:41) Filter Context
13. (00:31:58) COUNTROWS Function (Super Charged COUNTIFS Function)
14. (00:32:51) Expanded Table Diagram
15. (00:35:25) Implicit Measures
16. (00:37:20) SUMX function and Iterator Functions to Replace Calculated Columns
17. (00:38:56) Compare SUMX One Step Method to Calculated Column SUM Two Step Method
18. (00:41:06) Row and Filter Context Work Together
19. (00:43:07) Using Measures in other Measures
20. (00:44:21) DIVIDE Function
21. (00:45:50) CALCULATE Function to Change Filter Context
22. (00:47:10) ALL Function to remove filters and get a Grand Total
23. (00:49:26) % of Grand Total Measure
24. (00:50:01) ALLSELECTED Function to get Filtered Grand Total and % of Filtered Grand Total
25. (00:52:31) % of Parent Total DAX Measure: 1) ALLEXCEPT Function and 2) ALLSELECTED & VALUES
26. (00:54:14) Compare ALL and VALUES Functions
27. (00:55:23) CONCATENATEX Function
28. (00:59:21) YOY Change Formula with SAMEPERIODLASTYEAR, CALCULATE, DIVIDE, HASONEVALUE and a special filtering Calculated Column in Data Table
29. (01:02:17) Variables in DAX using VAR and RETURN
30. (01:05:58) Calculated Helper Column to make Measure less complicated
31. (01:11:06) Boolean Filters
32. (01:13:21) Second Look at Filter Context
33. (01:14:36) Overwrite Operation
34. (01:16:26) FILTER & ALL for Boolean Filter
35. (01:17:54) Logical Tests in FILTER and CALCULATETABLE Functions
36. (01:20:10) FILTER & VALUES for Boolean Filter
37. (01:22:02) KEEPFILTER to Convert Overwrite Operations to an AND Logical Tests
38. (01:23:44) Boolean OR Logical Test with Double Vertical Bar Operator
39. (01:25:07) Self Filtering Report with KEEPFILTER
40. (01:25:36) Filter Context with KEEPFILTTERS
41. (01:26:21) Boolean OR Logical Test with IN Operator
42. (01:27:16) NOT Logical Test
43. (01:28:18) Context Transition in Calculated Columns
44. (01:32:18) Hidden CALCULATE in Measure
45. (01:34:26) Context Transition in Iterator like AVERAGEX. Calculate Average Monthly Sales.
46. (01:39:30) Context Transition and Filter Context
47. (01:41:03) Context Transition Error: Iterate Over Table with Duplicate Errors
48. (01:43:46) Context Transition: Correct Formulas and Incorrect Formulas
49. (01:46:23) Context Transition Error: Iterate over Fact Table
50. (01:47:28) Grain of Calculation & Iterator Functions for Transactional, Daily and Monthly Averages
51. (01:49:46) Average using DISTINCTCOUNT to make a faster formula
52. (01:51:49) Cardinality and Iterator Functions. See five examples of howto reduce cardinality and increase formula calculation speed
53. (01:57:47) DAX Studio to time formulas. EVALUATE Command.
54. (02:00:03) Complex Filter and Complex Filter Reduction Error (from Overwrite process): KEEPFILTERS or Data Modeling?
55. (02:02:37) KEEPFILTERS in Power BI Quick Measure
56. (02:03:58) 12 Month Moving Average DAX Measure: CALCULATE, AVERAGEX, DATESINPERIOD, IF, MAX Functions
57. (02:08:18) Table Filters in CALCULATE to go backwards across a Many-To-One Relationship
58. (02:11:28) Unmatched Items in a Relationship
59. (02:13:03) DAX Approximate Match Lookup
60. (02:17:43) DAX to create Date Tables in Power BI using GENERATE, ROW, CALENDAR and more
61. (02:19:44) Extract Data From Power Pivot Data Model using Existing Connections
62. (02:25:17) Query View in Power BI Desktop
63. (02:30:33) Video Summary and Conclusions
64. (02:31:20) 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
Hi Mike,I've been following you since 2016. When I started, my Excel knowledge was limited to basic functions like sum, max, and average. Now, thanks to you, I'm an expert in Excel. I have a folder on my computer dedicated to all the resources you've provided, and I download your videos to watch offline when I'm traveling or in areas without network access.I only completed high school and was working a data entry job in 2016. Since following you, I've undertaken many projects in my organization, including macros and automation. In October 2023, I was promoted to Assistant Manager, and I owe it all to you. Thank you so much!
That has been my goal for 16 years at RUclips: provide all my Highline College classes (videos, notes, files) to the world for free. I am so glad that I have been able to help you : ) : )
Best Teacher I've ever seen ❤
Thanks for your kind words : )
Thanks for sharing it Mike.... You are one of the best human being on earth..
You are welcome, RRR!!!!!As I always say, it is my duty to try and make the world a better place, and this is one way that I try to do that : )
Wow! Michael, You are the BEST! another great video on DATA ANALYSIS, thank You so much for such masterpieces that keep coming!
You are welcome for the 2.5 hours of masterclass, danselb!!!!
This video is a must-have reference source for anyone looking to learn, update, or even improve their DAX skills. God bless you men, great work you are doing.
You are welcome for the work, but it is just one man: excelisfun ; )
It will be a perfect refreshener to me just after i got PL-300 certificate last week thanks to you man. Most important thing in Power BI is DAX learning and practice. People demand thousands of dollars to teach this and you give the best quality stuff for free as always. Maybe i repeat myself a lot in the comments but i want you to be sure that you are fully aprecciated by learners.
Thank you very much for your repeat!! I appreciate your full appreciation : ) : ) : )
Many years ago, I took some of those thousands of dollar classes about data analysis and DAX, and all they did was confuse me. Information on a plate is no good. Good stories matter.
@@excelisfun :))) I am a follower of your channel since the early 2010's, it is very emotional for me, all those hard work and dedication crowned with the official Microsoft certificate. I always try to write in the comments but I had to change my account's email, all those comments have gone; but really, I was thinking I could never learn those things, but you gave me tons of knowledge and self-confidence; after 14 years, I have two official certificates and lots of projects I did, and none of them would have been possible without you. Also, your book is simply amazing; you explained everything step by step and with screenshots. Then I took the official Microsoft course on Coursera; it was 150 hours but actually two hours of information. Your one hour of lesson worths hundreds hours of others, I must say this. Thank you man, you are the best.
@@excelisfun Hey Mike, do you have any good reference books for data analysis/business intelligence? Ideally something that speaks in plain english, rather than some overengineered technical jargon!
@@qasimawan3569 I know of none. That is why I make my videos : )
I just finished this awesome video. There are numerous knowledge of DAX packed in this epic video. Thank you for you great work Mike 💚👍👍
You are welcome for the work, longtime Teammate kiwikiow!!!
what a man,what a lecture.you deserve noble prize.
lots of thanks and love
Thank you for your kind words, Abdul!!!
Great tutorial! Thank u
You are welcome!!!
Very explicit and insightful. Thanks for sharing!!
You are welcome for the share!!!!
Great Mike ❤
Glad it is great for you!!!
Epic.Great One.
Glad you enjoy the epic!!!
Amazing masterclass. THANK YOU !!!
You are welcome!!!!
Thanks Mike!!!
Taking the liberty to make a small appeal.
To all those here who appreciate Mike's work, should support the man by paying a small subscription. :) :)
Thanks for the shout out, Teammate Formula Guy John!!!!
I am your student sir I learned everything from you excel, Power BI and the main thing to build logic...now I am doing a job.. it's all CZ of you sir.. May you live along
I am so glad to help you with the logic!!!
D Dazzling..
A Astounding...
M Majestic..
E Exquisite...✌😉
Thanks, Poet Excel Lambda : ) : ) : )
Excellent.....as always, thanks Mike....you are the best...
Thank you for your kind words, Mr Fredy!!!
Hi Mike,
Thank you for making these videos. Even as a Power BI Consultant this DAX video helps me understand row and filter context. Clearest explanation I've ever gotten in six years of consulting. You also helped me out during with my bachelor's degree with you VBA videos. Thanks again :)
Thanks for your kind words : ) I am very glad to help you with DAX!!!
Thanks Mike, again very good and extensive. You make all older video's about DAX (including your own...😉) obsolete. Especially thanks for the PDF handout very handy if you want to look up something. I always wonder how you get the time to figure all these issues out. But hey, Excel and Power BI is fun, so time is relative...😉
I wonder how I get the time too. To figure all the things out takes a lot of concentrated time. Many of these huge videos take 100-200 hours to make. I am glad it is all helpful for you, Bart!!!
Thank you so much amazing Mike for this EXCELlent video.
You are welcome for the EXCELent fun, Fellow Teacher!!!
Happy Memorial Day Mike! Great way to celebrate the start of the week! Thanks for all you do for the Team!!
You are welcome for the all for the Team, Teammate Chris M!!!!!
Thank you Mike. Excel IS fun when you explain it. I've been a subscriber since 2008, I guess. Thank you so much for all the great videos.
You are welcome for the 16 years of fun!!!!
DAME! Dax Amazing 😂
Thanks Mike
You are DAXDAME welcome, Lester!!!!
Another milestone 💯💯
Glad it is 100 for you, Lucaviglio!!!
@@excelisfun more than 100 Mike💪
Thanks for your video.
Hopefully, your channel will hit 1million subscribers ASAP,
I hope this will come true no later than the end of this month, June 30 ^^
: ) : ) : ) : )
You are welcome for the video!!!
DAX is daunting ,
That is true. It is the most daunting aspect of Excel and Power BI that I have encountered. With my video, I try to tell stories to make the complicated less complicated. I hope it works : )
Thanks a lot, lot of information to be consumed! 👍🏻
Could you explain how to deal with two fact tables, like sales and budget; to monitor achievement percentage?
Two fact tables is all about graina nd allocation of one of the tables to the other table's grain to create one fact table from two. Here is an epic video all about this:
ruclips.net/video/KTPjlM3sEDI/видео.html
Hello Mike, another to add to my to-do list, BTW I was searching for a video, where you use a double unary to allow say ; EOMONTH , to handle an array so EOMONTH(--(date_range),0),
can you put me out of my misery. 😳😳
To-do lists... I have so many also ; )
EOMONTH trick:
at 2:20 minute mark: ruclips.net/video/L0KY7pHgudM/видео.html
original video about EOMONTH trick: ruclips.net/video/hE4Mo_yrxxw/видео.html
@@excelisfun Thank you.
Hi Mike at 11:19 you have a small typo in the comment label. It's written "Start Schema Data Model" this t in the first wort is too much.
Thanks for the editing help, Teammate!!!
How many DAME classes are there?
Here is the playlist with the 6 videos that I have posted so far:
ruclips.net/p/PLrRPvpgDmw0lAIQ6DPvSe_hfAraNhTvS4
Is it okay to transform my data in Power Query, create measures in Power Pivot, and only open my workbook in PowerBI to do the visualizations? That is, I prefer to create measures in Power Pivot and not in PowerBI.
Yes, you can build model in Excel Power Pivot and then import the model into Power BI. I do that often.