Power BI Master Tutorial

welcome back to power bi boot camp i'm chelsea gilman and i am very excited to discuss some of the more advanced features of power bi with you through this course we will introduce you to power bi as a program that's what we'll be spending most of our time doing but we will also discuss power bi as it relates to features in excel and the online service called power bi online this has been designed as a hands-on style course with plenty of moments to pause and try on your own in order to follow along you will want to download the work files for this particular section of the course you'll want to go into your student work files and start with day 3 work files in day 3 work files you will see an item called data model that's a power bi file that's where you'll want to start if you haven't been following along with the course up until now you also need to download the power bi desktop program and a recent version of Excel Excel 2016 of course would be nice but you can also use 2013 and 2010 so long as you are allowed by your company to use the add-ins necessary if you have office online or your company has provided you with an office online account you can also follow the course sections discussing power bi online if you've been following along with the course so far then this sheet looks pretty familiar with you in case this is the first video that you're watching in the power bi series you'll want to know that we have already been querying information from many different locations on the right-hand side you'll see all the different tables that we've been querying some of these came in from excel some of them came in from online and some of them came in from an Access database but there's so many other places that we can query information from using power bi on the Home tab of your ribbon in power bi you'll want to find the command called get data this is where you issue all of your queries from scratch now a lot of these sources will seem familiar to us already such as Excel a sequel server analysis services Tex and CSV files we can also of course query information from the web which we have done in day one and from a couple of different locations as well including a blank query however this is not everything that we have available to us if we click on more you'll notice that there are so many other really interesting places where we can import data from like JSON here's Azure we can query information from SharePoint Online list or SharePoint list we can query information from Hadoop from Facebook from Salesforce there's your ODBC Google Analytics github mailchimp and Marketo some very interesting locations so we're gonna start off by querying a sharepoint online list I'm gonna select SharePoint online list and click connect now I will need to provide the URL here so what most people do is they actually go to their SharePoint list location first and then they copy the URL on that page and then they just paste it right in here but in the case that you actually have the URL off the top of your head like I do you can go ahead and type it in then you can follow me along here okay so we're going to HTTP colon slash slash learn it anywhere dot SharePoint dot-com slash power bi and I'm taking care here to make sure that I'm spelling everything perfectly now's a good time to pause the video and make sure that you get this entire URL after I click on okay I will now have the opportunity to put in the credentials for this so depending on the source I might actually have credentials for this or I might want to access this as simply an anonymous user for our purposes we're just going to use the anonymous access and go ahead and click on connect in this case I'm just going to need to go ahead and sign in here so I'm going to click on sign in and that's gonna allow me to sign into this account and grab that information from SharePoint and I'll go ahead and click on connect I'll see the table right here called contoso sales customers that's exactly what I'm looking for gonna click on it and click on load alright once that is successful you should now have your contoso sales customers table loaded in here if you decide not to do this exercise no worries because we're not actually going to be using this data if you haven't done so already and you want to feel free to take a moment pause the video go to get data and get data from a SharePoint list you could even try one of your organization's SharePoint lists for the next big important example we're gonna talk about unfitting data which until recently wasn't really a term but it has now been coined a term and there is a command that does it for you let's first discuss why you might need to do this you'll see that the data that's presented here on this slide clearly the top image here is actually of a pivot table but the bottom example is just an example where somebody chose to populate something as though it were being displayed in a pivot table the only problem with this is that your pivot table reports are not good data for querying for a couple of reasons namely the data is not structured and columned out by category we'll see here that we have multiple different periods of time if we have multiple different periods of time those things should really all be listed as labels in a single column so you would have a column that said a year and then a column that said quarter and a column that said month if you wanted that as well instead what we have here is 2007 quarter one and 2007 quarter two or something like that now this is really not good data so what we're going to want to do is take this pivoted result and unfit the data to make the data much more easy to analyze model and utilize in our analysis we're going to start by getting some data that is already pivoted so we're gonna want to go up to get data and this data is housed in one of the excel files that's in your work files I'm gonna click on Excel and we're looking for a file in student files in day 3 work files and we're looking for the file called competitor report now if I click on open I'll have the option to choose from three different tables that are actually pivoted results so one of them analyzes profits another one analyzes returns another one analyzes sales we're gonna start with just taking a look at the profit totals page here and once we do this we're gonna we're going to open this up we're gonna unpin it and then we are going to take all of the features that we've applied to this very first one and and instead of reissuing this for the other two were also going to learn how to use another and instead of doing this individually for the other two tables we're simply going to copy the EM script that was applied when we applied our query steps to the first one and then we'll just paste it in the other two tables so I'm just gonna take a quick look and it does look like each one of these tables look similar they're all set up the same way so I should be able to do this just fine so start by just selecting the table called profit totals and instead of just immediately loading it I'm actually going to edit some of the query steps here this is gonna launch my query editor and that's perfect that's exactly what I want so this is actually some information from our competitors so we have we have data about our competitors and the different product categories that they sell and the different profit for instance listed for those particular categories so I'm gonna want to do a couple things before I actually unfit anything first of all you'll notice that our headers here say column 1 column 2 column 3 column 4 that's not what we want we'll have to fix that but before we do that we actually have this very first row of quote unquote data that is not actually data right this is just a title so this is a sum of profit report and then it's listing some column labels here and then everything else is null so the first thing that we're gonna want to do is remove this unnecessary row here so I'm gonna go find my command called remove rows and I'm gonna select remove top rows and it's just the top 1 row that I want to remove so I'll type in 1 and I'll click OK now is a good time to pause the video and do this and come on back we'll keep going the other thing I don't need is grand totals if I scan all the way to the right hand side I do have a grand total over here I'm not gonna need that so I'm just gonna go ahead and remove this right now so I'm gonna select that column and choose remove columns then as we were observing earlier we do have these column names that are not the correct names right those are just placeholders so I'm also gonna use this command that says use first row as headers okay I'm gonna scroll back to the beginning here so we can take a look at this data our row labels are actually appropriate these are our categories or our product categories so that's okay information to be columned out everything else here is inappropriately columned out there's way to finite sections of detail in each one of these columns all of this data in these columns need be combined essentially into one column or eventually we'll have year quarter and month as separate columns so the first thing that we're gonna want to do is select the necessary data to unfit it a good way to think about this is if you have your labels already showing up in a column you don't actually need to unpick those what we do need to unpick here is all of the items that are being sectioned out into different columns that should really belong in one time or date column so I'm going to start by selecting the very first year quarter month column here I'm gonna hold down my shift key and go all the way to the end of my data I'm holding it on my shift key and selecting the last one there by selecting all of the columns except the first one and this is very important I have not grabbed this very first column here because it doesn't need to be unfitted now that I've selected all the rest of those columns there I'm gonna go to the transform tab of the ribbon and I'm gonna find the feature here called unpick columns after unfitting your columns your data should look like mine if it doesn't you might want to go back a step just by clicking on this little X right here go back a step and make sure that you selected all of the columns that had year quarter and month as a header but not not the category labels we didn't want to select those all right so this is looking pretty good so far I might want to rename some of my columns I am gonna need to split my attribute column too I would like this to be split by year and by quarter and by month so I'm gonna go ahead and start with that first I'm on the transform tab of the ribbon and I'm gonna find the feature called split column and I'm gonna choose to split the column by a delimiter now I wish I had a simple delimiter in here but I don't I have a rather custom delimiter of a space - space in there so I'm gonna need to choose the custom option from the delimiter drop-down menu and then I'm just gonna type in space - space and I'm gonna choose to split this at each occurrence of the delimiter you'll see that I actually have that going on multiple times in this column here so I'm gonna want this to work for every single occurrence of this particular delimiter and I'll go ahead and click OK now's a good time to pause the video and make sure that you have chosen to split by a delimiter a custom delimiter space - space this looks pretty good now all I'm gonna want to do is rename my columns so that they're actually named appropriately so maybe I want this to be a category name the second column should be year and then I'll have quarter and then this is month and for value over here this is profit data so I am going to call it profit okay I'm feeling pretty good about this so let's go ahead and save this query now I don't actually want to close it but I do want to apply it so I'm going to click on the close and apply drop down menu and choose apply now there's a benefit to having your original data all be structured exactly the same it just so turns out the other data that I'm gonna need to fetch I already have profit but I'll need the sales and returns data once I query that it has all the exact same steps I don't need the totals column all the way on the right hand side and I needed to unpick it all my data except for the labels then all I did was split my year quarter month column into three separate columns then I just renamed these headers here so I'm gonna need the exact same steps in the other queries I'm gonna issue right now however if they're gonna be exactly the same steps then why repeat all that work on the Home tab of your ribbon you'll see a command called advanced editor if you open up the advanced editor you'll actually be able to see the EM script that has been used to achieve this query that you've done now if I want to take all of the querying changes that I've made already all I need to do is just select the appropriate query steps here now you might think that you could copy all of this data here but you actually can't because the first item here talks about where to get the information from and then what sheet to actually get there last time we got profit totals but this time we're not going to want to do that after that power query always transforms the data into a table and then following that you'll start to see the commands that you actually applied so the first thing that we did is we remove the top rows then we remove some columns we promoted some headers we unfitted the columns split columns by a delimiter and then there's some more information about transforming that into a table style there and then of course we did rename those columns there so the steps that we actually took we started with this hash sign remove top rows what I'm going to do is I'm going to copy everything from that all the way to the bottom I'm just gonna highlight it like this and then I'm gonna use my ctrl C shortcut on the keyboard to copy that once I've copied this you can either place it in a smile if you want to keep it forever or if you're just gonna do this and just run it a couple of times right now you can actually just keep it on your clipboard and do it right now I did copy that I'm gonna go ahead and click done and then I'm gonna issue another query from a new source so I'm going to start to query those other two tables that we didn't grab last time I'm going to start with the new source button here and then I'll click on Excel I'll choose the competitor report but this time I'm gonna choose one of the other tables here so I already got the profit totals now I'm gonna grab the returns totals and I'm gonna click OK now before I do anything in here I already have all the steps I need so I'm gonna go straight into the advanced editor all I'm gonna do is remove this last line here that says in just gonna remove that and paste my data now there's one thing that I might not be totally aware is gonna be a problem for me in the last step I removed the previous statement that said in hash and then there was an instruction at the end of that what I might not be aware of is that each one of these lines of M script all the way at the end if there's gonna be another line after it they all need to have commas there because this line right here didn't actually have anything after it except for the in statement this line the change type line doesn't currently have a comma at the end so you'll need to go to the end of that column there go all the way to the end and then just make sure to put a comma in there and then you should be good go ahead and click on done and that looks pretty good and one thing I will need to do is make sure to change the title of this column to returns I can either do that by just changing it manually here or you can go into the advanced editor find the line where you renamed the header or rename the column and you can just go and find a statement that currently says that it's changing it to profit and change it to returns looks pretty good now I've just got to do this for the last one first I'm going to apply this just being safe and now I'm going to start with a new source from Excel competitor report and I'm just gonna do the last table the sales totals click okay go into the advanced editor remove this and paste it I'll also go to the end of the previous line and put a comma in there and then maybe if I'm really savvy I'll go over here and change the renaming of that column from profit to sales and click done if you haven't done so already now is a really good time to pause the video make sure that you have completed the first query on your own and then after that you will want to copy the EM script in the advanced editor now remember you will need to copy the EM script after where it says hash changed type table transform column types after that statement you need a comma at the end of that statement and then you can go ahead and paste your code at the end you'll want to make sure that you only have one in statement and if yours looks like mine you should be good go ahead and click on done now in this last example we were able to open up the advanced editor and take a look at some scripts that you might not already be familiar with this script that you'll see back here is referred to as M m is the querying language that we use with power query or with the query editor here in power bi now if you haven't looked at M before it's gonna look a little bit foreign to you and so I want to take a couple of minutes just to familiarize ourself with some of the very simple basics of M if you want to get more involved in M I highly encourage you grabbing a book called M is for data monkey it's a really well reviewed book about the M language for our purposes we're just going to learn enough of the basics to be able to perform a simple operation M is a case sensitive query language it uses functions and with each function or with most of the functions you can specify a type for that function now each of these functions much like the functions in Excel are going to need particular arguments to be specified in order for the function to be able to process down here at the bottom where it says make a list of dates this is where we have the example of the function that we are actually going to use and let's talk about why we're gonna do this today in a previous video we have discussed the benefits of creating a calendar table or a date table and we actually created an entire date table but we did that using the data modeling engine or if you're in Excel that would be the power pivot engine as neat as it was to be able to create that it has a couple of limitations namely every single time you want to create a calendar table you're gonna have to create it from scratch but as we already know with M we can easily copy and paste m in a new file in a new place on different data and it will perform that entire operation very quickly like we just saw in the last example so what we're gonna do is we are gonna take the knowledge that we have about calendar tables and we're gonna learn how to create them much more efficiently if we create them with M the very first time that we create it we still have to create the entire thing scratch but every time we want to create another calendar table there after all we have to do is copy and paste the EM script and it's really fast the particular function that we're going to be using to create this calendar table is the list function so you'll see the list function right here and specifically we're going to be listing dates this list dates function has three different arguments first of all you have to determine when you want that calendar to start how long you want that calendar to be and when you're specifying how long you want it to be are you talking about days hours minutes or seconds so once we use this function to get us started we can then use a couple more familiar looking functions to create all the metadata that we need in order to issue a brand new query we're going to want to start with a new source here and this time we're actually going to issue a blank query that's gonna allow us to start pretty much completely from scratch so we're gonna want to click on new source and then go down to blank query now it looks pretty empty so far we need to use our list dates function in order to get some dates started that we can work with I'm gonna start with an equal sign just like the functions in Excel and then I will name the function that I'm gonna use which is called list and then I'm gonna put in a dot and dates that specifies what kind of a list I want to create and then I'm gonna open up my parens now in Excel we were able to put in the date function and that allows us to transform a particular day like you and I think about it now in order to type in a particular day here I'm gonna have a little bit of trouble if I try typing in something like 1 comma 1 comma 2014 as we saw in the last example of calendar table our calendar table our program really doesn't want to recognize this as a date our program wants the serial number for that date I have no idea what the serial number is for January 1st 2014 so I'm going to use a date function to do that now in Excel when we did the date function we just create a date function like that but in M it looks more like this hash date make sure to be lowercase here remember M is a case sensitive querying language and then I'm going to use this function to take my inputs of year month and day and it will transform that into an actual serial number now let's just start with something basic we've already determined when our calendar is supposed to start January 1st 2014 let's just say we wanted 10 days we could then type in 10 as the duration and then the third argument is just specifying how long each one of those 10 things are is it a day long is an hour long a minute a second long so I'm gonna use this duration function to just say yes it's a day and this is how we notate that one zero zero zero for all the other alternatives I've closed out my duration function but I have not closed out my whole big list dates function so I'm gonna go ahead and close that out as well and now's a good time to pause the video make sure that yours matches mine character for character and then go ahead and hit enter and confirm that it works so this is pretty good it worked however what we really need to do is get every single day from january 1st 2014 through the end of our time period which is December 31st 2016 I have no idea how many days that is I don't want to count and figure it out I suppose I could go over into Excel and just subtract the days from each other but I'm I could just as easily subtract the days from each other here in power query I'm gonna use a function called duration dot days this is gonna allow me to calculate the difference between two different dates so I'm gonna take my last date which is December 31st 2016 and of course I don't know what the serial number is for that so I'm going to use my date function again here and then we'll say it's 2016 December 31st and we're gonna subtract from that January first 2014 that should give me the number of days between those two dates now the only thing is that this subtraction right here is actually going to end up being one day short so you have a couple options you could either add one more day in here or what a lot of people do is they just skip one more day ahead so they'll just make it easy for themselves and go in and say 2017 one one that'll make sure that we actually have all the days that we need okay I'm gonna want to make sure to close out my duration days function here and then I'll go ahead and hit enter and I should have all the days in here last thing I'm gonna do here is actually convert this to a table now you'll see in the ribbon on the upper left-hand side and now have a button that says convert to table so so far this is not actually a table of data this is just a blank query with one list function right there so I'm gonna go ahead and click on convert to table and I'll choose I don't have any extra columns I don't need to select or enter a delimiter I can just go ahead and click on OK and now I have this really good list of dates here I'm gonna rename my very first column to date key make sure that you do this as well rename your first column to date key okay come on back and we'll add the metadata now of course this is not gonna be a complete calendar table if I only have a list of dates I'll need to also create all the matching metadata that I'm actually gonna want to analyze by I'll start by creating a column that just lists the year find the add column tab of the ribbon in power bi and on the left hand side you'll see a command that says custom column I'm gonna go ahead and click on that we're gonna call this year and here's how the M Script is going to be used to create all these columns of metadata the function that we need is the date function specifically we need it to extract the year I'll open up that function on the right hand side I actually have the other existing available columns that I could use over here and I'll just double click on the column that we already created called date key I'll close out my function here now's a good time to pause the video and make sure yours looks exactly like mine and then go ahead and click OK we're gonna need to create quite a bit of metadata and so I'm gonna put up a slide that's gonna have each one of the formulas you'll need here's a list of all the columns metadata that you want to have in your calendar table now's a good time to pause the video and add all the metadata that we want in our calendar table here and then come on back and we'll keep going all right well done that may have taken a little bit of time but it was definitely worth it I'm just gonna rename our table here and I'm gonna call this our dates M okay now I can either click on close and apply or just apply I'm gonna click on apply because we're still gonna have a couple cool things that we want to explore back here in the query editor but before we move on I just want to impress upon us how cool it is that we've set this whole thing up if we now want to go into the advanced editor we can simply copy all of this M here and just paste it anywhere else that we want this exact same calendar table for instance let's say I want to get a calendar table in Excel what I can do is I can copy all the script that I've just created here in the query editor and as we've discussed in Prior videos the query editor here in power bi is almost exactly the same as power query in Excel so I could go ahead and open up Excel if I want to and for this tutorial I'm just gonna land in a blank workbook and what I'm gonna do is I'm gonna go over to the data tab and find my get and transform command group in previous videos we've discussed that this command group right here is also called power query now I'm in 2016 if you have a prior version of Excel then you will have needed to download the power query add-in if you have the power query added in one of those previous versions of Excel then it doesn't show up as a command group on the data tab of the ribbon instead you'll see that you have a tab of the ribbon called power query either way I'm gonna choose to issue a brand new query from another source from other sources and at the very bottom of that list I'll see a blank query now just like last time when we are creating this query in power bi the very first option I have here is to start with a function that will create the list and I could indeed start this whole thing over again but we've done all of the work and we copied all that work so now all we need to do is go into the advanced editor and simply select everything that's there remove it and paste now that I've pasted all that great script that we just spent so much time creating go ahead and click done and I have that whole calendar right here in Excel so anytime I need to add a calendar to either Excel or to power bi the great thing is that I only had to do that once so now I know that in the future if I ever need to get this calendar table in any other file I've already done all the work for it all I have to do is just copy that exact same script and just paste it right in here and now it's done just like that I've returned back to power bi to discuss our last cool feature that we're going to learn about power query in this course we're going to talk about creating your own custom functions we've created this really great calendar table however in the future I might not always want to start my calendar from January 1st 2014 and I might not want to end it on December 31st 2016 so I want to build in some flexibility here so that I can enter in a start date and an end date and this calendar table will automatically populate with whatever I choose my start date and end date to be creating a function in power bi is a little bit easier than it is in Excel we're gonna start by going into the advanced editor for your dates M table here so I'm gonna click on the advanced editor to add the little bit of coding necessary in order to convert this query into a function a custom function all you have to do is start by opening and closing two parens put an equal sign and then your greater than sign now what I click on done notice that this actually gets converted to a function look my symbol has changed in everything now all I need to do is create some parameters that I can use for my start date and end date on the Home tab of the ribbon you'll see a command right here called manage parameters if I click on manage parameters I can easily create a new parameter I'm going to start by creating a parameter called start date now this is going to be a date datatype but I'm just going to plug in the starting value as 1 1 2014 click okay now I even have a parameter for my function now my function is going to be ready to run at any time if I click this invoke button here it's gonna create a brand new table it's just gonna run that entire function so it's just going to invoke that entire function again and essentially create a double of my calendar table so I'll just go ahead and click on that to test it out and bam now I have something called invoked function so if I wanted to I could come in here and I could rename this my current dates or whatever I wanted to name it and then I could keep that as my date table however we discussed using a parameter inside of a function so I've created the parameter called start date now I actually have to go into my function and put my parameter in there and here's how we do that in the advanced editor I'm gonna need to start by passing the parameter through the parentheses that initiated my function here that little programming is actually meant to hold parameters and you could put as many parameters as you want in here I'm gonna say okay I'm gonna use a parameter called start date so be ready for it now I'm actually going to go and find the locations where I've hard coded January 1st 2014 and I'm gonna remove all of that and I'm gonna put in start date and I could find every other time I need to use this as well in a real world situation you would also probably want to apply this to an end date so you would create another parameter and use it for end date but we're just going to start with our very simple little example here so I am putting my start date in there as the starting date for my calendar and I'll go ahead and click on done now you'll see that I actually have the ability to type in a start date here and I'll go ahead and type in 2014 that exact same date because that's actually the dates that we need for our data for the rest of the class it'll click on invoke now I have a brand new calendar table that is starting on January 1st 2014 okay let's clean this up a little bit here that was pretty cool but I just want you to take a moment and make sure to delete any extra versions of this calendar we only want a single calendar and we'll call it dates and we only want one dates table when you're done but in the future now you know you can always just plug in a new start date you could plug in an end date very easily and use those parameters great now that we have all of our dates in here let's actually get back to some fun analysis I'm going to go ahead and click on close and apply here and you may notice that on the right hand side your dates table has actually been renamed to dates too that's totally fine for the rest of the course though you will want to make sure to use just a single one of these dates tables and you'll want to make sure that it's related to your data now that we have these great dates tables we're actually gonna start to take a look at date information and using some of the time intelligence features in power bi in order to do this you'll want to create a brand new fresh slate here we're gonna get a fresh canvas by creating a new page we're gonna go ahead and build a couple of tables that are gonna be the exercises with which we create our time Intelligence functions now I do have the dates to table that we created with power query or the query editor engine here on power bi however I'm not gonna use that I could but I would need to go in and relate the table to the rest of my data and I do still have this dates table that we built using the data modeling features so I'm gonna go ahead and use these just out of convenience let's say I wanted to be able to analyze some information about our years and about our months I'm gonna grab my year field drag it and drop it on the canvas and then I'll also do that with my month name field now this is showing up as a clustered column chart but for the next few examples we're not gonna use that very much we're gonna use this matrix table visual instead I'm going to switch to the matrix table and now I can actually see my data segmented out by year and all of the different months in each year now let's start to do some analysis we are going to take a look at our total sales information here I'm gonna move my table to the left hand side we do have total sales information we have already created a measure called total sales and it is on the fact sales table so I'm gonna go ahead and open up the fact sales table find my total sales measure and I'm gonna drag that and drop it in my matrix table as well now this is pretty good however if I wanted to start to be able to see something like a running total or a year-to-date calculation I don't really have anything built in here already that can accomplish that we need to talk about some concepts of how data is presented whether it's here on a table or whether it's in any other kind of a visual there are some intrinsic filters that are being applied simply by the nature of organizing data in a table or in a visual for instance my table is segmenting my larger data set to just analyze three different fields so it's looking at total sales information I have a column of data and a fax sales table that contains the sales data and then I am using this category here the year 2016 to segment out that entire collection of sales data for just a particular year and then I am drilling down a little bit deeper and also starting to segment it out by month so if you think about it there are intrinsic filters that are being applied here by the table so this total that I'm seeing right here that is not a total for all my total sales it's a total for just 2016 and just January now if we wanted to create a measure that provided a running total we would need to essentially negate the existing filter here on the table we would have to say no longer filter for just January let's say we were figuring out the running total for February in that case February would include the totals of both January and February because this is a running total now this is a year-to-date function which means that it is continuing to add up each previous month all right so now I have to take this one little spot in my table and somehow get my table to understand that it needs to ignore the the February only filter ignore that filter and instead include February and January and add it up and place it in that one little spot essentially what we are doing is we are intentionally negating implicit filters on the table so essentially we're going to create a function that will still add up the total sales but this time only if that sales have to do with January and February now we've already seen a function that allows us to some if or average if here in power bi and that function was called calculate now we talked about how this function allows you to put in a measure that you've created like sum and then you can apply a filter criteria or many filter criterias this helps you build your own custom sum if average if or what else you want now this if criteria is actually just a filter option but what's really cool about the calculate function is it can actually use this filter argument here to negate existing filters so instead of saying just February here we could say if it's January and February now building this function on your own could be a little bit cumbersome so it's nice that we've already had a few functions designed for us that are gonna really nicely take care of the job we have year-to-date functions and then you also have months to date and quarter to date functions that you can use as well these functions look like this dates year to date now this is only used as a filter criteria for your calculate function so essentially this is what we consider a helper function for your calculate so we're gonna go ahead and build ourselves a measure that's going to be able to add up the total sales but take into account the actual year-to-date information I'm going to right click on our fact sales table and choose to create a new measure now I'm gonna call this measure total sales year-to-date and maybe I'll actually put a little space in there that's gonna help me after I've created three different ones that'll help me to have that little space in there visually all right so this is going to be based on my calculate function and it's just going to use the normal total sales measure that we've already created you and the second argument the filter criteria for your calculate function is going to be the dates year-to-date function there and we will tell this function to take the dates that it normally uses which would be the date key and our date key is in our dates table and you'll see that you have a lot of specific options that you can use but this is pretty good we can close out this function here now I've closed out the dates here to date function I'm going to need to put another parens on the end to close out my calculate function and then I'm going to go ahead and hit enter let's see if it worked I'm gonna find my total sales year-to-date drag it and drop it on my visual and there we go that looks pretty good if you haven't done so already now's a great time to pause the video and create a new function we're gonna call it total sales year to date and the calculate function is going to be based off the normal total sales measure that we use except for an additional filter criteria that allows it to take into account that running total and for that we use the dates year-to-date function and we chose to negate the existing filter of February or March or whatever it is there and include all of the dates for the entire year there's a whole collection of really neat time intelligence functions in power bi we're gonna focus on a couple of them we've already talked about dates year-to-date you might also want to create additional measures for quarter to date and then you could also do month to date but keep in mind you're only looking at your month information so if you actually want to see your month to date function working you will need to take some data that is more granular than months like maybe weeks or something like that and then you'll actually be able to see your month to date function in action now is a good time to pause the video and create additional functions based on this calculate year-to-date except you're gonna create ones for quarter to date and for month to date go ahead and give that a shot and come on back to see if you got a right when you've created the quarter to date and month to date measures go ahead and also put them on your matrix table here so you can see the results with year-to-date you'll see that the running total continues adding up until it actually gets to the end of the year which makes sense because that's a year to date for your quarter to date measure you'll notice that that only continues accumulating value until the actual quarter is over and then in the fourth month for instance in April you'll see it reset in month to date you'll see that each month actually looks like the original total sales looked and that's because we're not seeing granular enough information to actually see month to date start to add up what you want to do is start to segment out your information by maybe weeks or something like that and then you'll be able to see it in action if you'd like to compare your measure to mine here's the month to date function that I created and then I'm gonna click on the quarter to date function as well so we can take a look at that one as well now is a good time to pause the video make sure that each one of those look correct and your answers look reasonable and then we'll move on for the next couple of exercises I'm going to go ahead and remove the year-to-date quarter to date and month to date measures that we've already made in this next example we're gonna take a look at a really handy function another helper function of calculate but it's one that allows you to go forward or backward in time now let's talk first about why we might want to do this if you wanted to figure out the growth or change over time from this month to the previous month you're gonna need to be able to include the previous month's data right alongside the current month's data so that you can actually subtract them together so we're gonna need to create a measure that is actually able to look to a previous date or a previous month or a previous quarter or previous year now we probably don't want to make this function to just use the functions result all by itself but we might want to use this function to calculate change over time we can only create change over time calculations if we can simultaneously consider a current value and a previous value and calculate them against each other for instance if you are calculating percent change over time you might take the previous value and subtract from it the current value and then the result of that could be divided over the previous value and that would give you a growth rate there are a couple different ways to calculate a growth rate but most of them require you to be able to take into account a previous value and a current value at the same time in a single calculation so first we're gonna need to be able to create a measure that can actually look at a previous month or previous years total sales we are gonna use calculate for this calculate once again come in in handy I'm gonna right click on my FAC sales table and create a new measure and I'm just gonna start by saying this is gonna be total sales for the previous month now I'm gonna use the calculate function to do this and it's gonna calculate the normal total sales measure except we're gonna disrupt the existing filter stop looking at the existing current filter and we're going to use the date add function to look forward or backward in time now the date add function just has three arguments first of all I need to tell it what dates it's going to be looking at so I can either say use the dates from the fax sales table or if you think about it the fact sales table is actually retrieving that information from the dates table so I'll probably use my dates table since it's more parental in a hierarchy I'll say look at the dates date key and then I wanted to go back in time by minus one that's gonna go backwards in time by one and then I need to specify one what one day one month one quarter one year I'll say one month and I should be good placing this on the same visual right next to your normal total sales measure should display information from the previous time period so I should be able to see February showing up there that should be the same actual total sales amount from January so February in my total sales previous month should be equal to January in my regular total sales now is a good time to pause the video and make sure that you have created this total sales previous month calculation okay now that I can actually look at the current month and the previous month in two separate measures now I can use those measures in a calculation themselves so I'm going to create one more measure here and create a new measure and we're gonna call this percent change from the previous month maybe I'll shorten this up a little bit now my percent change from the previous month is simply going to be my total sales for the previous month - my current total sales which is just total sales I'm gonna want to subtract these from each other and then after I get that result I'm gonna want to divide it over the total sales for the previous month of course if I just do this with a slash right now for the divide sign I'm gonna have a PEMDAS problem if you remember your order of operations from school we should put those parentheses in there to get this calculation to actually happen first and then I'll go ahead and say I want to divide it by the total sales for the previous month now we should be able to go ahead and execute that and add it on to our visual looks pretty good but I'm going to format it so it actually shows up as a percentage and that should look a little bit better I can go ahead and remove my total sales for the previous month calculation from my matrix table because I actually don't need that for my end-users to be able to see that that would be a little bit confusing for them I really just created that measure in order to be able to use it in this percent change previous month calculation if you haven't done so already now is a great time to make sure that you have completed not only the total sales previous month measure but also the percent change previous month measure as well and then go ahead and throw it onto your visual and see how it looks for these next couple of examples I'm going to remove all the existing measures that we have from our table and I'm just gonna leave the labels for year and month we're gonna use a function called first date and what its gonna do is detect the periods that you are using on your table and it will be able to populate for you the first date of that month now it also works for quarters it works for years it works for weeks it's great let's go ahead and build this I'm gonna right click on my fact sales table and create a new measure we're just gonna start by calling this first date period so of any given standard calendar period it will be able to figure out the first date of that period now this function is really easy and it's a standalone function if you want to use it by itself I'll just type in first date here and there's my first state function excellent and I am just going to use the date key from the dates table if I drag this and drop it on my table I'll be able to see the very first date of each period populated there pretty cool this comes in handy especially if you're wanting to do a calculation like first 15 maybe you want to know what the total sales were of the first 15 days in every period because you want to be able to analyze that in that case not only are we going to have to detect what the first date in each period is or in each month is but we're also going to need to be able to perform a arithmetic calculation while expanding our filter criteria to include the first date the second date the third date etc all the way until we get to the 15th date now we are going to use the calculate function to do this but we're gonna use a helper function of calculate called dates between and what this function is gonna do is allow us to include all of the dates between this first date of the period and the 15th date of the period what's nice is that the dates between function will allow us to collect all of those days together that we want to be able to analyze and then the calculate function can actually use those dates as the filter criteria so let's go ahead and create a new measure here I'm going to right click on fact sales table create a new measure and we're gonna call this total sales first 15 now I am going use my calculate function for this and my calculate function is going to be using our regular total sales measure now for the filter criteria I'm going to use this great function called dates between now the dates between function only has three arguments it just needs you to tell it what dates it's going to be using so in our case we're just going to use the dates key information from our date table and then all we need to do is tell it what the starting date is and what the ending date is now if we really wanted to do this and hard-code it we could say okay calculate this for the first day of January all the way through the fifteenth day of January but eventually we want to be able to use this calculation for any of the other months as well we're gonna use our cool first date function to do this for us so it's going to TechEd the very first date in that particular period and that will be the starting date and the ending date is gonna be the first date I guess actually in our case it's gonna be the first date plus 14 okay I need to close my okay let's put it on our visual and see if it worked looks good looks pretty good now let's say we want to be able to check out this great analysis that we've done for our own company but we also want to compare this to the information for our competitor now we pulled in some competitor data earlier in today's session we called those tables profit totals returned totals and sales totals we could easily have renamed those to be a little bit more explicit and say something like competitor profit competitor returns and competitor sales but for now you and I know what they mean so we'll leave it as is if we want to be able to do any of the analyses that we have already done for ourselves on our competitor data we're gonna need to relate that competitor data to the rest of our data so let's start by doing that we're gonna go over to our relationships mode and in relationships mode you may need to click on this little command down here that will expand your view so that you can see all of your tables in there here's the information for our competitors profit totals return totals and sales totals as well we also have a couple of other tables over here that we're not going to be using in any further examples so I'm going to move them over to the side lucky for us in all those time intelligence functions that we've already created we reference the dates that are on the dates table each one of those functions would also have worked if you reference the dates that are on the fact sales table however in a few minutes we're gonna be taking our dates table and using it to create a connection to our competitors data by nature of relationships any kind of a filter that you apply to a parental table like this we refer to them as look-up tables any filter that you apply to a dates table like we did with our time and date like we did with our time Intelligence functions that same filter will flow downstream into any down stream tables like fact sales so this is convenient we've created these measures and they're on the dates table so in a couple of minutes when we now relate our date's table information to our competitors data it means that those exact same calculations are also going to be useable with our competitors data let's start by trying to take our date key information and relate it to the sales information from our competitors first of all we're gonna notice a bit of a problem in that in our dates table we have a date key but there's no date key over here in our competitors table so what some people might think to do is to take a year or month and try and drag and drop that to create a relationship with that for instance if I tried to grab month number and relate it to month over in our competitors data we're gonna have a problem because you can't create a relationship between these two columns because one of the columns must have unique values so the problem that we're having is that each month number is listed multiple times in the dates table and it's also listed multiple times in the sales table well if I have the same values showing up multiple times in each table how is power bi supposed to understand which version of january is supposed to connect to which version of january over on the sales table or February to February so the problem I'm having here is that I have multiples in each one of these tables what would solve this problem is if I had a value in my competitor table over here that didn't repeat multiple times such as a date key then I could relate the date key to date keys over here in sales totals the only problem is that the data from my competitor isn't that granular the data from my competitor only gets as granular as months we have years quarters and months we don't have weeks we don't have days and we certainly don't have hours and minutes so what we're going to do to deal with this is cover a couple of complicated relationship strategies these are going to help you handle the different kinds of complicated relationships that you will naturally run into as part of your power bi career in our scenario here because we're having a granularity discrepancy between these two tables that's what's really causing our problem what we can do is falsely create some more highly granular data over here in our competitor table if I create something that looks like a date key then I'll be able to relate it to the date key in our own data so let's go ahead and do that I'm going to switch back to data mode so that I can look at the actual data in our competitors sales table and I'll go ahead and find our sales totals and we'll see that we just have year information quarter information and month information what we're gonna need to do is falsely generate some date keys for each one of these months I'm gonna go ahead and create a new column and I suggest you follow along with me this new column is going to populate a date key for each one of these months now a typical date key should have the year and month and day basically meaning that it is an entire date key now we already know of a cool function that will do this for us and that is the date function I'm going to go ahead and name this date key and then we'll use the date function for the year of the date function I can actually use the year that is in this particular table we have a year column right here in our table I'll choose sales totals year for the month we have the same sales totals month now for the day it's totally up to you although I usually choose the very first day of the period so I'm always going to choose day one now when we execute this you should notice that date keys are now being populated in here now all we have to do is go back to relationships mode and now use these date keys that we've generated and connect them with the date keys on the dates table I'm gonna grab the sales totals Day key and drag it and drop it on top of the dates table date key there we go now I should be able to take our sales total data from our competitor and compare it side-by-side with our total sales in our own data let's go back to report mode in order to take a look at this I'm going to take off the two measures that we already have on here from the last example and instead I'm going to find our total sales measure which looks at our own data and then we'll go down to our competitor data sales totals grab the sales information from here drag it and drop it onto our visual as well if we look at this data in the visual we really want to be careful that the sales information doesn't go into rows but that the sales information goes into values now this is another hiccup that has come in from the way that we actually retrieved this data and the problem that we are seeing here is that this numerical data is being treated like text so we're gonna need to fix that going back to data mode let's go ahead and isolate our sales column in our sales totals table and you'll notice up here that the formatting of this particular data is coming in as text that has to do with the weight that we retrieved this information we can go ahead and just change it really quickly here and say that this should be a decimal number and I'll go ahead and click YES on this morning and now let's go ahead and look at our visual and see if it looks a little bit better now I do have a minimum of sales being calculated but I can go ahead and fix that just by clicking on sales here and say that I want to revert that to sum now we can easily compare side by side the total sales for us and the sales for our competitor and I can turn that into the standard currency format also okay looks pretty good if you haven't done so already make sure that you have generated a date key column in our sales totals table then make sure that you have related that information to our own dates table then you'll want to grab that sales data and place it in a visual and if you run into problems with the sales field not showing up in the right area then you might need to go back and reformat that sales column so that it's actually being treated like a number when you've done that come on back for the next example of a complicated relationship we're gonna go ahead and generate some extra data so what we're gonna do is we're gonna take a look at some information on our fact sales table about when a payment was actually due so let's say when each transaction happens in our fact sales table there's a date key associated with that data but we want to start to create payment due dates and we want to put that in our fact sales table as well let's go ahead and just generate a little bit of extra data for this next exercise we're gonna want to switch to data mode and take a look at our fact sales table now we do already have our date keys but we're gonna take a look at a scenario in which we don't want to analyze just by date key but we also want to be able to analyze by a due date a payment due date let's go ahead and create a new column and we will generate a column of payment due dates I'm going to go ahead and call this call on payment due date and that's gonna be pretty easy it's just gonna be our fact sales date key plus 15 days if you haven't done so already pause the video and make sure that you've gone over your fact sales table and created a new column call it the payment due date and it's gonna take the existing date key and add 15 days to it now what we're gonna want to do is we're gonna want to be able to periodically analyze not by the date key in the dates table by the payment due date now if we switch over to relationships mode let's take a look at what this is really going to mean for us I'm going to grab my facts table and move it up a little bit now we do want to be able to analyze by the date key to date key relationship that we have here but we also want to be able to do an analysis using the payment due date column that we have now created in here if we create another relationship here watch what happens I'm gonna grab payment due date and drag it and drop it on top of date key in the dates table when I do this if you look really closely you'll see that a secondary relationship was created here but it's a relationship with a dotted line that means that it is an inactive relationship now if I wanted to I could make this relationship active if I really wanted to all the time but I don't really want to do that because most of the time we're gonna use the date key relationship it's just that every once in a while I do want to be able to use the payment due date relationship as well so what I can do is create a measure that specifically uses this relationship that we've just created let's go ahead and do that all right I'm gonna switch back to our report view so that we can see this in action let's take out the information for our competitor because we're no longer analyzing that we are however analyzing our own total sales information what we're gonna do is we're gonna create a brand new measure here in our fact sales table using our friendly calculate function the calculate function is gonna be able to use a another helper function called use relationship and this is gonna allow us to actually use that inactive relationship there let's go ahead and create a new measure called total sales by due date and we're gonna use our calculate function here we're gonna do our normal total sales calculation but with one small change we're gonna use the relationship that we've just established here's how use this function we're gonna specify the column on the fact sales table that actually has the data used in that relationship so that happens to be that column that we created a few minutes ago called payment due date and that connects to our date table and it connects straight to the dates key on there the that's it that's all the users relationship function requires it just requires you to name the columns in both tables that actually create the relationship there I'm gonna close out the parens for my use relationship function and I'm also gonna close them out for my calculate function go ahead and execute that and then let's put it in our visual to see it in action looks pretty good if you haven't done so already try this out in your fact sales table create another column of data that populates the due date which would be the date key plus 15 days then create a relationship between your payment due date and your dates table then we created a measure called total sales by due date that uses a calculate function with the use relationship helper function go ahead and give that a shot and come on back for this last example of complicated relationships we're gonna deal with a problem with many to many relationships typically many to many relationships are not handled by data modeling technologies very well and power bi isn't really an exception for that we're gonna run into a problem here in that we have colors listed on our products table for each one of our products but when we offer the product selections to our customers we send them a swatch and that swatch has all the colors in a particular theme so we actually pay for swatches by a collection of themes we really want to know when we sell a product to somebody we send them a swatch for that entire theme and we want to determine how many theme swatches are we sending out for each one of the products that contain a color on that particular theme okay this will make a little bit more sense as we go we're gonna need to query a little bit more information for this because it just so happens that our swatch data comes from the swatch manufacturer and we're gonna go and grab the data from the swatch manufacturer so we're gonna go ahead and get data and this is housed in Excel and you'll see that there is one more file in your day three work files called many-to-many go ahead and open that and in here you'll see a table called colors now you'll notice with our colors that we have colors themes and in each color theme we have multiple colors in that particular theme additionally a single color could be in multiple different themes we're gonna have a little bit of trouble when we try and relate the color names here to the color names on our product tables because as you can see here each one of these colors is listed many times let's just go ahead and edit this on its way in because we're gonna need to address the problem of our headers here so let's go ahead and do that real quick click on edit and in the query editor we'll go ahead and use our first row as headers okay let's go ahead and close an apply and let's actually see the problem that we're gonna have here let's go ahead and just try and create the relationship like normal if I switch to relationships mode and go find my new colors table I'm going to need to relate the colors information to our product information I'm going to zoom in a little bit now our product table has color name and our colors table also has color names if I try and relate color name to color name we're gonna run into a problem here because you can't create a relationship between these two columns because one of the columns must have unique values we've already seen this error before but in the last example we were able to quickly and easily generate a false ID column or a temporary ID column that served as a bridge between these two tables however in this case we really can't do that because we don't have any ability to ID a color and somehow differentiate it from the different themes that same color might be in that's just isn't going to work so what we're going to do is we're going to create a bridge table now this query here that brought in our colors table we can really easily edit that query a little bit to be able to isolate the colors and if we just take the colors out and remove all the duplicates so that we only have unique colors being listed then we could easily relate those colors both to the product table and to the colors table so let's start by actually creating our bridge table here we're going to need to get back into our query editor so I'm going to click on edit queries edit queries now in our colors table I'm going to right-click on this colors table and choose to reference it for a new query now this is gonna be my colors unique table so I'm gonna take a second really quick to just rename this really quick here are my colors unique you will also see people name this a colors bridge or just bridge now the only thing I need left in here is just the color names so I'm going to go ahead and right click on color names and say I want to remove other columns what that's gonna do is just maintain this one column there now that we've done that we will also need to remove duplicates from this column we can easily remove all the duplicates from this column by simply right-clicking on the column and choosing the command called remove duplicates now you see that I have a short list of just my unique values this is gonna make for a great bridge table all right I'm gonna click on close and apply and we'll need to find our colors table and we might be even so lucky as to have our colors unique table immediately create its own relationships between the product table and the colors table if not you can go ahead and click and drag to create those relationships yourself now it all might look all well and good and it does look pretty good but I'm not quite in the clear yet you'll see that if I try and actually create a report that uses the color themes for my colors table and analyzes maybe something like the total sales or or maybe we'll get a count of all of our colors listed there let's go ahead and try that out on the colors table I'll grab my color themes and I want to count all the times that a color from each one of those themes is listed in the product table so first of all the problem that I'm having is that my color names are being placed in the rows that doesn't quite work so I'll place them in the values instead and now we'll try and count them okay even when we're counting though looking at this I can tell that something is very wrong and anytime I see numbers duplicating themselves over and over and over again I know that it's probably a relationship problem and indeed it is if I go back to relationships mode and we look really closely you'll notice that I have a normal one-to-many relationship going from my colors unique table to my product table as indicated by the icons on either side of the relationship I have a one-to-many that's great what that means is that anything I select on the colors unique table that filter will flow down to the product table and affect all the results on the product table so what I need is for colors to flow down to colors unique to flow down to product but that's not what's happening here you'll see that I have the one-to-many going in the opposite direction what this means is that my colors table is not being able to filter my colors unique table at all now it's actually really easy fix for this what we need to do is simply create a measure that actually uses this relationship right here and it's as simple as using our friendly calculate we're going to need to create a new measure I'm going to go ahead and put this on our fact sales table because that's where we've been putting all of our measures and maybe this time we want to do a count of the sales quantity so we know exactly how many swatches were sending out so each time a product is sold the color theme that that product color might be on maybe it's actually on two different themes a swatch is sent out for each one of those and so we want to be able to calculate the quantity that's being sold and determine how many swatches were sending out so here's our count of sales quantity here all right so I'm going to start with my friend Lee calculate function and this function is going to add up we haven't created a measure to count the sales quantity yet so we're going to add up the fact sales sales quantity there okay we are currently closing the parens for our sum function but not for the calculate function the calculate functions second argument is usually where we can determine a filter criteria however it also has this really nifty trick where you can just specify a table that you want to filter by that's all that you need so I'll go ahead and execute that and then we'll grab this measure that we just made place it on the same visual and suddenly it works great I'm going to switch this from being color named to sales quantities so that were not being confused by that we'll grab sales quantity and put it on the same visual our sales quantity is not working our count of sales quantity or I guess that should be sum of sales quantity huh anyway our sum or count of sales quantity measure that we made over there that actually is working and it's thanks to our friend Lee calculate function all right if you haven't done so already you'll want to take a moment to create a function that will actually utilize your colors table that you created there and for that you will need the calculate function and you're gonna use it to sum or I guess let's have it count since we called account of sales quantity all right so that's going to count the sales quantities that are listed and then it is going to filter it by the colors table you'll want to make sure that you are able to actually execute this complicated relationship solution and then congratulations you have now finished day three of the power bi bootcamp see you for day four all right welcome back this is the beginning of day four of our power bi bootcamp it is also the second day of our mastering class and we are going to cover three really important topics today we're gonna start by covering some concepts that are going to lead you towards mastery of power bi and data modeling in general that is discussing how filters work and the concepts behind how data modeling handles data and filters the resulting downstream tables of data following that we're going to take a look at the utilization of disconnected tables so why would you want to have tables of data that weren't actually connected to your data model through a relationship there are some reasons to create tables like this and we'll talk about what the advantages are once you know what the advantages are of course you'll also be taking home the reasons why lakhs of relationships or unworking relationships also cause problems which is a continuation of the topic that we discussed yesterday complicated relationships and finally today we are going to be customizing visuals using the custom visuals that are available in the power bi online community and we are also going to be customizing some dashboards so that we can program them for specific use by specific people and so that we can share them as easily as possible and a few other mechanisms that are going to allow us to design for consumption let's get started we're gonna start with one of the most complex but one of the most important skills you will want to achieve and this is a conceptual skill understanding how a pivot table or any other visual applies inherent filters for instance when we're looking at this pivot table data right here well notice that the grand total is being placed right there some of this conceptual stuff is going to seem rather simple but it's really important to go over and it will lead to your mastery of data modeling we have our grand total here at the bottom but then above that we have our data sliced or segmented into sections of the data so if you think about this this number at the bottom represents every single sale that is on the fact sales table all together and added together and then this one single number is being represented that's the total of all those numbers above this we are seeing that our data is being segmented out by a particular category but if you've looked at the fact sales table the product category information which is the row labels that you're seeing here that product category information isn't necessarily on the fact sales table and it doesn't have to be that information is on a uphill table from fact sales our fact sales table which is also commonly referred to as a data table because it has all of the transaction and all the historical figures it has all the data on it it is the most downstream table in our data set which means that there are a lot of upstream tables that are able to affect the fact sales table for instance if we apply a filter on the product table that filter that we've applied on the product table is also going to inherently filter out the fact sales table so let's talk about this when we are creating a visual and we want that visual to spread out the total sales by product category how does our pivot table or any other kind of a visual actually figure out what that slice of the data should amount to the way that it's able to do this it every single cell that has a measure in it is using coordinates from the pivot table or from the visual to determine how it's supposed to slice the information for instance here we have a label called audio that means that the total sales information here is going to be sliced by audio audio is on our category table so our data model must somehow figure out how to take just the audio slice of the category table and send that all the way downstream to filter the fact sales table and this is how it works if we select one particular category from the category table we'll take the example of audio audio translates to three different sub categories it has three different sub categories and the three different sub categories if you care if they are Bluetooth headphones and mp3s and mp4s and recording pens if we slice the category table and then wanted to analyze anything on the subcategory table all that's gonna be left on the subcategory table is items that specifically match the audio category meaning while your pivot table or other visual is processing the calculations that it's going to display for you it literally you can think about it like this to make it easier to understand it literally filters all the downstream tables and then once everything is filtered it actually does the arithmetic so so far we've chosen the audio category in the category table which has inherently filtered out the subcategory table for only sub categories that have to do with audio that flows further downstream to send all of those sub categories that had to do with audio down to the products table which means that the products table is being filtered by all the items that match the sub categories that were left on the subcategory table so now we just have a portion of our products table left and then whatever's on the products table can of course flow downstream to filter our fact sales table so the way that this works is once we've actually chosen a slice of our data like audio it flows through all of the downstream tables until it lands in the data table in this case our fact sales table and then you'll see this bright yellow portion at the bottom is all that's left that had to do with audio now that that table has been completely filtered out that the only answers it sees are audio now it can actually do the arithmetic once that arithmetic is performed that arithmetic is then presented on the pivot table in that cell where the measure was placed one more important thing to understand about this is that this means that all of your filters flow down stream a filter applied to your highest table will naturally flow all the way down into your lowest table assuming you have one-to-many relationships all the way along the way you'll see we have a one-to-many here 1/2 asterisk indicates one-to-many and a one-to-many here and a one-to-many here that means that once we are left on our fact sales table we are only left with the information that was filtered out of our product category table once we understand the order of operations or the order in which a measure is processed then we can start to understand why sometimes things go wrong when we start to design our own measures in the real world you're gonna find that really often you'll run into a snag or two and you'll want to be able to troubleshoot that snag using this order of operations will help you cure a lot of those snag issues I'm gonna use the visual aids that are on the right hand side of this slide to demonstrate the order of operations but remember even though I'm using a pivot table here this same principle all of these principles go for any visual that you'll use our very first item in our order of operations is detecting the pivot coordinates now remember I am using a pivot table so I'm talking about pivot coordinates but that would also be true for any other type of visually that you'll use let's explain what we mean by coordinates if we were to just use the example of the measure that is returned for audio for total sales then the pivot coordinates that we would be looking at here would be audio and total sales and maybe if I've placed something in the columns I'll take that into account as well however so far I only have a row label called audio and I do have the measure that I'm using called total sales after I've detected the pivot coordinates we now apply those coordinates filters to each respective table which means that each coordinate actually acts as a filter to filter out a table of data for instance in our category table we had a chunk in our category table called audio we are now going to slice the category table for just audio and then of course we know that that filter that's place there is gonna flow all the way downstream all the way through our subcategory table and our products table and into our FAQ sales table and that's step three filters flow down relationships so we've filtered out audio and that's flowing all the way down through all of our down stream relationships now that we've actually applied all the filters now we can do the arithmetic so it depends on what arithmetic we're actually trying to do in this case we're looking at total sales and that total sales information just happens to be in a column on the fact sales table and so we can actually apply the arithmetic there it just so happens that the measure that we design sums up the information in the fact sales table and that's the arithmetic that we're doing there and then the result is sent back to the pivot table and actually displayed in this one little spot here it's helpful to think about these order of operations as actually happening step by step in every single measure that you ever create and then after it's done audio it'll move along two cameras and camcorders and complete the exact same process now there is one very important interruption that we can use in this process after we've actually detected the pivot coordinates we can stop the process before we actually apply the coordinate filters to each table there gonna be a lot of cases in which you have implicit filters being applied by either a visual or by a pivot or some other kind of a table here and you don't actually want it to completely pay attention to just this item for instance maybe you were trying to do a calculation that compares just audio sales to the entirety of all the sales well unless all the arithmetic can happen in this one little cell the arithmetic stock happen so this one little cell here has to be able to simultaneously understand what the total sales are for audio and what the total sales are for everything but the total sales for everything aren't in this cell up here they're down here and so what you're gonna have to do is create a measure that actually ignores audio so that it can consider everything and then it can compare audio to everything and our calculate function is exactly what does that this is one of the reasons why the calculate function is one of the most important functions that you will ever learn in your power pivot data modeling or in your power bi data modeling features we are going to be using the calculate function multiple times again today in order to disrupt existing implicit filters on either a pivot or any other kind of visual if you haven't already done so take a moment to open the file called data model that is a power bi file and you'll find it in your student files inside the folder called day for work files in day for work files you have a file called data model and this is what we're going to start working on today in your data model file please go to the page that is called using all we're gonna use the function called all in order to intentionally negate some implicit structural filters that are placed either in a table or on a visual for this first example we're going to want to figure out what percent of all sales is by a particular category so we'll start with the example of audio so for audio we would want to be able to see a percentage that showed the portion of actual audio sales divided over all of the sales for all of the product categories in order to do this we're going to have to get this one space in our visual or this one space in our table to be able to simultaneously consider the total sales for just audio and at the same time be able to understand what the total sales is for all the product categories in order to do this we're gonna need to use our measure called total sales that will figure out just the one for audio but we're gonna need to create a special measure called total sales of all the products so that we can then divide the regular total sales over the total sales of all the products so let's go ahead and create a new measure here I'm going to click on new measure and create a new measure now remember you can also do this by right-clicking on any table that you want to place your measure in like fact sales and go ahead and create a new measure there we're gonna call this measure total sales all product categories now this is going to use our friendly calculate function and our calculate function is simply going to use our standard total sales measure that we've already created much earlier in this class except instead of adding an extra filter we're gonna negate an existing filter using the all function so I'm gonna go ahead and type in all and then all I need to do is just mention the table name that I want to clear all the filters from now in this case my sales information is all the way down on my fact sales data but my product categories are being chosen way up on my product categories table so I am gonna have a couple of options I recommend using which ever field you are actually going to be pulling into your visual now in these visuals here we are actually retrieving information from each table where this product category information is located so the product category name we fetched that from the product category table so I'm going to go ahead and just mention the product category table and I don't even need to specify a field there I can if I want to but I don't need to and I can just say clear all the filters from the product category table all right looks good I just need one more closing parens to close out my calculate function and I'll go ahead and execute that if we were to find this measure which we called total sales all product categories and place it on the same visual that we already have here it should look like total sales all product categories is constantly the same number now in this case that's exactly what we want if you haven't done so already now's a really good time to make sure that you've created a measure called total sales all product categories we're using the calculate function and just doing our normal total sales measure but we're creating a filter criteria or you could kind of think about it like it's negating a filter and we're going to use The Awl function for that and that's going to remove any filters that are placed on that product category table and then all we did is we just placed it on this visual so that we could actually see it in action if you would prefer you can also place the measure in a card visual I'll do that for both of the measures that we're focusing on right now once you have completed this total sales all product categories now what we can do is we can actually compare our normal total sales to this fancy total sales that's going to negate any filter criteria and just consider all of the sales altogether so let's go ahead and do that we're gonna create one more measure and place it on our fact sales table and we can call this percent of total sales now this is going to be our regular total sales measure over the neat one that we just made which is total sales all product categories if you haven't done so already now's a good time to pause the video and make sure that you have created the measure that you see in my formula bar when you're done you'll want to take a moment to just switch over to your modeling tab real quick click on your measure and just make sure that you format it now if you're getting the gray bars you can just click on any other measure and click back on your total sales or percent of total sales measure sometimes power bi does that hopefully it will get fixed in one of the most and one of the upcoming updates all right let's see what that looks like now it doesn't look very good on my visual here I'm having a huge problem with this visual and that is that this is actually a percent series so of course that numbers not gonna seem very big so a percent series usually you want to place it on a visual all by itself or alternatively you do have the option of using one of these column and line chart combined chart styles in which case you could put a percent of total sales into the line values ensures that it is calculated on a secondary axis now in this case this chart isn't very meaningful I would probably want to put a percent of total sales measure on its own chart all by itself and then I will segment this by our different product categories which I have in my product category table and I'll grab product category name and drop it in there that might be a little bit more meaningful to us put on a couple data labels and decision makers are gonna love this let's make this a little bit more interesting I'm gonna delete this visual on the upper right-hand side so I can make a little bit more room for more data in my table over here instead of just having product categories in here I also want to have the product subcategories so I'm going to go down to the product subcategory table and find the product subcategory name there now I'm gonna need to change the order here because it will put it in third so I'm gonna drag that and drop it so now I have a product category name product subcategory name and then total sales nothing exciting quite yet let's go find the measure that we just made and see if it works with this if we grab percent of total sales and drag it and drop it in the values field area you'll see that all of a sudden we start seeing a lot of 100 percents so let's kind of break down and see why this is happening let's just make a couple of observations and then we'll follow our order of operations to figure out why this measure isn't working in our first item in the order of operations we detect the coordinates of the measure location on the visual next we're gonna apply the coordinates filters to each respective table so we'll see that we have a percent of total sales measure here and we're also filtering this by audio and then we're also filtering it by Bluetooth headphones but as we know the calculate function can hop in there and actually get it to negate some of these filters which is good because if these filters stay then of course over 42 million change is gonna be a hundred percent so we need to make sure that both of our filtering criteria are actually taking place where we want them to take place and not taking place where we don't want them to take place for instance our percent of total sales measure takes into account the total sales all product categories measure that we designed if we go and look at our total sales all product categories measure that clears all the filters from the product category table okay let's go look at our data model really quick if we negate filters on the category table and then reapply filters on the subcategory table then the most downstream filters which are the filters on the subcategory table are going to persist all the way down to the fact sales table meaning in our visual here our number our measure is clearing out all the product categories but then after that it actually continues to filter for just Bluetooth headphones so of course the result by our percent of total sales measure which is using our total sales all product categories the result of our total sales all product categories measure here is happening before we actually reapply the product subcategory meaning the calculate function is applying too high of a table meaning we need to redesign a measure that actually intentionally negates the filters that are on the product subcategory table in this case it's not enough to just negate all the filters on the product category table cuz right here we can see that an additional filter is being applied to the product subcategory table okay let's go ahead and see this in action we're gonna need to grab our total sales product categories measure and just recreate one that is total sales all product subcategories measure so you can copy and paste it if you'd like but we are gonna have to make a couple of changes so I'm gonna walk through it item by item you can follow me if you'd like or you can copy and paste it and try it on your own I'm going to click on new measure and we're gonna call this total sales all product subcategories and this is going to use our calculate function of course and take into account our total sales except it's going to clear all the filters that are on the product subcategory table okay this should fix our problem and now all we need to do is modify our percent of total sales measure to create one that says percent of all parent category sales because if you think about it this particular measure is only clearing all the product subcategories and all that means is that any existing product category will stay but our product subcategories will disappear meaning it will tell us what the percentages of the parent category in this case of audio all right so we have one more measure to create in order to actually get the percentage one more measure and we'll call this percent total sales of parent category this is going to take our total sales and divide it over that measure that we just made which was total sales all product subcategories let's try that one instead and see if it looks better okay looks a little bit better now of course you will want to modify this that it's actually a percentage and then it should look perfect I'm going to remove the percent of total sales off of my visual and that will continue to update those numbers a little bit there for this next example let's go ahead and start ourselves a brand new page on this page we want to do an analysis that is able to determine the total sales for things that are low promo and determine the total sales for things that are high promo so anything that we consider high promo we want to be able to see see all the sales for that and we've decided ahead of time that anything that's high promo might be greater than a 10% promotion so we're gonna need to start by creating a measure that actually figures out what the average promotion discount might be of a selection of our data so we're gonna go ahead and do that really quick on the promotion table you'll see that there is a field called discount percent and if we switch over into data mode we might get a better view of it so we do have a lot of discount percents a lot of them do happen to be above ten but some of them are below ten and we have decided that anything above ten is generally considered a high promo and anything below ten is considered low promo well we want to be able to do is take a large selection of our data that maybe isn't listed out promotion at all maybe we're looking at a bunch of other information about our product categories we want to be able to see for everything that we sell in audio how much of those sales are greater than 10% and how much of those sales are less than 10% promotion so we're going to need to create a measure that actually looks at all the discount percents of any selection of our data and is able to create an average once we've actually created an average then we can use that average to actually compare it to the 10% or to see if it's greater than that or it's less than that so let's start by creating those couple of measures I'm gonna right click on the promotion table and create a new measure we're gonna call this measure average promo and this is going to be a simple measure it's just gonna be an average of our promotion tables discount percent which should be right there now let's go back and build ourselves a little visual so we can actually see this in action let's say we want to be able to look at all of our product categories I'm gonna grab my product category name field and drop it on the canvas and just to start off with let's just grab our basic total sales measure our total sales measure so we have something to start off with and I'm going to convert this into a clustered column chart now that I have the total sales I do want to be able to see what portion of the total sales are low promo and what portion of the total sales are high promo so I'm going to go ahead and create a new measure here I'm gonna place this measure on the fact sales table we did create the last measure on the promotion table that's because it specifically had to do with promotion data it actually doesn't really matter where you put your measures so I recommend that for the most part you keep all your measures in the same place to keep it simple for you so I'm going to right click on the fact sales table and we're going to create a new measure here all right so we're gonna call this total sales low promo and as you might suspect I'm going to use the calculate our total sales but this time I'm going to use a filter criteria that says I only want to calculate this if the average promo is less than 0.1 okay I'll close out my friends like normal I'll go to execute it but I'm gonna have a problem I'll get a little message that pops up that says a function calculate has been used in a true/false expression that is used as a table filter expression this is not allowed I'll tell you exactly what's happening here that's causing the problem we are comparing a measure in a true or false evaluation that greater sign just tests to see whether a particular number is greater than or less than that number and the only answer to that could be true or false yes or no so anytime you use your greater than or less than symbols you're essentially performing a true or false operation now a true or false expression can't be used with measures not in regular calculate in order to fix this you're going to need to use your filter function your filter function is a rescue function for calculate in this case all I'm gonna do is put in filter now the filter function does need to know which table it's gonna be filtering here so I'm just gonna say okay we are going to filter everything on the promotion table if the average promo is less than 10% all right now I've opened two parens but I've only closed one paren so I'm gonna need to close that second one and when you execute that that should work fine let's go ahead and see if it worked we're looking for total sales low promo I'm gonna drag it and drop it on my visual okay looks pretty good if you haven't done so already now is a good time to pause the video and go ahead and create this calculate function the challenge that we ran into is that we had a measure that was looking at the discount percentages in our promotion table but we were asking that measure to be compared as less than or greater than of regular number and your regular calculate function can't really handle that and so we're gonna need to use a filter function which is a helper function that's specifically going to filter out your promotion table based on that logical test when you're done go ahead and try to create your total sales high promo it's gonna be basically exactly like this except we're just gonna ask if the average promo is greater than or equal to 10% go ahead and give it a shot now if you have added your total sales high promo to the same visual it should look roughly like mine now in this case I probably would want to do a stacked column chart for this and then get rid of the total sales measure so I'm gonna get rid of total sales all by itself and then I'm gonna stack low promo and high promo I'll probably want to make sure low promo is on the bottom and then I could put data labels on here to specify each one of these that at least gives my decision-makers an indicator of how much this totals out - if you really do want them to be able to see total sales you're not going to want to put it on the visual but you could easily put total sales in the tooltips and that way when you hover over one of these items you will still see the total sales for everything and then you'll also see a high promo and then if you hover over low promo you'll see that as well well done make sure that both of your measures work that we just created high promo and low promo and then come on back and we'll continue for this next example I'm gonna have us once again start from a completely blank page for this next analysis we're going to want to look at our total sales information and we want to analyze all of our different product categories but I do want to see this on a table and the reason that I want to see this on a table is because we're not just gonna look at our total sales information we're gonna look at our total sales as it could be expressed in Euros and then for a challenge you can also try and do this in the Chinese yen I'm gonna change my text so that it's a little bit bigger here so I'm gonna go to my format options for my table go to general and I'll change that text to 14 right now we're seeing the total sales for each product category and that's great but if we wanted to create another measure that was able to take our total sales and divide it over some kind of a conversion rate then we could also end up seeing the results of these total sales expressed instead in euro form so our first example is going to be to see this expressed in Euro form now the only problem is that the conversion rate from dollar to Euro does change on a regular basis if I hard-coded some kind of conversion rate from dollar to Euro then I'm gonna have to be going way back into my measure first of all remembering which measure it was remembering all the measures that are connected to it being really careful to change it and anything that's dependent upon it every single time the conversion rate changes that's too way too much work so what we're gonna do is we're gonna put a slicer on here that populates all different available conversion rates to choose from now what we're going to need to do in order to get a slicer like that is to actually get that kind of data into our data model so let's start there first we do not have any data in here that populates conversion rates so we're gonna need to make one on the Home tab of the ribbon in power bi you'll want to click on the command called enter data this is gonna allow us to basically design a table from scratch I'm gonna go ahead and enter in some different values in here and we could name this table USD per euro and we might have conversion rates like 1 and 1.1 and 1.2 1.3 1.4 all right we'll stop there you get the idea and we're gonna name this table USD per euro okay and load it now that we've created this table called USD per euro we can now take the USD per euro information and put it on our canvas and turn it into a slicer now we have all of our different USD per euro conversion rates the only problem we're gonna have here is actually getting the numbers that are chosen on these slicers to be pulled into some kind of a measure now we can do this but the way that we do this is using what's called a harvest measure in order to create a harvest measure you'll want to create a measure like normal but we're going to need to create the measure on the USD per euro table I'm going to right-click on the USD per euro table and create a new measure and we'll call this euro conversion now when you are creating a measure there is no way to just simply select a value in a table you have to harvest the value that's in that table as you probably already know all the data that's contained within power bi or within power pivot it's not modifiable as individual values it's only modifiable or selectable or usable using measures or columns in this case what we're going to do is use a measure to select the item that's been chosen here now if you think about it item that's been chosen this has been chosen on a slicer and slicers are filters which means that we have now filtered out the USD per euro table for only the 1.00 value what that means is that all that's left on that table is one point zero zero so all we have to do is fetch whatever single piece of information is left on the USD per euro table we are going to need a function to do that though unfortunately we can't just say Euro conversion equals USD per euro you'll notice that we're not able to just type in the table name here we're gonna need to use a function around the outside and this is a pattern that you will see throughout power bi and throughout PowerPivot in excel you cannot have any naked measures naked measures are referred to as measures with no function in them whatsoever so measures must always have a function in this case it doesn't really matter which of the common aggregation functions we use we could use average that would be fine because if you think about it there's only gonna be one value left on that table and so it's averaging up one number fine we could also use max because once again if you think about it we're only gonna be maxing one number doesn't matter we could use some it doesn't really matter which one you use I'm going to use max and then all I'm gonna do is say I want the USD per euro table and then the USD per euro column and if you want to check and see if this is working what you can do is you can grab your euro conversion measure that we just made and either put it in a card visual or put it in your table that would be fine too and then you should notice that when you select different things on your slicer whatever's in that card should also modify at the same time so now you can see that the harvest measure that we created is actually working great so now that the harvest measure is working all we need to do is pop this harvest measure into a measure that actually takes total sales and divides it over the conversion rate all right that's gonna be easy I'm gonna go to the fact sales table and create a new measure there this measure we can call total sales euro and all you should need is the total sales your regular total sales measure it that's actually our euro conversion measure euro conversion there it is okay let's look for total sales euro all right in our total sales euro measure I'm going to pop it into our table here and now we should see total sales and total sales euro and as I choose the different USD conversion rates I should see that the total sales euro is being modified to reflect the chosen conversion rate all right now it's time for practice so we've learned how to do this once what we're gonna do is we are going to do this one more time and you're gonna try and do it on your own when you come back I'm gonna have a example displayed here so that you can see what yours will look like if it's correct we're gonna create another one of these conversion tables except instead of Euro we're gonna do the Chinese yen which is also referred to as the renminbi not sure I'm pronouncing that correctly the different conversion rates might be something like zero point one zero point one five zero point two zero point two five and zero point three what you want to do is first of all take this data and enter it as new data this is going to create a separate table here now keep in mind this table never actually got related to the rest of our data and we want it that way we're intentionally using this disconnected table to be able to use it as a slicer that allows our end user to choose one of the inputs and then we harvest that choice by our end user we harvest that with a harvest measure and remember you're gonna need to create a harvest measure and that might look something like renminbi conversion which will equal max and then whatever your table name is and then whatever your column name is once you do that you will need to create a measure that actually takes the total sales and converts it into renminbi that might look like total sales renminbi and that would be your regular total sales measure divided by renminbi conversion now the one thing we did not do in the last examples actually change our total sales euro so it's no longer dollar signs okay we have dollar signs there that's not ideal so you're gonna want to change that one for total sales euro and then also see if you can change the measure that you create here in this lab change this to Chinese yen or renminbi alright and with that go ahead and give it a shot the lab results for the previous exercise should look something like this first of all we did need to convert the formatting here to Euro and then when you created your Chinese yen or a Redmond B calculation over here then you'll want to make sure to actually use that yen character additionally you should have a USD per yen or renminbi slicer over here that allows you to select the different options on this one I selected 0.15 and that is returning the result of 21 billion seven hundred and eight million and some odd change in there if you haven't done so already you'll want to make your USD per renminbi table we did that by using the enter data feature that's on the Home tab in power bi and we created a table by hand and entered in some conversion rates there we did also create a measure that harvested that data so if you haven't done so already you want to do all of the above and then come on back and we'll continue welcome back for this example I'm opening up a page that we worked on previously in this course that has a lot of visuals on it the reason I'm doing that is because we are going to make a quick observation about how the visuals interact with each other on a single page and then we're also going to talk about how to modify those interactions so I'm gonna start by taking a look at this pie chart that I have on the left hand side each piece of the pie corresponds to a particular product category if I click on one of the pieces of pie that selection of that piece of pie serves as a filter for all the other visuals on my page it's as though I'm actually clicking on the slicer item called audio if I click on that slice of the pie again I will turn off that filter and everything will return to normal if I wanted to instead use a slicer I might want to slice by Asia which turns out I'm already sliced by or I could turn that off by simply clicking on the slice that I already have on Asia so that's the basics of using the built-in filters that are part of each one of the visuals in power bi now just by the nature of each one of these visuals and that inner interactions each visual is actually interacting with all the other visuals on the page but sometimes you want to be able to turn this off for instance sometimes I want to be able to choose one of my product categories like audio and I do want to be able to slice my map visual by audio I don't want to be able to slice my clustered column chart visual by audio as well but my pie chart is gonna look rather silly over here since all that's left is audio that's the entire pie chart so maybe I want to be able to modify the interaction between the product category name slicer and the pie chart so that the product category name slicer actually has no effect on the pie chart and it stays like the normal pie chart to edit the interaction between any two or three or four visuals however many you want you're gonna go to the format tab of the ribbon in power bi all the way on the left hand side you'll find a command called edit interactions by clicking on that command you now have the ability to select a given visual like this slicer here if I select this slicer here I now have the option to choose how this interacts with any other visual on my page for instance I want to turn off the interaction between product category name and this pie chart over here so what I'm gonna do is find the little icon that just showed up above pie chart now currently it's being set to filter this visual but I want to turn off that interaction and so I'm gonna click on none now when I use this slicer over here I can slice by audio and affects all the other visuals on my page except for the one that I turned off so thankfully it's not actually slicing this pie chart and making it look a little silly let's say I want to be able to do this for another visual I want to be able to use this continent name slicer here I don't want to be able to choose a continent but when I choose one of these continents I only want to slice the pie chart and my time scale over here I don't want to actually slice the map because I still want to see a nice topical view of all the different regions that we sell in so what I can do is select this slicer now and then I can see its interactions with all the other items including its interaction with the map I'll go ahead and turn that back to none and you'll see that it turns off the effect that this slicer had on the map if you haven't done so already now is a great time to pause the video and try this out for yourself remember you're gonna need to go to the format tab of the ribbon in power bi and click on the button on the left hand side that says add edit interactions if you don't actually see the format tab it's because you haven't clicked on a visual so just start by clicking on one of your visuals any visual will do and then click on the format tab sometimes you're gonna need to take a report that you've designed and distribute it to a lot of people on occasion you're gonna need to be able to decide exactly who can see what type of data and so it's worth it to talk about row-level security now before I actually get into it I'm just gonna mention that this is a power bi Pro feature and so in order to get access to this and be able to utilize it online you are going to want to get power bi Pro but assuming that you have power bi Pro you can easily go to the modeling tab of the ribbon in power bi and on the right hand side you'll see a command group called security so row level security means that you're gonna be controlling exactly which rows of original data anybody can see the results of depending on what kind of roles they have so if somebody has a role that has to do with sales you might want to show them all the sales information across the entire company you might not want to show them payroll information for the entire company likewise somebody who's in HR probably needs to see all the payroll information but probably isn't going to need to see or analyze any of the sales information so what you can do is you can design specific roles that allow people to see certain things and the way that you're going to do that is you're gonna click on manage roles I'm gonna create an example let's say I'm gonna be sending this out and I want people in the United States to be able to see the United States data and then I want people in a particular country in Europe to be able to see European data and then I want people in a particular country in Asia to be able to see the Asia data what I'm going to do is click on create and then I can create a role like we'll say us or better yet we'll say this is the role for anybody in North America and what I can do is click on any one of these tables like will say geography I'll right-click on geography and say I want to add a filter and I'm gonna add a filter for continent information now this is my continent number if you were here at the very beginning of class you'll know that that's just a continent number but down here I do have continent name I'll go ahead and click on continent name and then I can actually specify a continent name here so one of our continents is named North America and then I've designed a role for my North American users that can see all the information but only from the continent called North America I'll go ahead and click Save here and in a normal scenario I would probably create the rules now for everybody at the same time right now I'm still seeing it from a programmers perspective meaning I'm not looking at any of the roles I'm looking at all the data but if I do want to see it what its gonna look like if you were a North America user I would go to view as roles and then I would say I want to see it as a North America user and I'll click OK I'll give my report just a minute to be able to slice all the information and change the display and there we go so it looks like we're still seeing some European information and this is kind of interesting I know that what is happening here is that power bi is actually incorrectly understanding one of the city names that we have here so because we're using some of the city information we're actually still getting some data and it's popping up on our visual here there are lots of really great ways of working around this and it all depends on your data but you'll see on my slicer I only have the option to see North America data this is actually a really good time to take a look at your data and make sure you're not having any weird glitchy things happening like this is a great way to troubleshoot why don't you go ahead and give it a shot if you haven't done so already go to the modeling tab in power bi and create a role for somebody at your company and then you'll go ahead and create a filter there and then you'll want to make sure to try it out by clicking view as roles and then come on back our pages and our report are looking pretty good so far but there comes a time in which you might want to be able to display some data on some visuals that don't actually exist already so you'll see that we have these built-in visualizations over here on the right-hand side and we have explored a lot of them but maybe none of these visualizations actually work for the type of data that you have it's really important to know that there is an entire gallery online of a bunch of really neat custom visuals that other people have built we're gonna go there together we're gonna fetch a couple of the custom visuals and we're gonna show that on the video here and then after we've done that what you're gonna do is you're gonna pause the video and you're gonna download every single one that you think looks cool and try and figure it out I'm gonna go ahead and open up my browser and I'm gonna go to HTTP colon slash slash app dot power bi dot-com slash visuals it's important to know that there is an entire community online of people that are excited about power bi and learning about power bi just like you right here we're at the app power bi dot-com slash visuals but I would encourage you to also just go to google and type in power bi community there's an entire community here and we're all really supportive of each other particularly here in the visuals area we can see a bunch of visuals that other people have submitted to Microsoft and Microsoft has said hey that's pretty cool all that a lot of other people would like to use that too if you scroll down in the visuals you'll see lots of different types of visuals that you can use when you see one that you like all you have to do is click on it I'm gonna click on the one called enlighten aquarium when I choose enlighten aquarium I'll see the option to either download the visual or download a sample now it turns out that this is a really simple visual so it's actually gonna be really easy to use however if I download a more complex visual and no amount of me playing around with it is helping me figure out what to do with it and I highly encourage that you download the sample the sample is optional but what you're really gonna need in order to actually get the visual and use the visual is the button that says download visual so I'm gonna go ahead and click on download visual and you will get a message from Microsoft and you will want to read the whole thing I've read the whole thing before so I'm gonna go ahead and click on agree you'll see that my aquarium is now downloading and I'm using Google Chrome so that's why you'll see it download there in the bottom okay now that I have it downloaded all I have to do is return to power bi and then I actually need to pull it in to power bi this little dot dot dot in the lower right hand side of the visualizations pane this allows you to choose from a couple more options including import a custom visual I'm gonna go ahead and import a custom visual and you'll get another notice you will want to read that entire thing as well I've already read it before so I'm going to click on import your custom visual is going to be in your downloads folder so I'm gonna go ahead and click on my downloads folder and then I'm gonna choose this aquarium that I downloaded today and I'll click on open I'll get a message saying that that was successful go ahead and click OK and then over here in your visualizations pane in just a second we will see our aquarium icon pop up there we go there's my little fish okay let's say I want to take this pie chart here and I want to convert it to my aquarium visual I will just click on that visual and then convert it to an aquarium that was pretty easy right this particular aquarium visual is actually really easy to use all you have to do is put in some categorical piece of information under the fish well and then the fish size well is gonna require some calculable information the fish size is going to be determined by the total sales of each product category now I'm having a little bit of trouble determining which fish is which unfortunately with this visual there's not any built-in feature that allows you to put a label on the fish or under the fish or to the side of the fish but if you hover over the fish the fish will stop moving and then you'll get your tooltip that pops up and says this is the computers fish and this is the total sales amount of that all right now is a great time to pause the video and go online and find a visual that interests you you'll want to do this a few different times with a few different visuals keep in mind depending on the complexity of the visual you might very well need to actually download the example and see what kind of data is used for that for example if I wanted to use this tachometer here the designer of this has created a few different Wells and I don't actually know what those wells do so I'm gonna download a sample and then I'll actually be able to see what their data looks like what kind of dated their data is and that's gonna help me determine what kind of data of mine I can use to be able to utilize this same visual alright go ahead and give it a shot I recommend trying out at least three different types of visuals here pick the ones that jump out to you and I recommend trying to choose at least one custom slicer because you only get the one option in power bi alright go ahead and give it a shot and then come on back let's take a second to republish our data model we published our data model at the end of d2 at the end of our power bi essentials course but let's go ahead and republish it with all the new information keep in mind that if you want to update any changes that you've made in your file then you will need to republish it every time unless you set up something like a gateway let's go ahead and do that now now remember it might take a little bit of time when you're done you'll get that same notice letting you know that you are successful at publishing to power bi you'll get a couple of different links here the top link says open data model in power bi and that will allow you to actually jump right into your browser and open up power bi online great this is my report that is now online and if I click on the hamburger on the left hand side I'll be able to open up and see my various dashboards that I've created you might have only created one or two so far all of the reports that you've pulled online and all the datasets associated with all of your reports and dashboards let's take a moment to recall exactly how we might actually create a dashboard from this data in this report I'm going to start by hovering over one of my visuals and I'm gonna find the tact icon that shows up there I'm gonna go ahead and click on that and that's gonna allow me to pin it to a particular dashboard now I know that we've already created a dashboard but you have made a lot of modifications in this file so we might want to just create a new dashboard and you can name it whatever you'd like and then we'll go ahead and click on pin you'll want to take a second and pin at least a few different things onto this dashboard that you've just created now's a good time to pause the video and come on back after you've done that now that you've pinned some items to this great looking dashboard well actually want to go to the dashboard that you've just created and go ahead and click on it if you're asked if you want to save any changes in your report just go ahead and click on save as we've already discussed in one of the previous videos you will want to rearrange the items on your dashboard so that it's going to make the most sense to your viewers so take a second to just move around the different items on your dashboard so that it looks really good to you it's important to know that even though most of these visuals we designed in power bi you can actually take content that's in Excel and publish it to power bi it's not built with the exact same dynamicism and republish ability but it is important to know that you can do it in power bi on the upper right hand side you'll see this download icon by clicking on that download icon you'll be able to download some add-ins that are going to be very helpful for you first of all you can download power bi desktop now if you've been following along with the class you probably already have that however one thing that we haven't really talked about is a data gateway this is particularly useful if you need to have data regularly fetched from your computer or better yet from some kind of centralized data hub and get it pushed up to power bi online in that case you're not going to want to click on the publish button every time there's new data so what you can do is you can design a data gateway which is gonna allow that data to be pushed into power bi online without you needing to do it manually you can also download power bi from mobile and a power bi publisher for excel you also have a feature called analyze in Excel and you can get updates for that what we want is power bi publisher for excel if we click on that you'll be taken to a place where you can actually download this add-in now let's talk about what it actually looks like in excel in excel it'll show up as an extra tab of the ribbon and you'll see some options in here to pin items from Excel into power bi online you can even manage the things that you've chosen to pin and then you can open power bi you can connect to the data manage things about your profile and then update what I'm gonna do is choose this great pivot table I've designed in Excel and I'm gonna go ahead and pin it if you've already logged in to excel then you won't need to real aughh in but if you haven't logged in to excel then it will ask you for your credentials already logged in so I don't need to retype my credentials I'm just gonna choose to publish it to my workspace and I'm gonna pin it to an existing dashboard and it's the dashboard that we just created so I'm gonna go ahead and click OK when I return to my power bi dashboard if I just scroll to the bottom of all the items that I've pinned to this dashboard you'll see that I do have this pivot table preview that has been pinned to my dashboard now keep in mind it doesn't work like a normal pivot table but it is a nice little display of the results from that pivot table now when I'm making pivot tables I do a lot of KPIs and I create conditional formatting and I make those pivot tables really informative and so this is turned out to be an extremely utilities tool for me in dispensing information from pivot tables if you haven't done so already now is a great time to go ahead and try out that download and you can try out any of the other downloads here as well go ahead and give it a shot and come on back this is looking pretty good on my computer but what about if I wanted to look at it on my phone on the upper right-hand side of power bi online you can click on the dot-dot-dot and that will give you more options if I click there you'll see at the bottom it allows me to edit my view currently we're looking at it as though we're looking at on the web on a computer but I can switch that to the phone view now what's really neat about this is by clicking on phone view I can actually customize the design for what its gonna look like on my phone I'll go ahead and click continue here and now you'll see it's looking pretty basic what I had laid out on the web view was looking a lot better than it does on the mobile view so I'm just gonna go ahead and move around a couple of these items I'm gonna move my card visuals and I'm gonna move around a couple more items that it looks more obvious and presentable on a mobile phone once I've redesigned that for my phone now I can go ahead and look at it on my phone and see what its gonna look like from my mobile device I can click on my power bi app if you don't already have a power bi app just simply go to your App Store and choose the power bi application and you'll get it for your mobile device I'm going to go ahead and click on power bi there in the lower left hand corner and once I open that up I'll be able to see all of the dashboards that I've created I'm gonna go ahead and click on the item called great dashboard that's the dashboard that we just created by tapping on it I'll be able to see this great mobile design I designed in the last example it might take just a second for my mobile application to download all this information just like it would as if I were accessing this information from a normal computer I can scroll down in my mobile view to see different visuals and different metrics of my analysis if I want to see more information about one of my items like I want to see this line chart right here in the middle I can simply tap on that line chart and then I'll be able to see more information turning my mobile device to the side is also gonna show me a different view of the data than it would be by just holding it normally up and down I also have the ability to annotate these visuals in the lower right hand corner you'll see the item that looks like a pencil if I click on the pencil I'll be able to tap anywhere on the screen just like it says right there I can go ahead and choose my ink color and then I can say I can circle something and say great gonna look just about as good as the rest of my annotations that I do on my mobile device which is not so good so if I want to erase that I do have that eraser icon in the lower-right ish hand side and if I click on that I can just go ahead and erase some items including clear all annotations I'll go ahead and click on clear all annotations and that'll take that off but if I created some annotations or put a smiley face on here or something what I can do is actually share this with my one of my co-workers and I'll be able to go ahead and share this through either a text message or email or whatever I'd like finally whether we're on the mobile application or whether we're looking at it on the web we might want to be able to get some kind of an alert if any of our data crosses a particular threshold let's say for instance our total profit margin is currently fifty six point four eight percent but I want to set up a alert that lets me know if it goes over a particular threshold maybe our goal for next year is to get to sixty or something like that now I'm not going to be able to change my data enough to actually get that profit margin so we're gonna use an example like we'll say our our previous goal was to actually get above fifty five percent profit margin and you see that we've already done that but we're just gonna set up an alarm that already looks for fifty five percent just so that you can see how easy it is to set up and then you'll actually see it in action on most your visuals you'll have the ability to click on the dot-dot-dot item at the top right if you click on that it basically flips your tile around and you can see some more options that you have with this you will see an option that looks like an alarm bell and if you hover over it it will say manage alerts I'm gonna go ahead and click on that I currently have no alerts set so I'm going to add an alert rule right here I'll call that alert alert for a total profit margin that's totally fine and the condition is that it is above we'll say 55 percent so I'll need to put in 0.55 now you can set it up to have a maximum notification frequency which in lots of cases you're gonna want to be careful about that because if you have a flexible alert margin that you're going over on a regular basis you can get notified too often so usually we want to know if it is if it happens every day or so but you can set it up that you can get it once an hour okay I'll go ahead and click on save and close and once that data actually crosses the threshold I will get an alert not only here in power bi but even if I don't have power bi online open at the time so long as I am logged into my online profile I'll get a notification that pops up on my screen very well done congratulations on completing the power bi bootcamp on behalf of myself Chelsea Dolman and all of us here at learn it thank you so much for joining us it's been a pleasure we'll see you next time thanks for watching don't forget we also offer live classes and office applications professional development and private training visit learning comm for more details please remember to Like and subscribe and let us know your thoughts in the comments thank you for choosing learn it [Music] you

Channel: Learnit Training

Views: 262,927

Keywords: free Power BI tutorial, Power BI Master tutorial, Power BI, microsoft Power BI, learn Power BI, Power BI tips and tricks, Power BI tutorial, Power BI training, tips and tricks Power BI, Power BI how to, free Power BI training, Power BI free tutorial, Power BI tutorial free, free, how to use Power BI, e learning Power BI video

Id: i3wZRgx_gpY

Length: 155min 14sec (9314 seconds)

Published: Fri Apr 10 2020