Power BI Zero to Dashboard in an Hour

you are you guys seeing a big blue screen right now yeah we do okay great now let me just get my powerpoint to lunch and that's the RBI that's good it's our point is having a moment so we're just having all kinds of technical issues tonight that's okay we have patience love see I think you're out east you guys it's you know it's much later than it is here so you might actually be even listening with a tasty adult beverage right which is which of course is a great way to do training all right so let's see so our unit seeing my PowerPoint slide yep yay thank you okay to everyone on the call thank you so much for your patience my first time using this particular meeting software my name is Barbara Rainey as our host so graciously introduced me and I'm super super happy to be here so we're going to spend the rest of our time talking about power bi and this wonderful tool that Microsoft has given us and learn basically soup-to-nuts how we can build a report how we can build a dashboard and then a few other topics along the way so as we get started here we're going to start off this is going to be the finished report that we'll build during today's session and as you can see it has a very different color scheme than normal power bi report and that you may have seen before and that's one of the things we're going to talk about today there's um we're going to bring in data from sequel and also from excel and we're going to edit queries and manipulate our data and get it all figured out and set for our data model then we will bring it together into the data model allow the tables have relationships will write some dacks which is the data analysis expression language which allows us to do some really complex calculations we'll do some visualizations we'll talk a bit about security and then we'll talk about different ways of publishing our data power bi is is an amazing tool for the business end user as Sarah mentioned as Ike I spent a good amount of my career over 25 years in Microsoft Excel doing desktop reporting so a user client-side reporting and power bi allows us an easy way to access a lot of different data sources the connectors are up over 70 at the moment bring the data into our data model clean it up so maybe it needs to be pivoted or maybe it needs to be filled or there's calculations that need to be can in order to perfect the data and then you can match it up with data from other sources and then explore and visualize that data and then finally publish it up to the service where you can share you can set up manage data refresh and then as you determine that you need more you go back and start the process again so it's kind of a cyclical process so the query engine of power bi desktop is where you can prepare your data and as I mentioned you can get your data from a broad variety of sources both on-premise and cloud based sources and the UI is ever-expanding with all kinds of different transformations that you can do and then once you've loaded it into the data model you can add more calculations to your field so that you can develop some really deep analytics the explore exploration really comes with the visualizations we can use real standard visualizations bar charts column charts we can get into some really amazing geospatial pipe data by using the built-in geographical analytics but also there is quite a few different things like the arc is geospatial visualizations that are also available once we actually build our data in the service you actually can use what we call quick insights to find information about your data and also ask questions about your data using natural language Q&A that will be one of the later things that we do in the day visualize it's interesting because with power bi first came out two and a half years ago the visualizations were really limited but there was this unicorn that they talked about which was the ability for people to build their own custom visuals so we'll show you how you can actually import and use a custom visual in your power bi file today and I'll show you where that library lives and how you can integrate and enhance your data model with with more custom visuals and then finally the whole share and collaborate so the power bi service is where we actually put our data models on in the cloud and it's app power bi comm you can access your reports and dashboards from any windows iOS or Android device which is kind of amazing at one of my plans out at Microsoft right now our VP has a dashboard and we kept it really simple his final dashboard has six tiles it's six KPIs key performance indicators that he needs to mock he needs to monitor and he loves it because he opened it up on his phone takes a quick glance everything looks right he's done and he basically checked his KPI for the day and so that ability to get your data anywhere is pretty amazing and then also when we get to actually showing you how the service works you can actually combine data from multiple reports up into one single dashboard to get that executive summary of the world and then also you can leverage the beauty at office 365 groups and workspaces for both and security and also arts authoring within the service so we're going to start off talking about power bi desktop so power bi desktop is a 100% free yes I said free zero cost zero dollars which is kind of amazing to me there's a free download so anyone pretty much worldwide because it has been localized I believe into all of the languages that office is localized into there is a power bi desktop version and at the moment last time I looked there were 77 different data sources that you can connect to I have a few icons here on the left that are some representative samples of things that you can get data from I picked MailChimp here just because I think he's a cute logo so but that's not where people probably report on very often but you can have cloud services you can have as your services you can add on-premise sequel data in SharePoint analysis services Excel etc and all of those data connections get made in the query editor so we're going to get data and we're going to bring it into our data model in queries and in this query editors also where we're going to be able to do some amazing transformations on the data and then once we finish with the transformations and we have the queries all set the way we want so that each query becomes a table in our data model we are going to load that data into the data model and power bi has the ability to auto detect the relationships among the tables in our data model with the exception of one the date which we'll talk about in a little bit but they don't make assumptions on your date relationships because those are pretty critical to your data model and then you also have the ability to view your data right within the data view area and then you have to report canvas where you can visualize all of your data and and create all of the different courts that you're going to create within the file all of these views are all stored inside one file the extension on the file is about PDI X kind of like an XLS X is an excel file a PV IX is a power bi desktop file so now we're going to jump right out to power bi desktop and start showing you how we're going to go ahead and build our report so first off and we've got our power bi desktop file open if you're brand new you're going to see a splash screen that looks something like this when you first come in I've already signed into my session so it already knows me this view also can alter some times and it will have a sign in in the center and basically what's happening is it's asking you to sign in using your office 365 or your UPN credential and it usually looks a lot like an email address and then basically that allows power bi desktop to know what ID you're going to be using when you publish up to the service on this splash screen is actually a really nice screen just to have around anyway I always tell people I'm you know you can go ahead and click the button and it will always show when we start up and you can get straight to videos here about power bi and learn more about desktop the what new click is an awesome URL to know it takes you out to the power bi blog because every month once a month the power bi desktop product gets a new I owe my screen did not change is not changing oh wait hold on just a second I'm just making sure that we are getting the right view here looks so bright it okay great and so anyway the power bi desktop gets a overhaul every month once a month and you get new features and so Amanda from the product team very nice lady it does a great video every month that tells you all of the cool things that are in this month's feature release and I and you can work with those features so when you are working with power bi one of the things I like to tell my students is if you go into file then health then about you can see which version of the file which fit your team with so I'm working with the may 2017 it just came out last week and it has lots of cool stuff in there but that's an easy way to tell whether or not you need to upgrade your power bi desktop to the newer version all right so here we are blank screen white canvas ready for us to create a data model so the first thing we're going to do is get data so Kay there's the first thing we're going to do I'm going to go ahead and get data from sequel server and we're going to go ahead and just use my localhost and at this point I don't even need to specify my server name or anything it will go ahead and navigate me to the different databases that my server has so we're going to go ahead and go into my FBI database and it's actually fun books incorporate it or something like that I can't remember exactly we just like the FBI acronym and as you can see I've got a number of different tables that are available to me in that database and as I select each one I get a preview of what the data in that table looks like and as I select them I'm going to end up getting a query that is created one for each table within the sequel server database that I'm that I'm bringing in now the next thing I do is actually pretty important if I click load that means I'm done I don't need to transform this data at all I just want to come straight back into modeling and actually start building visualizations but I actually need to do some editing to my data and doing some manipulations to it so I'm going to click the edit and that's going to bring me into the query window and so now you'll notice that I've got one query on the left hand side of the screen for each table that I connected to in my data model alright so the first thing that we're going to do is we're going to walk through and kind of look at each table and my campaign IV came through as a number so that's great my traffic channel came through as text and how I can tell that is by looking at the data types on each column and they each have their own little symbol and I couldn't like that from a UI perspective because each of the symbols is different enough that as you learn them you don't really have to click and look at the data type up here you can just look at the actual icon that they've got and know exactly what data type things are are showing up at all right in my product a let's see in my geography table my zip codes came through as text which is good because East Coast if codes have got a leading 0 in them so it's kind of important that they come through as text so that I don't lose my meeting I don't lose my leading zeros also those are in my customer dimension as well and they came through properly as text so that's good we're doing well alright so now in the product table we're going to go over there and kind of look around we've got a product ID we've got a product category a product name the unit price and a unit cost now it's important as you go through my data typing is actually coming through from my sequel server database because I have my columns appropriately typed in sequel they actually came through my load process appropriately but if you needed to change a data type for instance if I wanted to change my ID from a decimal number to a fixed decimal it's as easy as just clicking the little button and changing that datatype all right so now we're going to do our first transformation so in my email column I've actually got two pieces of information I've got the email address and then I also have the last name first in my email so we're going to go ahead and parse that out now there is a new way of parsing data on the transform ribbon and we can let's see where did it go my split so we're going to split by a delimiter here and our delimiter is actually going to be a colon and then equals so we can put in a multi character delimiter in in this column so I'm going to go ahead and click on OK and then I need to replace my parentheses here so we're going to go ahead and do our place values we're going to get rid of the left parenthesis and replace it with nothing to get rid of it and we're going to rinse and repeat with the right one and place it with nothing so now we've got our email name and it's all set up then we're going to go ahead and do another split to split our last name first column and we're going to just split that using a comma the only problem with that is it left a trailing space in our first name column so for that we can use a transform and do a trim and now our first name and our last name are properly put together in their own columns now up until about two months ago if we wanted to combine my last name and my first name into a new column we would go into our add column window and create a custom column and we could do a full name I'm going to put it the old way and that by saying our first name is actually email email name 2.2 ampersand space ampersand and then email name 12.1 right all those splits put all those twos and ones on there and now we end up with a full name that's all fine and good but we can do it that way and that's still 100% legal but there's another way that they've just added into the product that's actually really cool so I'm going to remove this step you'll notice over here on the right hand side as I've been doing things like replacing the values and splitting and changing ties and trimming that my applied step has been recording my every minute you'll notice that you can also see the code that's been generated by the system in my formula bar if you open up power bi desktop and you go into query and don't see a formula bar very easy to turn on from we can't have it highlighted when you try to turn it up and on you can toggle it off and on right here in the formula bar in the view ribbon and so you can see the M code this is actually the language called M or power query that is being generated by each of the UI steps so I'm going to delete that step and remove that added column because I'm going to show you another way that we can add this custom column and we can add it from example so if I click on add by example all I need to type here is lauren space clue and the system looks at the other columns and says oh i bet what you are doing is you're combining if you see up here in the formula bar you're combining email name to a space and email name one and i'll just click okay and now you've added a custom column the syntax is just a little bit different than the syntax i had originally created but the system did it for me so the query engine is getting really really smart and helping you so that you don't have to a full-blown coder and their question came in and it says can I have a link to Amanda's page yes I can send out I can get you guys a link at a page of resourcing after the call and I will provide them to Sara and I'm assuming she can then get it out to you and then also just power D I have a waterfall chart as a matter of fact it does so I'll show you all the different visualization types in just a few minutes so a couple of the questions I came in all right so now let's clean this up because we've got some really strange column names now so we'll go ahead and just do a quick rename rename us to email we'll rename our first name column now it's interesting is I just sort of renamed and it created a step called rename column and if you look in the formula bar it said it renamed my email name one to email and when I hit enter here it's just going to add to that step my second rename and then when I do the next one is going to keep adding so now this is my last name step and then finally now I have my full name so I can do some pretty amazing text transformations there's all kinds of different transformations that I can do and just that quickly I have now split my email name column and this is what life was like when I first got here was right here at navigation and now my finished product has my columns all done and what's really amazing about that is when it refreshes the data from its original source it will reprocess all of these steps again so if you've got a table that's got data but it's not quite in the right shape it's not exactly safe the way you need it to be it can read eat get reprocessed quite simply alright let's head on down so now we're going to go down to the campaign table and I'm going to add two kind of strange columns but you'll see later why I'm adding them I'm going to add a column that allows me to put in a URL so I'm just going to use a really simple URL and they're all going to be the same in this case I'm just going to put in bing.com but I need to do that in double quotes since I'm basically adding it in as a text string at this point and you'll see later how I actually use that and I'm going to add another custom column and we're going to call this a male 2 and we're going to do the male 2 also needs to be in double quotes male 2 pull in and we'll just do my email address Barbara are at Swan calm but that in double quotes and I'll show you how we use this one a little bit later we can do some neat things with URLs all right so then the next thing we're going to do is we're going to build up a date table so my sequel I only have literally just the dates and they came in as date times and I really want them to be just days so I'm going to go ahead and format that particular column so it's a date and then I have the ability if I've got a date column highlighted to go in and add a column that is my year so now it just did all the function for me to take this date and add my year I can go in and I can add my name of the month right I can go in and add my month end date and any other I've got year month quarter weekend day and then also I can also do functions on my day like subtracting combining data and time age so I can do age calculation which basically would be how old is a dates from today and so there's a lot of really cool transformations that you can do right in your date table all right so now alright so there are a couple questions that I'm going to go ahead and pause the questions that just came in in the URL window for a little bit because some of the things I'm going to get to a little bit later in the top so we're going to go and mash up data now so we need to go and we're going to do a new source and we're going to get data from excel they're actually from a CSV so I'm going to go ahead and connect and I'm going to go and get my budget data now my budget data is in a bit of a funky view because that's all why'd all my columns go out to the right so I've got my I've got my product categories and then my first column is but my first row is budget my second row says the month my third row says the date and then I've got all my data so I'm going to go ahead and bring that in but now I need to actually fix it so now the first thing that we're going to do is I'm just going to flip it on its side I'm just going to do a transpose because query likes to work in columns it doesn't like to work in rows so now I'm going to add a new custom column and I can either do it as a from example or I can do it using my custom code so I'm going to go ahead and use that by example and and I'm going to scoot down to Row 2 because that's where my example really lives and I'm going to put in budget and a concatenation Tildy and then January 201 3 and that's how I'm going to put those together the system recognize that as a txt combined of column 1 Attili and then column two and column 3 which is exactly what I want so I'm going to go ahead and click on it okay now this is going to be my new header column so I'm going to go ahead and move it all the way over to the beginning and then I'm going to transpose everything back what that's going to allow me to do now is use this brand new header that I just built I'm going to go ahead and use that as my new header row now the ones that I use to create it the first place I no longer need so we're going to remove our top three rows and now I've got my data I've got my header row I've got my product categories here and now I'm ready to actually now I need to go get I need to go get product category but I want it to be an ID so I need it to be a special little table so I'm going to go make a product category dimension so how I'm going to do that is I'm going to take my product category it's my private category ctrl-click two columns and actually I need to make a duplicate first huh so I'm going to take my product table and I'm going to isolate just the product category there we go that's where I'm meant to go and I'm going to remove all the other columns so I just have my category ID and my categories and then I'm going to go ahead and remove the duplicates from this table this is effectively creating a dimension or an attribute table just for my categories categories I'm having trouble typing there we go get that spelled right RI es there we go I knew there was a problem with my spelling so now I'm going to go back to the budget and I'm going to use this table because you'll notice that my categories I have children health history sports and travel those are the same ones I have here but I want to apply those categories to this table so I'm going to go ahead and merge in my categories table so I can merge on one column which is effectively a sequel join where I can merge in on two or three or four or five however many columns I need to merge on in order to get the appropriate connection I can merge on multiple columns and then basically that was like doing the join on in sequel and then the split here is selecting what column from that other table I want to bring in so now I've got my new category which I'm going to move to the beginning we're going to take this category out and that always feels a little weird to people because they're like wait I just used the category to get the category ad so I'm removing it so when i refresh is it going to work right and the answer is yes because all of the queries were done in the right order so that the next time the data is refreshed it's going to process through those same exact steps again so now the last step we're going to do a unfitted other columns so now I've got my category D my attribute column and my value so I'm going to split my budget away from my month oops get me the sort I meant to split so go ahead and I'm going to split by my fancy custom delimiter and the system is actually getting really smart you'll notice I didn't pick the delimiter the system figured out that the only logical thing I would be using for a delimiter would be the still D so it's set it to custom for T so there's a lot that this says the power query can do for you to make your life a lot easier alright so now we're going to go ahead and rename this column to Samaria I'm going to rename this column should be nine months and then I'm going to go ahead and rename this to do my budget amount and then finally you'll notice that there is and it might be kind of hard to see on the screen but there is a datatype here called abc123 and if I highlight that column you'll notice up here in the data type the data type is showing as any and any is just not a good data type any causes problems because the system space will be going I don't know what kind of data type it is it could be anything right so anytime in your queries you see in any data type you're going to want to strongly to whatever it should be in our case this should be a successful number because it is in fact currency you'll notice that the commas and the dollar signs aren't showing up here and that's because what we're actually doing is typing our data we're not actually formatting it at this point we're going to do our data typing in the query editor and then when we get down into the modeler and we're doing visualizations that's where we're going to add our formatting all right so now we've got our budget and I'm going to go ahead and shorten its name so it's just budget because the names of each of these queries is going to be the names of the tables in the final data model so now we're just going to do one quick pass on the query so that we can see kind of how everything looks and we can see that we have everything done the way that we wanted to do that and now I'm going to go ahead and close and apply and what that will do is it will close the query window and that is going to then load all of the data from all the sequel tables and you'll notice that is saying is applying the query changes it's processing the query it's creating the connection and then it will start actually processing the load so now it's loading the data the model is showing how many records are getting processed it detected the relationships and then it finished so now my data model actually has all of the fields from all of the tables in my field list so now we've been into relate in the report view so this is the report view where we're going to actually build our canvas and in the center of this left navigation bar you also have the data view now the data view and is where you can actually go look at the data in your data model and then on the last view is the relationships view and is where your actual data model is showing up and you can actually see your edited diagram now what's interesting is if you might notice there's a lot of lines in this energy diagram already so let's go and look and see what the system what power bi desktop did for us it figured out that there is a relationship between our customer ID in our sales table which is our actuals and the customers in our customer table and because this is the one side in our customer table and our sales side has the many side you'll see that little line has the one and the many on it also our customer table has a zip code in it and that zip code relates to the zip code in our geography table which is where we have the information about the city and state and latitude and longitude of our geography another relationship that got built is between the product ID is the sales table with the product ID in the product table and you'll notice as I'm clicking on the lines of each of the relationships the fields on which the relationship is build is highlighted for me and that allows me to validate that I in fact got them correct which is really good so now my campaign table has a campaign ID now if the names of these IDs were not exact it would not always necessarily find the relationship for you and I'm going to show you in just a second how we draw a relationship or create a relationship if the system didn't ask it didn't actually get it built for us budget is our other fact table so budget has a relationship close on the month of our budget which we're going to relate to our date table but then it also has a relationship up to the product category now you'll notice that we've actually created a snowflake so our sales relates to product at the product ID level and then the products category relates us snowflake dimension which is product category right but then budget is a single level star to the product category table so we're basically creating two fat tables our sales which is our actual and our budget women which is the fact and we're creating them at two different levels all right now let's deal with date as I mentioned very quickly earlier before this system does not automatically ever put in a relationship to your date table and that's because oftentimes there's multiple dates in facts and date is such a critical relationship in your data model that you really don't want to have the that relationship drawn for you you really want to have that control so when I'm drawing a relationship I can grab a feel from my fact table and drop it on top of the related field in my dimension table by doing that the relationship is built and now when I click on the line you'll see that now there is in fact a relationship on date between my sales table and my date table also if I do the same thing using my month field from my budget to my date table I can build the relationship there so now I can use both product category and month to summarize and look at the differences between my actuals and my budget in my data model all right so now we've got our data model belt so let's go take a quick look at our visualizations we don't have any well this is significant what you want to visualize so we're going to go ahead and start building some visualizations so we're going to start off with a good stacked column chart and we're going to get our sales amount from our sales table and we're going to put that into values now from my base table I'm going to go ahead and get my end of must field and I'm going to put axis and you'll notice that the system generated a hierarchy forming at the year quarter month and day level so I can either choose to leave that hierarchy in or I can switch it so that it really in fact does show my month and then I'm going to go ahead on my legend and I'm going to grab my device and if I couldn't remember what table my device came from I can actually use search on my field to find it and then drag that into legend so now I'm seeing my sales amount by end of month and device and that's going to go up into my top right corner and let's go ahead and there we go so that's all set now we're going to go ahead and give it a name so this one I'm going to title as and change the default title to monthly device sales I'm not going to do any formatting on my title just yet but we're going to go ahead that just shows you how we can actually go into this formatting ribbon and we can change change some of the attributes about our charts within this formatting pane all right now let's limit this because we've got quite a few years worth of data in here so we're going to click on the white space to start a new visual and I'm going to go ahead and clear that search I was doing and I'm going to put date in as a slicer now if you notice the date slicer becomes a range slicer and I can actually just drag and drop this range and adjust what time frame the everything on the page is going to be sliced too and you'll see here now that I have it 2013 to 2015 now if I wanted to I could even change this so I could change it to a slicer and now that means that whatever date I pick that the slicer will only ever be from that day forward I can also switch it to be a relative slicer this is brand new this month and I can say you know what I want to be able to always have this page filtered to the last 18 months and I can choose to do relative to today or I can do relative to the calendar now in this case I'm not getting too much interesting data because by using the relative slicer it's looking at today but my data is actually doesn't have any 2016 so we're going to switch this back to be a between slicer again and I'm just going to pick a day and somewhere that's going to show about a year and I can even type a day in here as well so I can say 12 31 2014 2 12 31 2015 which is basically going to give me a nice year's worth of data in this slicer you can also have it be just a regular old drop-down list as well if you so desire now the range slicer is one that you can do on any numeric field or on a date on the rest of the slicers work a little bit differently all right in the I believe it was January relief they came out with a new sub news previews there's a table preview which is brand new and a matrix preview and they keep updating them so they're a little different there is a table and a matrix that come with power bi naturally and they've been around for a while but now they're seasoning previews if you want to see a preview function so if you're reading about the blog and you look at what's new you will um you'll have to turn them on and where you turn them on is in your options and settings and then you're going to options and then you look down F reviews so all of these are the different options that you can set within a power we against that file the ones that I'm showing you here are a relative date slicer that's what one of the ones I showed you we could do like the last year quick measures is a preview feature which is a good one to turn on and then the table and matrix are another one that are just awesome so you'll definitely want to turn them on but one thing to note when you turn on a preview feature you will be asked to restart power bi desktop so it can instantiate and load those visuals for you into your visualization pane so as I'm working with a visual it will then if I have new visuals like my matrix it will show up in my visualization pane so if I click on white space before I click on a visual type it gives me this ghost image kind of like a pivot table does in Excel all right so with this I'm going to go ahead and we're going to put traffic channel on rows we're going to put device on columns columns and then in our values I'm going to use my new URL and my mail to and then also we will go ahead and do the sales amount in here as well and actually I take that back we're going to put those guys on rows there we go but you'll notice that they're not appearing and that's because I need to drill into this chart in order to actually see them so now I can see all the items on my rows but maybe I don't want all the sub totaling nuts in here so I can go in and to my subtotals and I can turn off subtotals so that I'm not seeing that and then then I can go into my row headers and I can turn off the step so if I like it to be more than matrix I can turn that off and then I can turn on the URL icons and let's see they did not switch so let's turn them on okay that's because they needed to be in values add them in the right place in the first license but they're not switching did I tell you this is in preview let's see so we need to make sure that our URL icon is on and our URL icon is on and our URL icons are not behaving the way they're supposed to so let's put them in columns so okay gotta love a live demo right so what they're supposed to do when you drill down and you turn on the URL icons is actually switch to a nice little icon that looks all pretty when you and then is small and short I'll show you another version of what it's supposed to do but I'm not going to go ahead and spend a bunch of time on this right now just because I want to I know we're a little bit limited on our time alright so um so we have a drillable matrix that's actually one of the things that's really key to this new version right now in the preview is that you can actually select items in a matrix and it actually can cross filter other other items on the page or I can select something in another visual and my matrix actually respects it as well so cross filtering across the matrix is a brand new thing that was new to power bi desktop in my everywhere II all right another thing that desktop does really well is it does Maps so now we're going to go ahead and we're going to do a map and we're going to use a review state and then we're also going to have the ability to drill into City on our map and you'll notice all of the dots where my data model have location all 50 states are basically lighting up in that map right away and we're going to use our units as our measure and now my bubbles are all properly showing up on the map in the right place now if I need to go into focus mode I can do this with any visual so that I can really focus in and see what's going on with it and I'm this visual because I have two levels in my location I'm going to go ahead and turn on drill and because I'm living in Washington State I'm going to go ahead and drill on Washington now a really odd behavior is taken place and that is because some of the names of my cities like Kingston being is thinking that I mean Kingston Jamaica and not Kingston Washington so I can help this map along get a lot better at putting everything where it needs to be by using the latitude and longitudes that I have for my zip codes and populating those is the latitude and longitude field drop that in there longitude does not want to go there goes and now you'll notice that all of my dots go appropriately to the right place in the map of Washington so if you are doing geolocation and it's critical that your dots are perfectly located if you can get Latin line you're really going to get a much better geolocation on your map and then I can go ahead and drill that back up to my state to my 50 state level if I so desire now the next thing I want to do is create a tree map that shows my budget variance because I have an actual and I have a budget and I want to be able to create a new measure and how I can do that is I can right click on whatever table and I can click on quick measures now these measures are going to get created using the back language but you actually don't have to be that good at writing Dax so it's for new people this is a really great way to start there's a whole laundry list of items in this in the quick measures that are coming out there's time intelligence for year to date there is filtering values there's aggregates I'm going to go ahead and do a mathematical operation which is a subtraction so we're going to go ahead and we're going to subtract from the budget table we're going to subtract the budget amount and then from the actual table we're going to subtract the actuals amount so it's going to be budget - actuals and the system is going to write this measure and because my cursor was on the budget table it's going to go ahead and put it there but it gave it kind of a long name it gave it budget amount - sale for now and I actually want it to just be called budget variance or a budget barb so I can rename it and I can even adjust it so if the measure is mostly what I need but not exactly what I need I can go ahead and fix it right in that window and now I can go ahead and use my budget variance in a visualization in this case we're going to go ahead and you know maybe I don't know what visual I want at first so I'm going to do that and I'm going to look at it by product category so I've got a got a bar chart that shows a left icon rayji that's the wrong thing let's make it by product category so now here's my budget variances by product category but what I really want it to show is let's do in the tree map so now it's showing up and it looks exactly the way I want it to look and I've got my nice tree map there now you'll notice with just about any visual if you're not sure if you like how it looks and you want to see how it looks as online or you want to see out this was an area etc you can go ahead and play with the different visualization types that are available to you alright so now we're going to go and check out a custom visual so custom visuals are just like the visuals that show up in your visualization pane but they're actually um built by people out in though out in the on the planets right and they do different things so the in the office store you can filter on power bi and it will show you that right now there are 66 power bi custom visuals and I'm going to go ahead and find I want to find a slicer visual we're going to go slice or visual and when I filter on that should bring out just the number of different slicer visuals and I like this chicklet one because it does some cool stuff I can watch a video on it and see information about it and then I can by adding it it allow me to download it I've already downloaded it before tonight so that it wouldn't take so much time and then once you download that file you can then import it into your into your data model and one thing to note is that you would have to import it each in each report that each file that you would want to use a custom visual you do need to re-import that visual and now my chocolate slicer is available so I'm going to go ahead and add it to my visualization pane and I'm going to go ahead and use my product category in that visual and you'll notice that I get my cute little Chiclets and move him oops we'll just do a little moving in sizing and we'll put him down in the bottom right hand corner where I have all my others and now I can actually filter all of the data on my page by product category my mobile device is at a slightly different granularity so it will not filter but some of the rest of the charts on the page will another thing that came out just recently is the ability to add a theme so we're going to go ahead and import a theme into our data model and the theme that we're going to import it is calls just need to go there we're going to go ahead and import spring and watch what happens so this is a JSON file that has all the custom colors in it and so now when I go to set data colors the palette that I'm using is this pretty spring palette and so I have complete control so if you've got a company or a client that has very very specific colors we do a lot of telecom up here in Seattle and there's our phone companies have very very specific colors that they use you know like the bright yellow or the bright pink and so you can make sure that your palette is exactly appropriate based on and based on the color palette that you would that you want to use and I can now add a title very easily to my beta model here f.b.eye Inc product category report and this is um this is now going to be editable and I can change the colors to my particular color I can change font sizes and basically adjust it so that it's all exactly the way I want to see it on my page and we also have the ability to use tiles so if I wanted to have a single just a total unit tile on my page I can go ahead and create that and it defaults to the category label which is the name of the field that it comes from so we can go ahead and update the title and we'll just call this total unit and I want to actually add some Flair so we're going to give this a nice blue background with some white text and make it be a 10-point font and Center it and then we'll go ahead and turn off the category label and so now I've got my nice tile set up and if I want to create a second tile that's very similar I can just copy and all I did with you literally ctrl-c ctrl-v and we're going to make this be total sales and then all I need to do is switch which measure is in this particular visualization so I'll just drag my total sales up there to replace it so now I've got my total units in total sales now all my other visuals none of them have this fancy title bar so I can actually use a format painter to go ahead and apply that nice title bar to all of the - all the visuals on the page except this one the only reason I can't because it doesn't actually have a title yet so I actually would need to give it a title like plenty we'll call it sales by channel and as soon as I give it the name um that title bar is going to show up and I can use my format painter to add a title to everything and then we can use our alignment tools to show our grid lines and we can snap objects I'm not going to worry about too much formatting at the moment and one more thing I do want to show you though is the phone layout so you'll notice on my screen now that I've got the mock-up of what a phone might look like and so maybe when I'm actually showing this for my executive there's a little different view that I want them to see rather than the view that I'm showing you in the wide tablet or web version of the file so that this is now optimized for looking at it on a phone so if a person goes to this report using their phone they're going to get the phone view if they go to this report using the web they're going to get the web view alright the last thing we're going to do before I leave the desktop is show you a little bit on security so there is a couple different way in desktop of managing security one if you're connected to an analysis services cube or a sequel and data bot database that already has role of security and you're using a live connection that security is going to pass through to your users if however you don't have that and it's a munch as the mashup you're doing and you need to manage the rules yourself then you would go into the modeling ribbon click on manage roles and you can create roles using any number of different types of Dax expressions so we can create a role and maybe I'm going to do it at the category level so now I'm going to have a actually let's do it now we'll do it at the category level so I can add a filter based on my product category and say that this is going to be the history role and it's going to be based so the role name is going to be history and it's going to be based on the product category history and then you would create each of these roles and what that's basically doing is it's giving you the structure for this role and so this would be a children role and then this value would be for children and then maybe you have another role that doesn't have anything associated with it but you call that the admin role and so there's nothing actually limiting the admin role but you need to have it so that you have a place to put the people who have an unrestricted view of your data model so then you basically hit save so you've got a data model that is basically done I thought my my report is set the way I needed to be set and now I'm going to jump back out and I'm going to go to app top power bi calm now if you were to go to a power bi calm and you've never signed in here it's not going to look like this you're not going to have a bunch dashboard and it's basically going to say hey welcome it's going to look a lot more like this view which is basically say and hey welcome to power bi and time for you to get some data so in order to actually import now into this service the file that we just created we're going to go ahead and find it on our on our machine and mine is n it's going to the wrong there we go so I want to get the file that we just created and we're going to import this in so while this is importing all that was went pretty quick the default do you want to actually have some assets up here is this new navigation pane and what you're going to see is you're going to see that you've got a number different workspaces where you can collaborate with different teams you're going to have your own personal workspace that's your personal playground and that's actually where I uploaded my report and I don't have a dashboard associated with this report yet it's just a report at this point so right now it's the sequel Saturday demo so this should look exactly like the file that we just uploaded here and if I now wanted to create say my total sales and total units and my executive dashboard would say my rate is barbecue that's my actual this is actually my food truck that my husband owns but anyway let's just say for sake of argument that the big box wanted to see our tiles that we just created from our report what I can actually do is I can take that report and pin any objects see the little Christian I can pin any objects from my report that I built to whichever dashboard so this one I'm going to go ahead and pin up to that rainy barbecue one but let's say this tree map I actually want to start a brand new dashboard I can click here and I can say new sequel set dashboard and on the way I'm screaming that I'll cast it for some strange reason and I went ahead and pinned that so now I can go to the and I can see that I've got that some tiles and then I'm going to go back to this other one so I can show you a couple of things is I'm not limited to the sizes I can basically these are 1x1 chunks and I can expand them out or make them big or whatever and organize them and move them a dragon however I want to and basically build a there's my unit that I just pinned I can build different things now if I've got my new sequel Saturday dashboard this sequel Saturday dashboard had row-level security in it and because it has row-level security I can't do QA but my other dashboard that I happen to have up here I will go ahead and just do a dashboard in a day one right has this QA function if I can do things like how many units did I sell and you'll see that it's showing me this and I can also say you know buy here etc and I can get different visualizations out of it and these visuals are visuals that would then be pinnacle to my dashboard all right so now I've created a couple of dashboards I've got some items pinned to them and now I want to actually start collaborating so now I can go ahead and share so I'm going to go ahead and put in one of my co-workers and if I share this dashboard and I allow them to share it if the transitive thing so that then they can share and they can share and they can share right so you have to be a little bit careful with that if I want to send them an email notification I would leave this box checked when I click Share if I want to do it more silently and I'm actually sharing to a much larger group then I can put an email enabled a group email draft excusing a email enabled distribution group email address in here as well and do a large share your large group of people and when I do that and I go back in to share what I'll see is the people with whom I've shared and also a dashboard link and this dashboard link allows me to say if you want to navigate directly to my dashboard you can use this link all right the other way that you can publish your reports is you can use an embed capability there's two different ways you can do embedding and you do an embedding of a report so I can do an embedding to a to PowerPoint or a SharePoint Online and in some instances you can actually get an embed code for embedding into the web my tenant doesn't actually allow that so I don't have that capability but I can actually embed in SharePoint so let me show you how that gets done so what you need to do is go to your SharePoint page there we go I have a power bi community page where I've got a this is the moderate view of it and all I'm going to do is go ahead and add a webpart to this page which is a power bi preview and it's going to give me this thing asking for report link it's like let's see what do I need to do there so we'll go back here and we'll get that link by clicking on oh that's PowerPoint and it need to export to powerpoint i embedded sharepoint so we're going to pick up our SharePoint Lync and hit close and go back over to our site pop the link in and as it turns out our report only has one page so I'm going to go ahead and it's so-called page 1 so you'll notice that it's going to populate here in just a sec and now all I need to do publish this and save it and this is now going to be a part of my SharePoint page now the benefits here is that any security that I have is going to get enforced so anyone who comes to this page if they don't have access to the right data in your data model they are not going to see data but the people who should see data well so it's a great way of sharing a power bi report across your tenant also if you belong to Microsoft teams and you're using Microsoft team you can use a very similar approach to embed power bi right within your team's workspace all right so a couple questions have come up so we'll go ahead and address those let's see so enter so that different users level of access on a more enterprise level that's a great question when you are working with security there is two different ways you can do it you can do it the way I was showing you which is creating the roles and more statically or you can create them by using an actual username function with index excuse me v2 so far right there but stay on that slide they user name function within Dax so if you have a table that lists the user name and what they're allowed to see then in the Dax function you can actually stipulate what user Nick that the user name function so it will pick up that it's me and what I have access to and only allow me to see that and it's a very dynamic way of working with with security now when you are in power bi and you're in your data set so you actually create your when you publish you create a data set which is your query engine as well as your modeling and DAC engine and that's all in the data sets you'll see that sequel Saturday demo here and then their work but you'll notice there's no report and that's because this is a new canvas in case in the service you wanted to build a second report so here now I can click on security since we added our roles and now I can add different people to the different roles so if I wanted to add my friend Leslie I could add her here or I could add an email enable group to these roles as well and now whenever someone comes to and looks at my dashboards they're going to see the appropriate data in my data model Oh save that there we go now it's now my row level Security's been applied and my report is all still here from an enterprise perspective if you're getting into very large datasets with enterprise level security honestly I would go tabular and you can go a rectangular as well as a tabular unfriend sequel analysis services tabular because you're going to get the better control over your your access levels and I just find it easier to apply on a tabular model it can be it can be a little bit clunky in the service but if you if you've got say less than ten rules you can manage it in the service without much problem and you can also manage it with groups as well which is good when you have a data set the next question is can you download to excel so when you have a data set in the service one of the things that you can do is you can actually analyze it in Excel so what ends up happening when you click on analyze in Excel is it creates a data connection to the data model in the service and it allows you let me just see what it's downloading we'll open it there we go allow this is the exact correct figure and what it does is it creates a data connection to the data set in the service and it allows you to build reports you can use two formulas against it you can use pivot tables against it usually takes a second or two for the pivot table to generate but then now this excel file is separate but it's connected and it still is going to enforce your security I'm going to come back and check on this guy and just a sec because we're getting really close one time and then one other thing I want to mention here too is you can do a scheduled refresh on your data model so you have to have a gateway set up that's a whole nother conversation but once you do then you can actually schedule refresh of your data so that you don't have to be hands-on with refreshing your data and schedule refresh is a pro thing Pro meaning in the service you can use it for free if all you're doing is basically working with your own stuff at the minute then you start really getting into the collaboration then that gets to a pro license which there's different pricing on it now but if you think about it as basically $10 a user per month for that alright how do we get themes so themes you can either get you can either create them yourself there is a very simple JSON structure for them but there is also a themes gallery where there's a bunch of themes that were already created but let me show you what one of those seems this is the spring theme that I just had open a few minutes ago and all it is literally is a small JSON and it shows anywhere from 6 to 12 category colors and then your background color your foreground color and your table accent color and these are the hex versions of colors which you can very easily take the RGB and convert it to hex and then you can apply themes and one thing I do want to bring up so it was one of the questions was asked how can I find out where that new feature stuff is if you go to file what's new it is going to take you to directly to the web directly to whichever month has got the most recent data on it and it's going to show you and it's also going to give you a link for downloading desktop if you haven't already downloaded it so that you can do your updates also there is a community which is really important for you guys to know about community power bi comm is the URL and there's an ideas forum there's the whole thing is one big knowledge base you'll notice for desktop there are 72,000 posts out there already so you can it's searchable you can ask questions there are galleries let's see where's the gallery or the Dutch gallery go um let's see they just move things around there is a themes gallery here so if you want to go get themes you can look at the different color schemes decide what you want and get your themes also there is a data storage gallery which allows you to see kind of different oh I lost my last my back button that allows you to see different stories that people are telling with their data and just a whole bunch of videos that are great they're putting out more videos every day I think you're going to have a video contest coming up shortly but I would highly suggest if you're new to power bi that the debt that the community is just a great place to learn so to do a dashboard you do if do you need the cloud in order to create a dashboard to create the report that we created you do not all you need is power bi desktop and then you can actually just give somebody that file and you can just share it on your file share if you want to create a true dashboard where you're pinning items from multiple data sources then you do need to come into the cloud and create a dashboard within the power bi service and then the cloud is free until you need to do there are certain things that you need to do that you cost and if you come up here and you see your personal storage I have the 10 gigabytes of storage which means I have the pro license if you have one gigabyte of storage then that means that you do have the free license you can start a trial for free and decide whether or not you you want to use it more but honestly the desktop does so much that you can do a lot even without using without using the without using the pro license all right I think I got to all the questions the phone to Webb is it automatic yes it is so when you are if you download the app so right up here in fact at Mario comm you can download the desktop you can now only the Gateway you can download the power bi from mobile all of those demos are available right here super easy place to get them and when you put mobile on your phone and you launch the app you'll give it the URL and it will it will sign you into your dashboard and show you your web versions of your dashboards hey Vova when you building this to report do you have to specify this a power full marble or or a full lab yep now so when your so when you're in when you're here and you're building the report you usually you just build your entire page of canvas and you'll notice you can add multiple pages as well I only built one page but each page can have its own phone view and then so you can stipulate whether or not you want to phone view or you don't want a phone view right in your in your power bi desktop the nice thing about it too is when a person acts it is the report on their phone and they do have the ability to switch back to desktop view by switching to landscape so just by switching the phone it will it will set it back to the landscape to view cool I think there's another question will here odd streaming analytics yes oh yes it does as a matter of fact so when you're empowered the I guess are power bi service and you do have to be in service for this you can let's see start a um if it that I'm yes you can do streaming analytics and I've had some on my page and right now my brain is failing me on exactly where you go to get that but yes a dashboard can't handle it analytic stream from Azure so you can stream in from Azure event have quite nicely as a matter of fact and so it can't handle streaming data maybe later on you can post it on your blog yeah I got a bunch of stuff that I need to post on my blog but yes that would be a good thing to post out there yeah then my dad is a little bit stale I will say right now but I am working fervently on some new stuff I just you know how that goes having a chance to get it out there but I'm on the power bi community all the time answering questions so if you go out to the community and you have questions post them because a if you have the question somebody else probably does too and once we get you answered that's probably going to help a whole lot of other people as well yes also since yeah i Chen has another question okay can we Donna look both charts from desktop to excel oh okay so your chart and the charts themselves don't download just the data so let's go back to Oh to fix that one not for whatever reason I did not want to connect normally it works really well so I'm not quite sure why that why that's that way tonight but so the charge themselves don't download into Excel but when you um when you own a repor sits up in the service if you basically just want to open it back up in the desktop you can just download it again and you can redownload it and then you can edit it from there cool so just in terms of the data sides ya limit

Channel: Saturday Night SQL VG

Views: 85,735

Keywords: Power BI Desktop, Data model, Query language “M”, cross filtering, DAX, visualization, Row Level Security, PowerBI.com, SharePoint Online

Id: aGJa2e8OeSI

Length: 80min 12sec (4812 seconds)

Published: Sun May 07 2017