Connor cato um she's done the power bi series that we're really excited with and uh her sharepoint power user video will be premiering uh this friday it's the site owner site user video series it's gonna go a little bit more in depth than the sharepoint basics video we had on but that's who we have here today joining us and uh trish is gonna be also doing a quick power bi demo for us as well um but i wanna thank trish for uh coming on the show today and hey trish you wanna maybe uh just do a quick introduction of what you.
You know let the people know what you're all about i'm trish i'm all about massaging my brain through technology i'm passionate about power bi excel i mean a good saturday night for me is playing around in power bi and discovering new things to do so i'm glad to be here thanks for the invitation ben yeah no thank you for being on the show i i definitely wanted to uh have people we like to see you know people see who's.
Actually teaching the class and everything like that always try to get everybody on eventually on one day or another um just gonna throw some quick updates out to you guys as well as far as the channel is concerned uh there this is the big one this is actually a major one we are gonna be starting to do uh youtube shorts it's probably gonna be about a month or two before you start seeing them pop up on our channel but you're gonna start seeing those quick one-minute tips we're gonna go through probably gonna go through every single function in excel powerpoint word and whatnot um so you'll start seeing those pop up on our channel uh and those will.
Also pop up across all of other social media outlets as well and again that's coming in a little bit of time uh but this and i've already alluded already told you guys that we have a trisha sharepoint uh site users our power user video series uh premiering this friday um i think it's a two or three part series i can't remember exactly off my head but um that'll be the next next couple of fridays and then the other big announcement i think i did this last week as well is that the office 2021 videos that i've been mentioning.
Probably every single show are going to be finally rolling out on june 3rd we have that all set we're going to have a big push for that so it's going to start with windows 11 and then we're going to just roll into excel powerpoint word and then that'll be most of the summer will be the office 2021 uh programs so that's what's coming up on the channel and everything you can expect so again the big one is the youtube shorts if you guys want to drop in you know like what uh programs you want us to cover in the shorts we'll definitely make sure to uh.
Anything microsoft trish really does know it all guys uh vba especially you've written a book on vba if i'm not mistaken correct yeah i've written um power bi vba a few others i'm working on i might actually do a sql server reporting services book i've written the book for the video so i might as well yeah yeah guys that's the other the sql server reporting video that i've been talking about i've tried to have a lot of people make that video and they it.
It just kept not happening but i know trish is currently working on it we were just talking about it before the show went on but uh that's gonna be i think it's gonna be about four or five videos i'm not mistaken right or even six maybe yeah it's a it's a huge one yeah so it's gonna be about it's gonna be a huge series guys you're gonna know everything about doing sql queries and everything like that and sql reporting services you're going to get it in-depth knowledge and i know it's it's stuff that's probably not out there anymore because i know microsoft's making a big push for everyone to get into the azure.
System and everything like that but there's still a ton of you guys using sql server i mean especially sql server 2019 even i think 2016 is the one that they're just ending support for uh i think at the end of this year they're going to stop supporting 2016 but i know there's still tons of people doing the 2019 stuff and so you know we're you know you guys asked for it so we're delivering it and then yes also in the comments and like that also let us know any software you want us to cover or anything else you want us to uh touch on or anything.
Like that but without further ado trish do you want to jump right into the power bi demo absolutely so i already have power bi open and instead of using excel workbook or getting data i have a file that has uh power pivot sheets in it so i have to import it so i'm gonna go to the file and import and then you'll see power query power pivot power view.
And i'm gonna don't save changes to this desktop and i just have an excel file that i'm gonna use and just start the import so i i prepared a quick demo showing you how you can create a map in as a report in power bi so all of my data is now in here and i can see on the fields list on the right that it's showing all the tables and.
Then i have for each table i have all of the fields
That i can display so the customers table is the one that has location data in it and so that's the table that i'm going to use to create this map so i'm not going to use my filters pane so i can collapse that get some more screen space i am in report view so what i need to do is i just need to select the visualization that i want to.Use and i'm going to be using arcgis maps for power bi so i'm going to just select that and i'm going to expand the framework of it so that when i do populate it it will be able to display the map so you can see it really well so i'm going to just make this big so you don't have to have an arcgis account if you do have one you have more flexibility without having an account you can only add one arcgis layer to a.
Map for this demo that would be fine so i'm not going to sign in it gives you a choice sign in or just start building your map so now when i look in the visualizations pane i see all the fields that i need to build the map now when it comes to location you have to use either the location field by itself or you have to use latitude and longitude together my data doesn't have latitude and longitude so for location i'm going to just use the state field.
And just drag it and drop it in there i know i can do this so i have my state field in there and then i have and you see it's already starting to create the map then the size field is really like what data do i want to show for every state so what i'm going to choose is the last year's sales field for size and then what i'm going to do is.
Even if you're not using a field itself in the map you can have that field show up as a tool tip in the map so i'm going to use city as my tool tip field and so i have my basic map it's still rendering a little bit and so each bubble represents the value of last year's sales that you're seeing.
On the map but it gets better because it's an arcgis map i have this map tools panel on the left hand side and when i expand it this is how i can add you're allowed to have two infographic cards now that's separate than the arcgis layers but they're good to have on the map because they show a lot of great information so i'm going to click the wrench and the first layer is the infographics.
Layer and i have to wait for it to load so i have up to two infographics cards that i can add i'm going to do a population one so i'm going to just open population and then population variables and i'm going to select 2021 total population oops second yeah it would help if i clicked it and so it's gonna show up on the upper right hand corner i mean it can be moved but it's called an infographic card.
And it's showing me the 2021 total population i can see it either numerically or as a percentage percentage doesn't make sense here because it's going to come to a hundred percent so i'm going to leave it on the number and then i'm going to go back and go to the households category and expand that and i'm going to put in see which one do i want i'll do the 2021 total households.
So now i have two infographics cards which will change as i interact with the map so right now each of them is showing the for the entire united states but watch what happens if i click on i really do know how to use a mouse if i click on a state let me get in there a little bit better and you can see they're recalculating.
I can actually get the state selected so now the number is different it will
Still say united states but it lets you know that you have something selected so it's showing the totals for california right there just interesting information to have on a map now i'm going to add what's called in my one arc gis layer so i went back to the wrench the second one down is called reference layer and that's where i can get the arcgis layers.Now i can search for a bunch of things i can do a search for population and anything that they have regarding population comes up i don't i'm not interested in you know juvenile justice right now for this map or child mortality some of the things are just like okay we're not going to do that on a thursday um so i'm going to do some demographics ones instead because i don't like the choices that came up and again i don't have an account so.
I'm limited in what i can grab from there but i'm gonna do another um one i'm gonna go to which one is this let me hover over it i'll just go to the 2020 usa average household income and now you'll notice on the map i can close this on the map you'll notice that you now have this green layer right and that is that map layer that.
Was just added so i'm going to zoom in on the map and show you how cool this can be and i can actually move it up a little bit here let it populate some more and so go back over here i'm going to click on california again and this time if you look at what pops up it's showing the state the count of last year's sales and since i selected city as a tool tip it's showing which city and i can zoom to.
That location and once i do that if i hover on the edge of like california let me do that again it's kind of hard to do sometimes because it keeps me on me give me one second here so i'm going to click and it gives me a box that has navigational arrows so right now i'm looking at.
I was doing a count of last year's sales so what i'm going to do is i'm going to come over here i want it to be the sum and i can change that in the visualizations pane and i got to wait for it to render again so now i'm looking at inyo county california gives me the abbreviation and it's given me all this other information right there's my household incomes and everything and then when i do the right.
Pointing arrow it takes me to another county and this one you know the same type of information am i at five minutes ben i lost total track of time because i will just keep going until 3 a.m you can keep going okay yeah so that's just an example you know of a quick map i mean there's actually you know a lot more stuff that you can do with a map but i like showing people the.
Map report because a lot of people just don't realize that they can do something like this in power bi they think about just their regular data and not visualizing it in this way so i like to show people that yeah you have the ability to have arcgis maps in power bi now it really does depend on your administrator if your administrator doesn't enable it for you to have you can't get it in here so if you really are interested.
In this you can speak to your administrator to see if they can you know enable it for you otherwise you have just a plain map that you can use that doesn't have any of the layers or the infographics cards and that one comes by default with power bi so i don't know what else to say about the map at this point it's no problem i i know we i did see some i did see a question from uh april and then also uh dara has one uh as well.
Um april's question for whatever reason uh came through on the regular one so i'm just gonna pull it in here just so i can highlight it um i'm gonna go ahead and pull it up all right i'm gonna show it on the stream here just for a second um this is from april though um i hope i said your name right i'm the worst person at saying names but uh what is the difference between the power bi and excel dashboard okay so power bi is connected to the power bi service which is online.
And so everything that i do this is power bi desktop everything that i do in power bi desktop i can put up into the service and so i can create dashboards that i can share um with other people that have power bi service right they can get my dashboards i can share the data sets underneath the dashboard so typically someone who is like creating all the reports and building the reports they're going to do most of that work in power.
Bi desktop you can also do it in the service the service is totally in the cloud so everything is being shared in the cloud it even has a microsoft teams connector and all of that and the business intelligence is much more robust than it is in excel in terms of data analytics so there's a huge difference when you're creating an ex a dashboard in excel um you i mean there are ways that you.
Can share it with people but it's not as integrated as all of the other power bi tools are and hopefully that answers your question so yeah hopefully april we got your uh question answered there um we're gonna jump into dara's question i hope i'm saying that right as well um i want to know the calculation and dax more if you show us if you get some tips thank you so any uh tips for uh calculations and dax oh wow um okay so very similar to doing formulas.
In excel but much different as well okay um tips for doing dax well let me do something here really quick so i can kind of get into the dax calculation screen let's see i think this might be a calculated field let me just see another i'm going to go to a different um table in here yeah i'm going to go to a different table and i'm going to just do like a basic stacked bar chart real quick.
And i'm going to do i'll put in order id and product let me grab this one and then for my values i'll put the sum of total cost something like that just to make a quick charge.
And i would have to use filters on a chart like this because it has so much data you know the chart is unreadable so i can i would use a filter on that but i just want to get to where you could like create a dax calculation right so um i'm going to do you could do a new measure new column new quick measure i'm going to do a new quick measure just for expedience it will do the calculation and then we'll be able to look at it so i'm going to do something.
Like uh average per category and my base value i've got to open the same table again and open order details there's no easy answer without me doing it this way sorry about that so i'm going to do yeah i'm gonna do the sum of total cost here for my base value and for my category field i'm gonna do product.
So something like that and so it creates so now i have another it just popped up another sum of total cost calculation and i want to get to so now you can see the dax calculation over here that was created by me using um the quick measure so it's up in like a formula bar here um the best way to learn dax and if i have a moment i'll find a website and and i.
Don't know um then if you can post it for everybody but there's a great microsoft document resource on dax calculations where you can look at all of the different functions what they're used for the syntax for each of them the best way to get comfortable with dax is just keep using it it's strange at first because you have to do it differently than you're used to doing your functions in excel and that's what's off putting for a lot of people.
But once you get used to it and there are some dax functions that i have to look at the resource guide to remember how to really do it and i get confused and stuff like that but they're these functions it's like a different language than the functions in excel so they're not compatible let me see if i can find that link and um then we can maybe get it up for them or something then yeah yeah sure thing.
Thanks for that question dara and um i hope we uh got that uh answered for you so keep practicing it's just is this like uh any like just like vba you got to keep practicing it to you know get all the functions and functionality down for it and everything like that just like python too you know you gotta just keep coding away and pumping at it um let's see just go down all right so i don't know if you guys had any more questions for trish she's i know we have about ten about a couple not ten minutes but just a few more minutes left here um it flies by every.
Time um but i'm looking for that resource um you guys have any more questions you can drop them in the chat um and we'll try to get it answered real quick i know we're running a little bit short on time but um we'll definitely uh get to as many as we can um i totally forgot though about the coffee mugs trish i talked about it just before we oh i was wondering when that was gonna happen i tried to do it in the beginning and again guys you know the coffee mug is one of the few ways if you guys are working in office i i work remotely from.
Home all the time so it's not the case for me but it was the one thing that i could show individual flair at the office um i do have i've had this mug on a few times but i just want to bring up again it is my final fantasy tactics mug um there's another channel run by uh jace seven if you guys are interested in knowing what this is about i recommend checking his channel out um but it's a great little mug that i got as a gift at the office but it's definitely one of my favorite games from a kid as a kid final fantasy x i say kid but i'm still playing it today so [Laughter].
Earth link oh yeah this is a real throwback for you guys just for showing me her uh old earthling modem i think she has it yeah there it is [Laughter] how to get internet [Laughter] at the dawn of the internet so to speak for when it was really reaching out to a two-year you know everyday people and like everyday consumers we had the old modem where you had to plug the phone line and you had to dial up to connect to the internet and make that.
Oh my goodness and you just hope nobody else in your household would pick up the extension on the phone right when it was connecting because then you'd have to start all over again it was like using an abacus like it was so i feel so old when i tell people like i've been working on computers since 1982 and when they took up whole rooms and floppy disks were the size of record albums and most people don't even know what those are anymore right so oh yeah it was a different different era.
I grew oh yeah here we go trish has queen on her coffee mug any story behind that one or my daughter-in-law gifted me with this oh nice so it was extra special coming from her oh nice nice yeah and so yeah that's just your one piece of player oh yeah guys i forgot to mention too we will be selling gothy mugs pretty soon on the channel too we're getting that set up as well um there's a lot of stuff coming out soon guys from our channel obviously like i said we have sharepoint uh being done the video is premiering.
This friday at 9 30 a.m as always and that's going to be done by trish who's joined us today um and then like i said that's going to be the next few fridays i don't like to you know go too much beyond that as far as what we got scheduled just because we switch it up all the time just based on what comes in and what's what's hot and what's happening but also let us know what you guys want to learn i mean that's how we create these videos and how we get to most of our topics is you know you guys drop it in the comments here you drop it in the chat you know wherever um we i go through every single comment that's on our channel i mean i.
Answer every single one i will get back to you guys one way or another i do that every morning that's how i start my day but that's how you know we start to you know figure out what topics are relevant for you guys what's relevant at your workplace you know what you what skills do you need to learn to uh you know to excel at work and um that when i when i see those that starts getting added to our production list and then i start looking for people to create those videos luckily we have somebody like trish who's been um with microsoft since for the longest time and.
Trish really i like guys i'm not kidding she knows like every single software program you know end user program that you can use in microsoft trish is an expert at um i know you like uh planner and uh project and vizio i think you were telling me that you mapped out your whole move with vizio or planner i can't remember exactly what microsoft project project okay yes i i use microsoft project when i'm um have any kind of project even if it's like around the house i i redid my guest bedroom.