Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships, Data Modeling& Visuals

[@excelisfun]

Full List of topics in the video: 1. (00:00) Intro & Overview 2. (00:41) Intro Song 3. (01:01) Compare and contrast Power BI & Power Pivot 4. (01:43) Why Excel people should learn Power BI 5. (02:19) Power BI Desktop used worldwide without the online option 6. (03:11) Power Pivot uses MDX and Power BI uses DAX (Must faster on Big Data 7. (03:44) Download free Power BI Desktop: powerbi.microsoft.com/en-us/desktop/ 8. (04:12) Look at data for project 9. (04:54) Look at New Power BI file and tour of Power BI user interface 10. (06:13) Power Query to import two tables from one Excel file 11. (07:40) Loading data in Power BI is different than in the Power Pivot tool 12. (08:48) Import and append data from multiple Excel files 13. (09:09) Why the From Folder Power Query option when you have Excel files is more complicated than when you have Text files 14. (10:27) Power Query From Folder to import files 15. (12:45) Close without loading query, to fix a mistake 16. (14:42) Power Query Excel.Workbook function to get Objects from an Excel file, using a Custom Column 17. (15:30) Intelligence and Tab key do not work in Power Query 18. (19:50) Append all tables from Excel files 19. (20:23) Add Data Types 20. (20:37) Using Locale Data Type feature to match imported dates with Regional Settings on Computer. Allows any country to use any other country’s dates and number settings 21. (22:10) Close & Apply Fact Table to Data Model and watch Relationships that are automatically created 22. (22:24) Create Date or Calendar Table using DAX Table functions. CALENDAR function. Also see DATE, YEAR, MIN and MAX functions 23. (23:13) Definition of Date Table 24. (25:38) Mark Date Table as a Date Table 25. (26:21) Create DAX Calculated Columns in the Date Table for Month, Month Number and Year 26. (26:47) Row Context in a Calculated Column 27. (27:02) FORMAT function to create Month Name 28. (27:48) Sort BY Column feature to get Month Names to sort correctly 29. (29:13) Create Relationship for Date field 30. (29:23) Create Date Table with GENERATE and ROW functions. See variables and fiscal period formulas in this DAX Table Function. Amazing formula!!!! 31. (32:05) Create Total Sales Measure in Data View (Table View) using the SUMX & RELATED functions 32. (32:52) Measure Icons in Power BI and Power Pivot 33. (34:52) create Measures in Report View on a Test Measure Page 34. (35:08) Matrix Visualization is VERY similar to a PivotTable! 35. (35:56) Drag and drop fields in Field area 36. (36:01) First look at the Format area and the Paint Roller option 37. (36:41) Move Totals from Top to Bottom 38. (37:15) Total Units Measure 39. (37:32) Add Number Format to Measure 40. (37:58) Frequency Measure with COUNTROWS function 41. (38:50) EOMONTH function in DAX Calculated Column in Date Table 42. (39:24) Why we need EOMONTH function for accurate average monthly sales formula 43. (40:00) Average Monthly Sales Measure with AVERAGEX, VALUES function 44. (40:36) VALUES function to get unique list of months 45. (41:45) YOY % Change DAX Measure using Variables, CALCULATE, SAMEPERIODLASTYEAR, IF and HASONEVALUES DAX functions 46. (46:40) Change Font size for subtotals 47. (46:47) Introduction to ALL and ALLEXCEPT DAX functions 48. (47:33) % of Sales Grand Total Measure using CALCULATE, DIVIDE and ALL DAX functions. 49. (48:10) Why ALL on Fact Table removes all filters in the Data Model 50. (49:16) % of Year Sales Measure using CALCULATE, DIVIDE and ALLEXCEPT, IF and HASONEVALUE DAX functions 51. (50:43) Hide fields in tables from the Report View 52. (52:09) What Relationships do 53. (52:36) Visualizations in Power BI 54. (53:20) Create Year Month Total Sales Matrix and Line Chart 55. (54:46) Turn off concatenated labels for line chart 56. (55:11) Measures in Tooltips 57. (56:58) Edit Visualization Title, especially when you use Tooltips 58. (56:35) Amazing Filter Pane in Power BI! 59. (57:41) Edit Interactions between visualizations for Matrix and Line Chart 60. (58:53) Upper Right Controls for a Visualization, including expecting data as csv file 61. (59:35) Drill Up, Drill Down, and move to a new Level 62. (01:00:39) What Line Charts do 63. (01:00:53) What is a Dashboard? Define Dashboard and what a Dashboard is in Online Power BI 64. (01:02:40) Create Year Metric “Dashboard” on a Page in a Power BI Desktop Report file 65. (01:03:11) What Tables or Matrix visuals do that is different than a chart or other visuals 66. (01:03:21) Visuals provide for immediate visual impact than tables 67. (01:03:29) What Column and Bar Charts do = Compare differences across categories. 68. (01:04:00) What is the difference between the Clustered Column/Bar and the Stacked Column/Bar 69. (01:05:37) Moving Visualizations 70. (01:07:16) Add Data Labels 71. (01:07:40) Edit Interactions between visualizations for Matrix and Column or Bar Chart: Filtered, Highlight or No Filter 72. (01:08:41) Third set of visuals 73. (01:09:00) Publish Power BI Report to Power BI Online 74. (01:09:30) Hide Measure Page 75. (01:09:45) What is a Workspace? 76. (01:10:06) Open Power BI Online 77. (01:10:27) Tour of Power BI Online 78. (01:11:04) Sharing reports and dashboards with Power BI Online 79. (01:11:45) Build a new Workspace 80. (01:12:05) Create a report from a dataset 81. (01:12:16) One Source of Truth Data Sets for Excel PivotTable 82. (01:12:35) Creating Dashboards in Power BI Online, including sharing a dashboard 83. (01:13:42) Add new Excel file to folder and see that everything in Power BI Desktop and in Online will update with new data 84. (01:14:45) Summary of what we did in video 85. (01:15:45) Conclusion and What’s in next video Four video release dates: Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations, kzbin.info/www/bejne/o3fYpKB3rsaaqc0 Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, kzbin.info/www/bejne/gqHZdJ1vq6iniKM Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, kzbin.info/www/bejne/m3rEfHZsr7GgmJI Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling, kzbin.info/www/bejne/rHOvfmarqL6FfJY Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas, kzbin.info/www/bejne/aHvPYnp7hK17jaM This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.

[@tumshukurumhalila9217]

Hey Mike! Pin this one 👆🏽👆🏽👆🏽

[@excelisfun]

@@tumshukurumhalila9217 Done!

[@rodrigocustodio1955]

Mike is the most talented Excel Guru you will ever find. Even the energy flowing from his delivery is amazing. Such a talent for teaching. GOD bless Mike for all the benefit that he has brought to the Data analysis/Excel community with his amazing and incomparable work.

[@excelisfun]

Thank you for your very kind words : ) : )

[@jeanphilippetombofoe4325]

Thank you Mike, I am currently preparing PL-300 certification and this is by far the best practical training I have done so far. The case study is close enough to real business situations with the right amount of data to practice a lot of Power BI features. Your explanations are clear and I do like the duration of the video around a little more than an hour which is less intimidating. Merci beaucoup as we say in french :)

[@excelisfun]

: ) : )

[@reanalytics1863]

I have made it a duty to first like, then share to a few friends before watching. Mike has never disappointed from day one

[@excelisfun]

Thanks for your support : ) : )

[@rodneyoganga6693]

The DAX measures are a challenge for me to master but with time, i'll grasp it, and once again IMPECCABLE job with a well articulated tutorial Mr. Mike Garvin

[@excelisfun]

Glad it all helps, Rodney!!!!

[@shaimamustafa1871]

One of the best really. I've been struggling with my big data at work and making pivots was taking a lot of time. Thank you for your effort!!

[@ambotsaimo9467]

Sir Mike, thank you for sharing your knowledge without asking for any payment. You are amazing! You are the best teacher I ever have as of this time. You are doing great things that helps people like me who wants to improve office work performance and make job easy. God Bless you and your family.

[@excelisfun]

Glad my posts help, Ambot!!!!

[@ambotsaimo9467]

@@excelisfun I am the same Ambotsaimo Maganda that you have friend in FB! Have a good day. I am not done watching the 55 videos on PowerBI

[@HusseinKorish]

Amazing ...One video equals a whole powerbi tutorial ... thanks Mike ...this video will be my reference for the future.

[@excelisfun]

Yes, I like to make videos that have it all in one place. You are welcome, Hussein!!!!

[@GeertDelmulle]

Just as beautiful as the previous one. Yet again, this one oozes quality and knowledge that are indispensable for the true practitioner. No one does it better. No one. You deserve the Oscar for best series for this!! Love it! Now I'm gonna watch the next one, because I need to catch up. :-)

[@excelisfun]

Thanks for your kind words. Indispensable for the true beginner : ) For true Advanced practitioner for DAX and Power BI, gotta go to others like Russo and Ferrari and Webb ; ).

[@danielwerner9543]

The best part is he sounds like Nicholas Cage!!!!

[@sajidrahhab9569]

the best Excel channel

[@excelisfun]

Thanks, SAJID!!!

[@andrewjohnson4352]

Finished this vid! Now to the 4th and final of the series!

[@excelisfun]

Yes!!!!!

[@mdx675]

Mr Gravin, I am going to buy your book just to thank you for your INCREDIBLE job with this channel in general, and with this Data Analysis series in particular. THANK YOU SO MUCH!!

[@excel_stuff]

Excel Is Fun - but it's also really challenging! I can see I'm going to have to repeat these classes several times to get my head around everything. As always, Mike, thank you for sharing your amazing knowledge so generously.

[@rodneyoganga6693]

Me too, especially the formulas, but if we practice and understand, we shall grasp it.

[@MostafaAhmed2024]

thanks for the all series form pivot table to power bi ,You are a legend :))

[@excelisfun]

You are welcome!

[@shahiinalam]

excellent work Mike

[@excelisfun]

Glad you like the EXCELlent work, Shahin!!!

[@jessicaestes]

Amazing video. This is better than any other tutorial I have seen. Thank you so much!

[@excelisfun]

I love to hear that it works for you, Jessica : )

[@csemanate]

Great information on Power Bi!

[@excelisfun]

Glad you like it!

[@yanickkenne9569]

fantasic video power BI. Mike thanks for taking time and share

[@excelisfun]

You are welcome, Yanick!!!

[@paulsingleton6071]

Hi Mike, thank you for your four recent tutorials on Excel Data Analysis. As usual, they have been both informative and concise in delivery. 👍 👏

[@elgennacorda4469]

Amazing, thank you Sir Mike 😊

[@excelisfun]

You are welcome, Elgenna!!!!

[@RevusDeonauth]

Great video once again. I have been watching your videos since 2013, and they have all been outstanding. Thanks again for another masterpiece!

[@excelisfun]

You are welcome for the videos and fun, Revus!!!!

[@kebincui]

Awesome 👍👍❤❤

[@excelisfun]

Glad it helps!!!

[@coogs9335]

Great Power BI videos Mike. Thanks for taking the time from your busy schedule and sharing such great knowledge. As we say from back from the 80s..you rock dude!

[@excelisfun]

You are welcome, Pete!!!! Glad to rock out the Power BI videos : )

[@jdparden]

My head is spinning with possibilities... Thanks!

[@excelisfun]

That is a good type of spinning : ) You are welcome for the boomerang like spin of awesome possibilities!!!

[@ogwalfrancis]

These last two advance videos reminded me of my favorite Excelisun KZbin playlist/class E-DAB, i need to watch all it's videos once again, eagerly waiting for the next one, thanks so so much Mike 🙏

[@excelisfun]

You are welcome so much, Stats Guy Ogwal : ) Here is the description that I wrote for the EDA class that compares it to E-DAB and MSPTDA: This is a complete course in Excel Data Analysis (EDA). It can used as a stand-alone class or as a review after the E-DAB & MSPTDA classes listed below. E-DAB is slower pace & has more basic detail. MSPTDA is more advanced with more technical aspects of the tools & data analysis. E-DAB & MSPTDA also have written pdf notes. This EDA class covers all the Microsoft tools such as PivotTables, Worksheet Formulas, Power Query, Power Pivot, DAX formulas, Data Modeling, Relationships, Power BI Desktop & PBI Online and uses the latest user 2021 interface.

[@ogwalfrancis]

@@excelisfun Almost everything is covered in these four videos, it's indeed a stand alone class and I keep loving the Excel and Power Bi desktop new user interface, great knowledge, great skills, great lectures always, great teacher and great videos all from Excelisfun, thanks so much the World Changer🙏🙏

[@excelisfun]

@@ogwalfrancis You are welcome so much!!!! I am happy to try and do my part to make the world a better place!!!!

[@sujith892]

Beautifully narrated so much respect for you prof mike

[@excelisfun]

Thanks for the kind words, sujith892!!!!

[@HanNguyen-iv5ku]

Thank you very much for sharing your God blessed talent.

[@excelisfun]

You are welcome, Han!!!!

[@rasoulkhoshravan5912]

Hi Mark. I always enjoy your videos on different aspects of Excel. They are wonderful and I am a big fan of you. Now I want to learn Power BI. Your list of relevant videos is so much that I am confused from which one I have to start. I didn't see any introductory notes in this regard. Will be very happy if you could give a clue on how to look in vast amount of your videos, on how or where to start? You are a good teacher and Excel is Fun.

[@kerazop]

This is good advise, I came to this channel for the first time today, and I struggle to understand where to begin.

[@dimmak8206]

Thank you for this ! Im memorizing this whole tutorial to be able to do it without notes :)

[@HopeCastle2000]

Outstanding as always, Mike!

[@chrism9037]

Excellent Mike!

[@excelisfun]

Thanks, Chris M!!!!

[@Globalpeaceseeker]

There is so much info here i feel overwhelemed😂

[@excelisfun]

I put it all in one video so you have one-stop shopping. Be sure to use the Show More button below video to expose the time hyperlinked table of contents so that you can jump to any part you want at any time - for easy referencing.

[@motech7168]

very nice run through!! thank you

[@darrylmorgan]

Boom!Simply Awesome Class Using Power BI...Thank You Mike :)

[@excelisfun]

Power "Boom" BI!!!!!

[@alexrosen8762]

A very good and impressive tutorial. Thank you!

[@wayneedmondson1065]

Awesome Mike! Looking forward to digging into this lesson in detail. As always, thanks for another action packed and information filled video :)) Thumbs up!!

[@excelisfun]

You are welcome, Wayne!!!! I am courious what you think of Power BI Desktop? Do you ever use it? The charts are just so much easier than in Excel... : )

[@wayneedmondson1065]

@@excelisfun Hi Mike. I'm relatively new to Power BI Desktop but love learning new things. I don't currently use it in business.. just watching videos and practicing examples to get the hang of it. I may see some future application for it in my daily business, but at the moment, learning it more as a hobby and for personal enrichment. Pretty much everything I learn in EXCEL pays back sooner or later. So, approaching it from that perspective. Lots to learn for sure.. but having fun doing it :))

[@excelisfun]

@@wayneedmondson1065 I agree: everything I learn in Excel paysback BIG : ) : ) : ) : )

[@rahulrana4608]

Excellent presentation Mike....I saw your this video and was so glad that I watch other videos as well!!!

[@excelisfun]

Do you mean that you are glad that you watched the Excel Data Analysis #0, #1 and #2 before this?

[@excelisfun]

Glad it was EXCELlent for you!!!!

[@galvint2]

I have been using Power Bi like super excel.

[@excelisfun]

Love it: Super Excel!!!!!

[@alndr4u]

Thank you 🙏🏻... Plz make a detailed video on incremental refresh on folders contains csv files.

[@excelisfun]

That requires that you have the correct online license. I don't think it is hard though, just go to the triple dots (...) next to the online data set and set refresh dates : )

[@oybekdavronov5638]

Expecting more PBI business analysis related videos from you !!!

[@excelisfun]

How about next video? Is that soon enough?

[@romangorbi4056]

Why do we need to add the date table? Would the dates not sort themselves as needed from just the available records like in an excel pivot table?

[@excelisfun]

Did you watch the other videos in this class? Date Tables are mandatory for many reasons. I explain it in the previous video: kzbin.info/www/bejne/m3rEfHZsr7GgmJI There is not just one simple reason. But Data Models are totally different than Standard PivotTables. If you watch the whole class, i compare and contrast all the tools like PivotTables, Power Pivot and Power BI. Full 4 video class: kzbin.info/aero/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp

[@alialbayati9368]

Amazing video, thanks Mike.

[@excelisfun]

You are welcome, Ali!!!

[@markosiridzanski2181]

Simply amazing

[@evgeniam8882]

Outstanding video as all of your videos & playlists!! I have a question regarding the date table. In this example is straightforward but what happens if you have multiple columns of dates (e.g Order date, ship date, delivery date etc) ? If you have any video (or a part in your videos) you deal with this matter please let me know. I am trying to find your reference in your other playlists all day long and I didn't manage to locate this issue! Thanks a ton!!!!

[@knikl]

Really enjoying the series, thank you so much Mike. Really appreciate it :)

[@excelisfun]

You are welcome, glad you are enjoying : )

[@johnborg5419]

Amazing!!!!! I wonder if that's enough to describe this video. Thanks Mike : ) : )

[@excelisfun]

Fun too ; )

[@Jorge-ix5up]

Mike, I've learnt a lot from you, you da best!, I'm getting ready to apply for a Data Analyst position coming soon :)

[@excelisfun]

You are going to be awesome as an analysts !!!! #4 in the series, it does Big Data and has some important formulas and visualizations, it comes out next Monday!!!!!

[@excelisfun]

Let me know how the application goes : )

[@Jorge-ix5up]

@@excelisfun Thank you♥

[@einoconsult5563]

Great Video Mike, I watched it a little bit late but I needed some time to be 100% focus as everything you say makes sense. I guess you answered again my question in 39:44 why it is important to take EOMonth column and not Month column :) Daniel.

[@excelisfun]

Yes, filter context in grand total cell for month would have 12 month names rather than 36 month. 40:36 gives a good visual : )

[@unionafrican6094]

If your circumstances force you away from data analysis, and you need to stay on top of your visual review, whether you are cooking, exercising, or something like that, Mr. Mike will be your best partner. In fact, I took the time of cooking to write this comment

[@franciscodiaz836]

13:40 And now we'll use our filter skills from excel LOL

[@excelisfun]

lol : )

[@zt.5677]

Dear Mike. There is a lot to choose from - you have invested a lot of your resources to produce and share these educational videos. Thank you. However, I now have the optopn to complete this Excel Data Analysis Class or the E-DAB series. They both offer the same content. Which one would you recommend?

[@excelisfun]

The four class video seriea is the more recent and has a fast pace. E-DAB is a little loder and has a slower pace. After either. then the advanced MSPTDA is the way to go.

[@zt.5677]

@@excelisfun Dear Mike, OK. Thank you. I shall boldly proceed with the fast track version.

[@SyedMuzammilMahasanShahi]

EXCELlent video. Thanks Mike for this amazing video.

[@excelisfun]

You are welcome Fellow Teacher SMM : )

[@KevinBravo1993]

Thanks again Mike!!!

[@excelisfun]

You are welcome, Kevin!!!!

[@ivanmamchych5802]

Great tutorial! Thumbs up! 👍 Thank you, Mike!

[@excelisfun]

You are welcome, Ivan!!!

[@pollora6233]

Thanks a lot for sharing this amazing tutorial.

[@excelisfun]

You are welcome for the share, Gaston!!!

[@PassportAndPixels]

As a data analysts, are we required to remember all the functions used in M code which are usually applied when we work through the UI (like syntax for adding columns/ selecting rows etc)?

[@nguyenchuong581]

Great Video! Thanks so much Mike.

[@excelisfun]

You are welcome so much, Nguyen!!!

[@nimanitto]

Wonderful, as same as always. you are god. please consider teaching SQL :D

[@excelisfun]

Glad it is wonderful for you. I am not an SQL ninja, although I know how to use it. The problem is that I have too many fun things to teach in Excel and Power BI to ever get to teaching SQL...

[@zayne_ovaa]

Thank you Mike! Lovely lovely video

[@excelisfun]

Glad you like the lovely Power BI, Zainab : )

[@emmafeng55]

Thanks Mike! I know you will be on this hahahah!!!!

[@excelisfun]

Yes!!!!!! hahaha ; )

[@marceljunioregondi5594]

Love the tutorials could you do one off business may be do one utilizing health data like indicators say number of children vaccinated.

[@homepcq703]

I'm not able to see tool tip for the functions as shown in the (23:13) Definition of Date Table....Please guide

[@ThermalWarrior]

Great video Mike, thanks G

[@excelisfun]

You are welcome, Graham!!!

[@henbz]

Simply amazing!

[@excelisfun]

Glad it is amazing for you : )

[@juja2819]

Thank you! :)

[@excelisfun]

You're welcome!

[@Anthony_Lecoq]

Wonderfull Mike. I am always astomished by your videos... always so much to learm ;)

[@excelisfun]

So much to learn and so much fun to have! Glad you like it, anthony!

[@Saafal7]

You are awesome!!!

[@excelisfun]

Glad to help, Safalshr!!!!

[@TheBrownAntiHero]

Hello sir, I am just wondering how valuable these skills are (power query/power bi) in the finance world? I'm planning on becoming a financial analyst and I am wondering if putting in all this effort to learn this is justified. Thank you!

[@excelisfun]

This is less helpful in finance. In Finance we build mostly cash flow, risk and other models using formulas. I have a playlist for this too. It is over 10 years old, but all the functions and formula I teach are still the same. Here is playlist: kzbin.info/aero/PL90E1F26C7B85E78F

[@zuberkhan9582]

Sir if possible make w seperate video of all type of chart why we use when we use how to present

[@kiwikiow]

Excellent. Like the video outro :)

[@excelisfun]

Yes, the Ourtos come from our Teammate Geert. The next video has a new and cool outro too : )

[@mohamedadjal8502]

Thank you very much for the videos that you sent to me on the Internet, I have followed your lessons through the videos for about a year and they were interesting and of a good level, and I also noticed your use of nested functions. I want to ask you a question in Excel: I have numbers that contain different formats, is there a way or A function to get these numbers in the same format when we searched in those cells that contain those numbers. Thank you so much.

[@excelisfun]

Formulas can not see number formatting. The TEXT function can apply a number format to a number and deliver it as text, such as =TEXT(B2,"0.00%") to show 0.12 in cell B2 as the text value 12.00%

[@amramostafa]

Excellent as usual , one question, why you are not using CalendarAuto, it expands automatically when you add new dates to the fact table. Thank you

[@excelisfun]

Yes, you can use that. And I probably should. But it will scan whole data model and might pickup dates from a different table, like employee table with birthdates.

[@amramostafa]

@@excelisfun Got it, thank you

[@jaychowdhury7251]

Hi Mike, sorry this is off topic but need ur help. I am trying to create an AR ageing report with cool graphs and charts like a dashboard. all i c are gud ones made in power bi, but i wanted to make using power query and DAX in excel. these r d columns i have made so far, "company name, invoice number, inv date, inv amt", now if i want to find the number of days its due, i need to put a date in a new column (wich can be any date). then use the formula, "this date - the due date" to give me number of days due. how can i put this in in power query or excel where i can change the date i want and the due days should be calculated accordingly? if i add new data, d due days should be calculated according to the date i have fed in.

[@klee4klee]

Hi, I created a full datamodel on my excel, then I exported the datamodel onto the PowerBI Online. Then, I opened a new excel file to get data from the PowerBI. I was able to run the power pivot from the datamodel, but somehow the original table relationship doesn't work. I'm trying to build a single source of truth, then allow others to use it for their own analysis on their Excel platform as my coworkers are not really good at PowerBI online.

[@SharozKhan-ii7ry]

Would you continue POWER BI further more ....??

[@wmfexcel]

Great video as usual! Thanks for the series. 👍 My two cents: 1) To avoid getting data from hidden worksheets, if any, we may filter "FALSE" under HIDDEN column; 2) SELECTEDVALUE maybe a good alternative to IF(HASONEVALUE... Cheers, MF

[@excelisfun]

I LOVE the SELECTEDVALUE options. I use Power Pivot so often and it is not there, so I forget about it...

[@wmfexcel]

@@excelisfun whenever i need power pivot or DAX, power bi desktop is my preference 😀

[@excelisfun]

@@wmfexcel , Power BI Desktop It is sorta eager than Power Pivot...

[@blackwhite2361]

Hi. In video 2 you said there will be a video number 4. Please when is that video on Big Data dropping? BTW great videos. I am learning a lot

[@excelisfun]

Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas 6 AM Pacific Time. Glad to hear you are learning a lot. See you in the comments, Black White, and always click that thumbs up : )

[@blackwhite2361]

@@excelisfun First thing I do on your videos is to click the thumbs up. You deserve it

[@excelisfun]

@@blackwhite2361 Thanks : ) Go Team!

[@mohamedchakroun4973]

Nice Mike :-) Very informative :-) Do you have any ideas about the % of use of pbi desktop by analyst in the world? And the rank of pbidesktop comparing to the others BI software?

[@excelisfun]

Not sure the %, but Microsoft Power Bi dominates over other software. Check this out: powerbi.microsoft.com/en-us/blog/microsoft-named-a-leader-in-2021-gartner-magic-quadrant-for-analytics-and-bi-platforms/

[@excelisfun]

And, for us Excel people, Power BI Desktop is just too good and free, not to use ; )

[@mohamedchakroun4973]

@@excelisfun Thanks Mike :-)

[@mohamedchakroun4973]

@@excelisfun waw it is far from Tableau :-)

[@excelisfun]

@@mohamedchakroun4973 Yes it is : ) : )

[@rajkirandhyani6029]

In your video at 50 minutes on %ofyear sales total measure. instead of taking Fsale table Can se use Ddate table in Allexcept first argument ----------->> Allexcept(Ddate,Ddate[Year])

[@rajkirandhyani6029]

sir waiting for your reply

[@arturoramirez712]

At the end you copied the excel 2024 file into the folder and pressed refresh in power BI to bring the data into your reports. Yet you still went into power query to refresh the specific query that shows the three data tables to add the fourth table. What happens if you do not do the power query refresh? Thanks

[@excelisfun]

Just click refresh in Power BI user interface. No need to do it in Power Query too. At end, I just want to show the mechanics inside Power Query at the end to prove that thnings update there too : )

[@txreal2]

HELP For Measure Total Sales ($) at 33:22, I couldn't fix this error, even after Google search ERROR: Function RELATED expects a fully qualified column reference as its argument. Total Sales ($) = SUMX (fSales, RELATED (dProduct[Price] * fSales[Units])) I tried one close paranthesis and two close parantheses at the end

[@excelisfun]

You left out the close ) on RELATED.

[@excelisfun]

In RELATED (dProduct[Price] * fSales[Units]) dProduct[Price] * fSales[Units] is not a full qualified column reference, it is multiplication. This will work: RELATED (dProduct[Price] )* fSales[Units]

[@txreal2]

@@excelisfun I was so excited about this video I didn't double check :) Got it now. Thanks for your prompt reply. Your long time subscriber

[@excelisfun]

@@txreal2 Thanks for the long time support on each video : )

[@martinpaulbannister1745]

Hi, studying Excel Data Analysis class number 4 - Settle 911 incident data, regarding the date detail using DAX (discussed on the 30 minute mark), can you please advise how I change fiscal year to suit my own criteria. Financial year for 2021-2022 starts Sunday 4th July 2021. Not sure how to update your code to reflect this requirement. Thank you in advance, Martin

[@excelisfun]

I have no idea how to do that because I do not know where July 1, 2 and 3 should go? Are they a different month? Do they go into June? I have only heard of fiscal years that start on first of month.

[@examknowledge7899]

Sir.....how can I get certificate so I can apply for the job I have heard without certificate no will give you job ???

[@mohamedgouda3170]

Auto complete for Power Query Functions (Tab btn or Enter btn) is now working

[@excelisfun]

: ) : )

[@simfinso858]

Power BI is a Combination of Power point + Excel with some advance features this is what I feel ....

[@excelisfun]

Cool! I am so glad that you are taking this class, Most Awesome Excel Finance Guy!!!!!

[@simfinso858]

@@excelisfun Thanks.I saw all 4 videos as soon as posted but I just forget to Comment .I hardly miss Your video because the Accuracy level of your videos are highest among all others,full of knowledge with very basic concept clarification Each time.Awesome channel as always.

[@excelisfun]

@@simfinso858 Thank you for your kind words and attension to come back and comment on each video : ) SIMFINSO!!!! I will continue to try and make videos to help our Team!!!!! Go Team : )

[@simfinso858]

@@excelisfun I will also post 20-25 videos very soon on my channel too. My style is 1) I make shorter video ( about 5 minutes Duration) 2) I post all of a sudden videos (in 20-25 numbers ) 3) then I keep quite for few months meanwhile I do my other outdoor work but You post Consistently.That's appreciable. Thanks for writing Good words for me.🙏🙏🙏

[@excelisfun]

@@simfinso858 Go Team!!!!

[@userme2803]

Thanks for this video, I want to learn Power BI, do you think Power BI Desktop free version is enough to learn everything or most of Power BI? Thank you again.

[@excelisfun]

Yes, Power BI Online does not even have all data modeling tools. It does not even have a full Power Query. So ALL of data modeling, Power Query, DAX and reports creation can be learned in Power BI Desktop. Power BI Online is for each sharing and automatuic updating. That part of it you can not learn in Power BI Desktop. But 100 % of the model and report and visualtion creation can be learned in Power BI Desktop.

[@userme2803]

@@excelisfun Thank, yes Excel is fun but your videos are the real fun. Thank for all your videos. very long time customer here lol

[@excelisfun]

@@userme2803 Thanks for your long time support : )

[@Swizzletwice]

Hi, I was able to successfully download the workbook file; however, the link to the Report file doesn't work for me. It opens to an html Wordpress page of jumbled data. I completed Class 3 (great class!) without it, but I guess I need it in class 4 for the vba code. Is there another way to download the file? Thanks, and thanks for your excellent teaching skills.

[@excelisfun]

Try: right-click link, save as. No VBA in next class, just DAX and M Code.

[@Swizzletwice]

@@excelisfun Hi, thank you! I had to left click the Report link to open the Wordpress page, then right click inside the jumble to select Save As. That worked. Apologies, I confused vba code with DAX code. In Class 3, a date table was created using a long DAX formula, and that same formula is copied and used in Class 4 at the 8:50 mark. Thank you again!

[@minhanhhua4219]

I like it

[@excelisfun]

Glad you like it!

[@bzflowerbee]

Would the process the same if I have all my files in One Drive?

[@excelisfun]

It should be.

[@bzflowerbee]

@@excelisfun Thank you very much 😊

[@youssoufsougueh396]

Thanks

[@excelisfun]

You are welcome!

[@hishamoo222]

Hello sir im just trying to be a data analyst and i want to start with excel and power bi what Playlist do you recommend in your channel

[@excelisfun]

Playlist #1: Full Excel Data Analysis Basics Class (E-DAB). Data Analysis & BI Made Easy with Excel Power Tools (10 Videos): kzbin.info/aero/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT Playlist #2: Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel (30+ Videos) kzbin.info/aero/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1 Playlist #3: Summary 4 video class: Excel Data Analysis Short 4 Video Class: PivotTables, Formulas, Power Query, Power Pivot & Power BI kzbin.info/aero/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp Free Data Analysis Education has never been better : ) See you in the comments, Hisham, and always click that thumbs up : )

[@hishamoo222]

@@excelisfun thanks you're the best

[@mayurrajude]

Please Make Videos For VBA

[@excelisfun]

I am not good with VBA, so I don't make those videos : ( But, you are subscribed to: "Dinesh Kumar Takyar". He has great VBA videos : )

[@mayurrajude]

@@excelisfun ty..sir

[@excelisfun]

@@mayurrajude I don't know what that means : (

[@mayurrajude]

@@excelisfun i am not wrote this symbols : (

[@z3r0-by-tuj]

when you are being livestream, i can't watch, because KZbin notifies "Video Unavailable, this video is restricted. Please check the google workspace administrator........" how to fix this error for your next livestream. Thank you

[@excelisfun]

I am not sure how to fix it : (

[@z3r0-by-tuj]

@@excelisfun thank for your relying, I'll try "google" ^^

[@kantimakwana5274]

Please try to provide this data entry in Gujarati or in Hindi Kanti Makwana

[@Ahmad_Askar]

I could use an advice to start recording videos

[@milux616]

🙌🙌🙌

[@excelisfun]

Cool, Milu!!!

[@irfandaud7380]

dDateGenerateAndRow = VAR DateColumn = CALENDAR(DATE(YEAR(MIN(fSales[Date])),1,1),DATE(YEAR(MAX(fSales[Date])),12,31)) RETURN GENERATE( DateColumn, VAR BaseDate = [Date] VAR MonthNumber = MONTH(BaseDate) VAR MonthName = FORMAT(BaseDate,"mmm") VAR YearStandard = YEAR(BaseDate) VAR EOMonthDate = EOMONTH(BaseDate,0) VAR StandardQuarter = ROUNDUP(MonthNumber/3,0) // July 1 is first day in Fiscal Period VAR FiscalQuarter = "Q-"& IF(StandardQuarter=1,3,IF(StandardQuarter=2,4,IF(StandardQuarter=3,1,2))) // if April 1 is first day in Fiscal Period IF(StandardQuarter=1,4,StandardQuarter-1) VAR FiscalYear = IF(OR(StandardQuarter=1,StandardQuarter=2),YearStandard-1,YearStandard) // if April 1 is first day in Fiscal PeriodIF(StandardQuarter=1,YearStandard-1,YearStandard) VAR FiscalPeriod = FiscalYear&" "&FiscalQuarter RETURN ROW( "Month Number", MonthNumber, "Month", MonthName, "Year", YearStandard, "EOMonth", EOMonthDate, "Quarter", StandardQuarter, "Fiscal Quarter", FiscalQuarter, "Fiscal Year", FiscalYear, "Fiscal Period", FiscalPeriod )

[@edge6192]

hey mike ,,kindly respond to my previous comment/query. pretty please