Power BI Full Course Tutorial (8 Hours)

Hi everyone I'm Trish Connor Kato and I'd like to welcome you to Microsoft power bi this extensive course encompasses 13 modules covering the basics through some Advanced features in the application the first module will explore the meaning of data analytics and the different roles available in that space we'll outline the important roles and responsibilities of a data analyst as That as the role we'll be functioning in when we're working in the application we'll also explore the power bi licensing options and their implications on the landscape of the power bi portfolio of products and services foreign

module we'll get Hands-On in the power bi desktop application this module will explore identifying and retrieving data from various data sources You'll also learn the options for connectivity and data storage and understand the difference and performance implications of connecting directly to data versus importing it during this module module 3 is where the real work begins the module will teach you the process of profiling and understanding the condition of the data you will learn how to identify anomalies look at the size and shape of the data And perform the proper data cleaning and transforming steps to

prepare the data for loading into the model the next module will teach you the fundamental concepts of Designing and developing a data model for proper performance and scalability this module will also help you understand and Tackle many of the common data modeling issues including relationships security and performance module 5 will introduce you to the world Of Dax that's data analysis Expressions it's a function language that's used in power bi to create calculations you'll learn about aggregations and the concepts of measures calculated columns and tables and time intelligence functions to solve calculation and data analysis problems

we'll move on to optimizing model performance this is where you'll be introduced to steps processes Concepts and data modeling best practices Necessary to optimize a data model for Enterprise level performance module 7 introduces you to the fundamental concepts and principles of Designing and building a report including selecting the correct visuals designing a page layout and applying basic a critical functionality the important topic of Designing for accessibility is also covered with the exception of the first module all of the modules on this slide will be Conducted in the power bi desktop application once we get to

module 8 we'll begin working in the power bi service the online component of the application in this module you'll learn how to tell a compelling story through the use of dashboards and a different Navigation tools available you will be introduced to features and functionality and how to enhance dashboards for usability and insights The next module will teach you about paginated reports including what they are and how they fit into Power bi you will then learn how to build and publish a report the next module helps you apply additional features to enhance the report for analytical

insights in the data equipping you with the steps to use the report for actual data analysis you will also perform Advanced analytics using AI visuals on the report for even Deeper and meaningful Data Insights since we'll be working in the power bi service module 11 will introduce you to workspaces including how to create and manage them you will also learn how to share content including reports and dashboards and then how to learn how to distribute an app module 12 focuses on managing data sets in power bi in this module you will learn the concepts of

managing power bi Assets including data sets and workspaces you will also publish data sets to the power bi service then refresh and secure them the last module in this course is about row level security it will teach you the steps for implementing and configuring security and power bi to secure your power bi asset welcome to module 1 where we'll get started with Microsoft data analytics This is the only module in the course where I'll be using a PowerPoint slide presentation to give you background information on the field of data analytics the licensing options in power

bi and the products and services that will be available to you all subsequent modules will have you Hands-On in power bi you can access this PowerPoint presentation from the video description Below so let's get started with data analytics and Microsoft what is data analytics it can be described as the process of analyzing raw data to find Trends and answer questions a successful data analytics initiative will provide a clear picture of where you are where you have been and where you should go the field of data analytics is very Broad and expanding and as such there

are many roles that fit in that area there are also four primary types of data analytics descriptive diagnostic predictive and prescriptive there are supplemental slides in this presentation that will give you more depth on each of those four types there are also additional slides that will give you Insight on the wide variety of roles that are available in this field When we get Hands-On in power bi we'll be functioning in the role of a data analyst data analysts provide real-time insights across an organization and power bi that means the data analysts will connect to and

transform data with Advanced Data preparation capabilities they'll also create interactive data visualizations and uncover important insights the data analyst is typically the person Who publishes dashboards and shares insights to drive informed action throughout your organization it is important to understand the license options for power bi as your features may vary based on the type of license that you have power bi free version includes the power bi desktop application as well as the online power bi service a user with a free license can only use the power bi service to connect to data And create reports

and dashboards in the default workspace known as my workspace they cannot share content with others or publish content to any other workspaces they can however consume content that is shared with them as of this recording the power bi Pro license is estimated to be 9.99 a month with a pro license users can publish content to other workspaces in addition to my workspace they can share Dashboards subscribe to dashboards and reports and share with users who have a pro license they can also distribute content to users who have free licenses power bi premium licensing has two

variations the per user variation as of this recording is about twenty dollars a month can do all of the same things as the power bi Pro license but can also share with users who have a premium per User license in addition premium per user license holders can distribute content to users who have free and pro licenses the other variation is the power bi premium per capacity license can do all of the things as the premium per user Licensing in addition to more things it's usually implemented at the Enterprise level there is a Word document in

the video Description named website links and more information where you can view the power bi pricing and product comparison website to see how the feature sets differ by licensing level in this course I'm using a Premiere per user license and may have features on my screen that you do not have in your version depending on your license level the landscape of products and services in power bi is amazing depending on your licensing the feature Set varies but even with the free version you'll have access to a robust set of services the three main components of

power bi are the power bi desktop application the power bi service which is in the cloud and the power bi report Builder let's take a deeper look as a data analyst most of the time you will be working in power bi you will be working in the power bi desktop application From there you can connect to over 80 data sources transform your data analyze it shape and model your data you can create calculations called measures and calculated columns create visualizations and reports you can publish to the power bi service and have access to the power

query editor which can help you with your data transformation foreign the power bi service is cloud-based it allows you access to some data sources you can also create Visualizations and reports there you can create paginated reports and that is where you go to create your dashboards there is some overlap in what you can do between the desktop and the service as you can see on the slide here the two are bundled together and as I said earlier even the free version has a very robust feature set lastly the power bi report Builder allows you to

create paginated reports In the power bi service we'll explore paginated reports in a later module now that we've covered the background information let's get our feet wet in the power bi desktop yay we made it through the background information and from now on we'll be Hands-On you may want to pause and grab the five files on the slide from the video description I would suggest you put all of them in the same folder on your Computer before we load data into Power bi let's review the data that we are going to put in there I've

opened a sample Superstore Excel file that you grab from the video description so that we could kind of take a look at the data this file represents typical orders sales customer and products information spread over three sheet tabs on the order sheet tab you'll notice That there is additional information that's not related directly to the order for example customer name customer ID those fields really should reside separately from the order information on the sheet we'll learn how to address that issue later on in this course then we'll take a look at the returns tab and

that just has order IDs and The Returned status and we have a users tab which contains Manager information I'm going to go ahead and save and close this workbook and you'll see how this data comes into Power bi in just a few moments now that we've explored the sample Superstore Excel file we're going to get into Power bi there are many ways to launch it like any other application I'm going to use my start menu to launch it so I'm navigating down to my taskbar at the bottom of my screen and in the lower Left

hand corner I'm going to click on start once the start menu opens I'm going to click on any letter that I see so it collapses all the applications and I'm going to click on the letter p underneath the letter P you'll see all of the applications that begin with the letter P we're going to select power bi desktop to launch this application foreign the application is loading it really Doesn't take a long time and you'll notice that the application opens in the background with a splash screen on top of it the first thing we're going

to do is sign in in the middle of the splash screen you're going to click on the yellow get started button it will show you a prompt to enter your email address and another prompt for your password go ahead and follow the prompts and log Yourself in we'll do a comprehensive tour of the power bi desktop after we load the data in from sample Superstore but in the meantime if you look in the upper right hand corner you'll notice your name and that indicates that you're signed into the program if you want to take a

moment go ahead and click on your name and on that screen you would be able to sign out sign in as a different user view your Account and go to the power bi service which is the cloud-based component of this application the first lesson in this module is getting data from multiple sources we're going to use the sample Superstore Excel file as our first source of data we'll be using other data sources in this module as well on the Home tab of the ribbon you'll see the arrow pointing to the Excel workbook button go ahead

and click on that icon And it will launch an open dialog box navigate to wherever you put the class files for this module and you should see your sample Superstores file we're going to double click it or you could click it once and choose open you'll have a navigator window appear on your screen and it shows the three tables that were in that sample Superstore file as you select each table the preview Pane to the right will show and it shows a truncated size of the data but you can scroll through and see some of

the data that you saw in the Excel workbook we're going to put a check mark in front of every table and as you select a different table to preview pain fills with that table's information in the bottom right hand corner of the Navigator screen you have three buttons Cancel would be the same as doing the X in the upper right hand corner load and transform data this time around we're going to click on the load button you'll learn more about the transform data button later in this course when you click on load you'll see that

it's working and you'll notice a load box that will pop up letting you know the tables that it's working on and what it's doing you also had a yellow band briefly Across the top of the screen at this point it doesn't look like anything much has changed in your file before we get into a comprehensive tour of the power bi environment it'd probably be a good idea for us to go ahead and save the file power bi has a quick access toolbar similar to what you find in the other Microsoft applications so in the upper

left hand corner of the screen so there's a save icon undo and redo you Can't modify the toolbar at this point in power bi desktop like you can in the other office applications but we're going to go ahead and click that save icon so that we can save this file it should route you right into your working directory wherever that is on your computer in a save as dialog box let's name this file sample Superstore the same as the Excel file and you'll notice where it says save as type it's giving It a DOT pbix

extension there are only two extensions for power bi desktop files the default one is PBI X which is a power bi desktop file and your only other choice would be a DOT pbit which is a template file we're going to leave it on pbix and click save now we're ready for the grand tour of the environment you'll notice that it also has a title bar like every other window now that we've named the file it's called sample Superstore power bi Desktop before it was just saying Untitled you have a search function right at the very

top Center of your screen and again to your right you will see your name with all of your account information in that menu we have a ribbon just like in the other Microsoft programs the ribbon will change depending on what view you're in takes a little bit of getting used to but I'm sure you'll get comfortable with It and you'll see that play out very shortly I mentioned earlier there are three views in power bi desktop and the default view when you first log in and when you load data in is report view that's the

view we're looking at right now foreign buttons are on the left side of the screen like I said we're in report view right now and this is the view button Indicating report View the other two views that you have available are data View and modeling View you'll see both data and modeling view in just a few moments in the middle of your screen where it says build visuals with your data that is known as the canvas that whole blank area is the canvas if you look below and to the left of the canvas you'll see

that we are on page One in report View and you have page tabs that are very similar to Excel sheet tabs you can add pages delete them duplicate them and hide them and you'll see that during the course underneath your page tabs you'll see an area it's a gray area at the bottom every Microsoft program has it and it is called the status bar right now we have one page in this report and the stat as far as reflecting page one of one The status bar will populate with different information depending on what view you're

in in power bi desktop let's take a look at the right side of the screen it may look different on yours than it does mine but the right side of the screen has three different panels they're actually called panes my filters pane is collapsed right here so you just see it saying filters and it Has a leftward pointing Arrow which I can use to expand it report view comes with these three panes so there's the filters pane we'll speak more detail on it when we start working on creating visualizations in power bi you also have

a visualizations pane on the right side and Report view that's the area where you go to when you want to choose what kind of visualization you'd like to put on your report you have a host of options here and again we Will cover that area very thoroughly later in this course foreign the last remaining pain over there for right now is the fields pane well when you look in this Field's pane you'll see the instances of what were on the three sheet tabs in Excel so we have an orders table we have a returns table

and we have a user's table even though they're on sheets in Excel Once you bring the data into Power bi by connecting to it through get data or Excel workbook they are known as tables so you can expand the orders table all of the tables come in collapsed you can expand the orders table and then report view you see the fields that are in the table but not the data that's in the fields just to go over some symbols that you'll see in a field pane if you notice you have the sigma symbol In front

of customer ID as well as a couple of other fields that's an indication from Power bi that that field contains numeric data so whenever you see the sigma symbol it means the field contains numeric data if you notice the order date field has an expand arrow in front of it and when you expand it you'll see what's known as a date hierarchy notice the symbol in front of the hierarchy it's indicative of a hierarchy in power bi you'll learn About hierarchies later in the course but what I will say now a hierarchy is a container

of sorts for fields that you would like to kind of group together that's what it does so when I expand a date hierarchy I see that the order date has been broken down into year quarter month and day that's what's in the date hierarchy as we progress in the course and we do different actions in power bi you'll see new symbols in the fields pane if you'd Like to take a moment and look at the fields that are in the returns and the users table you'll notice when you expand the users table that it has

two columns and they're named column one and column two when you're working with data you're going to want to make sure that the column names are indicative of the data that's being stored in those columns in the next module you'll learn the Process of transforming and cleaning your data and that's where we will clean up that table let's go over to our view buttons now and we want to access the view that will allow us to see the data that's in the tables that we imported from sample Superstore so we're going to click on the

data view button on the left side of your screen it's the second view button and it will take us right into Data View When we get into Data view you'll notice that you have a new tab on your Ribbon column tools and as I said earlier the ribbon will change depending on what view you're in and it could change again depending on what you're doing in that particular view you'll get used to it after working in this environment for a while dataview also has the fields pane on the right and I can use it for

navigation purposes so if I'm looking for a Particular column I can just click on it in the fields Pane and in data view it will navigate to and select that column for me the other thing that's updated here is the status bar in the lower left corner you'll notice now that the status bar in data view is telling you what table you currently have selected in the fields Pane and how many rows are in that table it also is telling me because I happen to be in the profit column right now That in that column

there are over 8 900 distinct or unique values so depending on the view the status bar will show different things the last few that we're going to explore right now is called model view so make your way over to your view buttons and it is the third and last view button in the list go ahead and click the model view button to switch to that view we're going to have a deep dive into table relationships once we get to Module four what I will say about model view is that it will show a card for

every table that you've connected to from the outside data source which in our case is an Excel workbook we'll be creating table relationships in this View again in module 4. but in the meantime this view also has the fields pane on the right side of your screen and just like in the other views you can Expand or collapse the tables that are showing in the fields pane another thing is there is a search box at the top of the fields Pane and if you have a lot of tables it's very handy so you can just

search for the field that you're looking for you also have an additional pain that you haven't seen before in this View this is the properties Pane and again we'll do a deeper dive into this in a later module and explain all of the Choices that you have on the properties pane here this view also has page tabs down at the bottom you'll notice that there's a page tab there's only one initially and it's called all tables and it will put every table in your data onto this one tab in the form of a card when

you're working with data sets that have several groups of related tables you can create more pages in here and Put the grouped fields on a separate page so it's not as overwhelming the deal in this view as it could be with a lot of tables so you'll get more information about model view when we get to module 4 in this course one last thing and it's just terminology here the Excel workbook called sample Superstore is our data source once you bring the data into Power bi desktop it is known as a data set so we're

looking At the sample Superstore data set in power bi desktop what we want to do next is bring in data from an access database we don't want to mix the data together with the sample Superstore data so we're going to start a new instance of power bi desktop and then bring in data from an access database the access database is in the video description it is called Northwind so what I'm going to do to start a new Instance of power bi desktop what I want to do is I want to go up to the top

left corner and I want to click on the file tab of the ribbon when I'm on the file tab you have many options but that's one way of starting a new instance of power bi desktop sample Superstore will also remain open the new instance opens in its own separate window go ahead and click on the file tab of the ribbon and click on new So power bi will relaunch in its own separate window it will bring up an Untitled power bi desktop and it's like a separate file similar to having like a separate file in

word or Excel because we're already signed in before we brought in the data from sample Superstore our splash screen now looks a little bit different than it did when we first launched power bi desktop Let's take a few moments to review the splash screen I'll start on the left side we can start the process of bringing in data from the access database by using get data on the left we also have recent sources our sample superstore.pbix power bi desktop file is listed there if we're going to access that file a lot it has a push

pin we can pin it to the list if you're not going to want to access that file often to clear it from the List you can right click on it and you can choose remove from list if you had a lot of unpinned files listed here you could remove all of them at the same time you also have the ability to open other reports your desktop files are known as report files so the only one we have so far is sample superstore we don't have any others to open at this moment in the center of

the splash screen you have the ability to look at many videos To get information about power bi the one thing I will say is that they really have help everywhere that you could possibly look for it in power bi both in the desktop application and the power bi service online so this one has a lot of videos how to get started building reports they have a link down in the lower right corner saying view all videos and if they're more to show it takes you online and you have a whole host of videos that you

can Look at from Microsoft online I'm going to disclose the internet for that if you don't want the splash screen to show up when you launch power bi you can uncheck the box there I find it useful I can do a lot of things right from the splash screen on the right side of the splash screen in the upper right hand corner you have the X where you can close the splash screen and then it shows your username there are more help topics on the right Side power bi is constantly updating so you might want

to take some time and review what's new in power bi constantly updating you have forums where you can ask questions and get answers and also interact with other users in the power bi community you have access to the power bi blog that keep you up to date on the latest news things that are going on and a host Of tutorials I go to the blog and the forums and I'm a member of the power bi Community ninety percent of the questions that may come up for me about power bi I find the answers in those

vehicles now it's time for us to bring in data from the access database entitled Northwind that is in the video description we're going to click on the get data button on the left side of the splash Screen and it's going to open up a window for us and in this window at this time as of the recording of this video there are over 80 different data sources that you can bring into Power bi so on the left side of the get data screen it defaults to all and if you want to you can take a

look you can scroll down the left side and look at all the different data sources Now one thing I will say some of the ones on the right side excuse me it's not the left side it's the right side all of the ones on the right side that have the word beta after them I know I passed one so here's one app figures and in parentheses afterwards it has the word beta that means that power bi is testing out this particular data source and it may or may not work out sometimes you'll come in here

and things Are no longer on the list and new things are on the list so it's always looking to allow you the ability to bring in data from more data sources than it currently does so what we're looking for on the left side I'm going to click on other and I'm going to scroll back up to the top and you can see this is where you would find web and all kinds of stuff if you want to use categories On the left I'm going to now click on database an access database on my screen is

the second one from the top on the right hand side I can double click it or click it once and then at the bottom click the connect button it should bring up your working directory and there's only one database file in that directory called Northwind I'm going to go ahead and double click It so it's going to go through the process of connecting to the database and it will ultimately open a navigator window similar to the one that we saw when we brought in the Excel workbook sample Superstore there are differences however databases usually have

four different objects in them the tables are the only objects that hold the data but there could also be queries forms and reports There can be macros and modules as database objects so what it brings in here is it will bring in any queries and notice the icon for query it looks kind of like a double table icon so when you you'll get used to the different icons that you'll see in here but that icon represents a query in the database beneath all the queries you will see tables and that icon looks pretty much Like

a table so sometimes a database will have temporary tables in them we don't want to bring in the queries we don't want to bring in the temporary tables I'm going to scroll down and underneath the temporary tables there are seven other tables I'd like you to click the check mark in front of customers and you'll see that the preview is evaluating and you'll be able to see the data that's in that Customer's table in the Northwind database we're going to check the box in front of employees order details we're going to skip orders for a

moment check the boxes in front of products shippers and suppliers what's different about this Navigator window from the one we saw with Excel is the button in the lower left corner that Says select related tables because databases already have table relationships in them and because power bi can automatically detect those relationships if you forget to select a related table power bi has the capacity to do that for you so we're going to get a demo of this right now actually not a demo you're going to do it with me so that select related tables button

we didn't select the orders table on purpose go ahead and click select Related tables and it'll take a moment because it's looking through the relationships to see what other tables may be related to the ones that we've selected and when it gets done it will automatically select the orders table for us so the Northwind database file that we just brought in that we're bringing into Power bi already has table relationships in it and power bi is able to detect Those relationships my preview is still evaluating but that's fine we're going to go down and we're

going to use the load button again to bring the data from the database into our power bi desktop so you'll see that it's evaluating all of the tables that we selected it's creating a connection in the model loading data to the model and you have the yellow band at the top of the screen that was telling you that you had Unsaved changes but once it's done with the load process that yellow band disappears if you take a look to the right you'll see the seven tables that we imported in the fields pane let's go ahead

and save this file so we're going to go back up to the left to the quick access toolbar the first icon is save and we're going to call it Northwind it's still in your working directory and You can see the sample Superstore power bi desktop icon so get used to that icon in front of sample Superstore that's indicative of a power bi desktop file so I typed in Northwind as my file name and I'm going to click save so your title bar at the top of your screen will update with the name of the file

because the database that we just brought into Power bi has relationships in it we're going to start by looking at Model view so on the left side click your last view button and we'll go into model view so you'll see the relationships between tables here power bi has the ability to Auto detect relationships and it is a setting that you can change again we will do a deep dive into relationships once we get to module four but I just wanted you to see mostly that power bi has the ability to detect them when you Import

a database file or any other file where table relationships have been created I'm going to show you where to find a setting that allows power bi to do this we're going to access the file tab on the ribbon and when you get there you're going to go down to options and settings you will learn more detail about options and settings throughout this course I'm specifically bringing us here now to Talk about how to make sure that the ability to Auto detect relationships is on or off when you click on options and settings you get an

options link and a data source settings link we're going to click on the options gear so what I will say about options in power bi desktop is they come in two variety there are Global options which are applicable to every file that you would have open Empower bi desktop and Some of the global options are only in that category you also have another option down here another category and its current file so you have Global options and current file options which only apply to the file that you're working in in that moment this the option

we're looking for is under current file so we're going to go to data load under current file and on the right side of your screen You'll see a relationships heading under that heading you have three different check boxes two of which are always selected by default the one that controls its ability to bring in relationships upon data load is the first checkbox the third one which is also a default is auto detect new relationships after data is loaded and you'll see an example of when that happens Um later in this class I normally am in

the habit of for every file that I'm working on I also check the middle one to update or delete relationships when refreshing data and you'll learn more about refreshing data later in the course but for now my advice is to just have all three of those checked for every file that you're going to be working for working on if that is your intent to have power bi help with detecting relationships I'm going to go ahead and click the OK button to get out of options and once we're out of options we're going to go ahead

and save this Northwind file Again by using the save icon on the quick access toolbar and we're going to close the file a couple of ways of doing it I just usually close the whole window so I'm going to travel all the way to the other side of the screen pass my name and I'm going to click the x button If you attempt to close a file and you haven't saved it it will prompt you to save the changes just like any other application because we did file new sample Superstore is still open the file

for Northwind opened in its own separate window our next task is going to be bringing in data from the internet we're going to go ahead and close the sample Superstore file I'm going to use The X in the upper right hand corner of the window to close it if prompted save the changes there's a Word document that you grabbed from the video description called websites and more information and I'm going to bring that document up now before bringing the data into Power bi let's follow the link and go to the website so I'm going to

hover my mouse over the link hold down my control key click on the link And it will open the website for me so this is just off of Wikipedia most websites are designed with tables on them and a website designed this way you can typically bring in the data from the website since we're here there's no need to go back to the word document to get the URL I'm going to click at the top of the screen in the address bar and select the URL and I'm going to do control C on my keyboard to

copy it Now I'm going to minimize or even close I'm going to go ahead and close that website and I'm going to get the word document off of my screen since we closed all of the files in power bi desktop we're going to have to launch it again this time I'm going to launch it because I have it as a button on my taskbar I'm going to click on that button and launch the application so that we Can import the data from the Wikipedia site into Power bi we can do it from the splash screen

or we can use the ribbon this time I'm going to go to the upper right hand corner of the splash screen and close it we're going to go back to the get data button on the ribbon just like we did for the access database and we're going to use it to access web content go ahead and click the button And when the get data dialog box opens on the left side you're going to click the last category other on the right side under other web is the First Choice go ahead and double click that it'll

bring up the from web dialog box now you want to switch over to the word document that has website and additional information in it and I'm going to bring that up on my screen right now in this file we have a link for median Household income by state 2021 and let's take a look at the information on the internet so I'm going to just hold down my control key and click on the hyperlink it's going to open a web page and you see it has some sort of a visualization on it but most web pages

are built using tables so if we scroll down we'll eventually run into a table and that's probably what we're going to want to import into Power bi desktop Since we're here we don't have to go back to the word document to get the URL we're going to just click up at the top of the screen in the address bar and select the URL press Ctrl C to copy it and then you're going to switch back over to power bi desktop we're going to paste the URL into the box by using Ctrl V and then we're

going to click the OK Button on the right so it flashes on the screen that it's establishing a connection and then you may see a flash of a box that says it is connecting and when that process is over the Navigator window will open this Navigator is different than the one we saw for the Excel workbook and the one we saw for the access database after clicking OK when you paste it in the URL you may be directed to an access Web content screen that's asking you for Authentication some websites the first time you try

to bring in data from their site into Power bi it wants to know the authentication level so if you get this box you're going to leave it on Anonymous which means there's no credentials required to view the content on that site when the Navigator window displays you'll see the tables that it was able to pull from the site and you'll also See in the preview pane that you have another tab at the top this enables you to see the data as it appears on the web so right next to your table view tab which we're

used to seeing you have another tab that says web view as you click on the tables in here you can either see the data in table view or you can see what it looks like on the internet so we're going to start selecting tables and I want to preview them in table View Foreign just to view the data I do not have to check the check box in front of the table I can simply click on the table and see the data so table one looks like it has part of the data from this website

when I click on table 2 it has more parts of the data and the same goes for table three so I'm going to go back and select each table and if you'd like you can take a look at webview to see how the data displays on That website so we're scrolling down we were just on the site and it looks exactly as it does on the internet so it looks like it's bringing in the information from the table medium household income by state 2021. I'm going to go ahead and do the load button in the

lower right hand corner you'll see your load dialog box pop up letting you know it's evaluating the information it pretty much goes through the same Process regardless of the data source in most cases while it's evaluating in the load box you'll notice the yellow band at the top of the screen as you've seen before that will disappear once the data is loaded into Power bi desktop you will see the three tables on the right side of your screen in the fields pane let's go ahead and save this file and call it median household income This

time I'm going to go to the file tab of the ribbon to perform the save still in my working directory so I'm going to just name it and it defaults again to the power bi desktop file you can press enter or click on Save let's expand the tables that we brought in over on the right in your Fields pane and you'll notice some inconsistencies with the data earlier we talked about how important column headings are and That they need to be descriptive of the data that's contained in the columns well table one is looking good

it has great column headings table two and table three are both going to need to be fixed let's take a look at the data in data view so again that's the second view button on your Ribbon and we're going to go over there and click it now so we saw this in the preview window That these three tables are really one table on the internet but it came in as three Separate Tables later on in the course you'll learn how to merge those into one table so you just have one table with all of the

data as it is on the internet foreign so far in this getting data from multiple sources lesson we have brought in data from an Excel workbook an access database and a website The next lesson that we're going to be getting into is optimizing performance and you'll see another way during that lesson of how to get data into Power bi desktop for right now why don't we go ahead and click on file new so that we launch a new instance of power bi in its own window we'll come back around to the median household income file

later and we'll do some data transformation in It so that we can clean up these messy column headings and the other thing that we'll probably want to do is rename the tables table 1 2 and 3 are not very descriptive table names foreign we're going to go ahead and close the splash green just so we're in the desktop interface before we bring in data from another file I'd like to open a file and review Its contents so in the video description there is a customer data Excel file I'd like you to go ahead and open

the file when you look through the sheet tabs in this file you'll notice that each sheet has a pivot table or a pivot chart on it and the underlying data doesn't appear to be on a sheet in this file this is an example of a file using a powerpivot data model to capture all the underlying data so the cool thing about this is even if You don't have the power pivot add-in power bi will be able to read that data and the decision that you're going to need to make in order to optimize performances basically

this one do you want to bring in the whole data set into Power bi or do you just want to bring in the underlying data from the pivot tables and pivot chart that's the question so far we've been connecting to data sources through get data or Excel Workbook if you connect to a data source it's only going to bring in the data from an Excel file that resides in the Excel application if you want to bring in the other data you're going to have to import the file into Power bi so I have the powerpivot

add-in and even if you don't it's still fine if you inherit a file with a power pivot data model in it and you don't have power Pivot power bi can access that data model so I'm going to go up to the ribbon and click on power pivot and then the first button is manage which takes me into the data model again if you don't have access to the powerpivot add-in you'll be fine when we bring this into Power bi what you're seeing right now is the powerpivot data model it is the underlying data that's Providing

subsets of itself for the pivot tables and the Excel application you'll notice at the bottom of the screen there are different sheet tabs just like in in Excel so the data has been broken up into categories onto different tables and the relationships have been created between those tables as well so we're going to just take a brief look at the data this is the customer's data we have some calculations on the sheet At the bottom in a calculations area you have an order details sheet where we have some more calculations at the bottom so on

and so forth those are the sheets that contain the underlying data that's feeding the pivot tables in the Excel application if I want to look at the relationships in here up on the ribbon I can go to diagram View and this is very similar to model view and power bi which we'll be Covering in module 4. so relationships have already been created in the data model for this Excel file now I'm going to go ahead and get out of power pivot I'm going to save my file switch back over to excel and we can all

close the Excel file now if you've gone into powerpivot make sure that closes as well now that we're back in our Untitled power bi desktop file we're going to Bring the data from customer data Excel file into Power bi but we're going to use two different techniques and you'll see the difference as this plays out the first technique we're going to use is this simply on the Home tab we're going to click Excel workbook and we're going to double click customer data it'll do its connections and then you'll have your Navigator window what I'd like

you to notice here Is the Navigator window these look like sheet tabs so these are the sheets from within the Excel file that have the pivot tables and or pivot charts on them if I click on customers by country it's only showing me the underlying data that's populating that particular pivot table what we're going to do is we're going to select all of the check boxes and we'll bring all of these tables in Or sheets in and we'll load so it's not showing the data model At All by using get data or Excel workbook it's

just showing the stuff that's in the Excel application we can go over the data View and look at the data and each of these tables and you'll see it's a small subset of the greater data set that's in power pivot we're not going to save this file so I'm going to just do file new And it's going to launch another instance of power bi desktop go ahead and close the splash screen when it opens whenever you use get data you're connecting to the data in order for us to bring in the powerpivot data model into

Power bi we're going to have to import the data we can do that from the file tab of the ribbon so click on file and you'll see the Import option notice the four different types of imports if we wanted to bring in a power bi template or a power bi visual from another file or a power bi visual from appsource we would use the import feature The Last Choice is what we're going to ultimately use power query power pivot and Power view three add-ins to excel even if you don't have those add-ins it doesn't matter

if you inherit an Excel File with power query power pivot data models or Power view reports you can still bring that underlying data into Power bi because it's able to recognize it so this is a power pivot file an Excel power pivot file we're going to select that fourth option and it's going to take you to your directory and you're going to double click customer data the screen that shows up can be kind of alarmy when you first read it import Excel workbook contents we don't work directly with Excel workbooks but we know how to

extract the useful content so you can work with it in power bi desktop that's great for us we're going to go ahead and click Start and it will go through the import process at some point you'll get the migration completed dialog and if you do the scroll bar on the right you'll see that it brought in seven items which are Queries known as queries it brought in seven data model tables it brought in some kpis and measures which are calculations 22 of those and there were no power view sheets in the file so zero items

there we're gonna go ahead and click on close so when you look on the right side of your screen in your Fields pane these table names are mimicking the sheet tabs in the powerpivot data model not the Excel workbook If you go to data View you can see the underlying data in all of the tables a much broader data set than what we'll use to build those pivot tables and pivot charts knowing when to import data versus knowing when to connect to data will optimize your performance and power bi if we didn't import the data

model data we would have to take the data tables that we did import from Excel which is a subset of the over all data set and we Would have to kind of merge them together to get a complete picture of the data this way we don't have to do that we'll go ahead and save this file and name it customer data before we get into the last lesson of this module let's go ahead and do some light housekeeping and close any power bi desktop files that you have open our last and final lesson in this

module is resolving data errors There are two errors that I'd like to show you we're going to force one to happen to get started so I'm just on my desktop and I have my Windows Explorer set into my working directory where I have the class files for this module what I'm going to have us do is you'll see your sample Superstore both the power bi and the Excel spreadsheet in the same directory what I'm going to Do is I'm going to grab the sample Superstore spreadsheet and I'm going to just move it out to my

desktop so it's no longer in the same directory while the directory is still open I'm going to double-click my sample Superstore power bi file to launch the application and open that file so right now when we open the file we're not seeing any errors we still have our tables in the fields pane you can go to data View and still see the data in the Table so when you connect to data it connects to the location of the data as well so you can be working in the file and it appears that everything is fine

until you do one of two things I'm going to go back to the Home tab here and in the queries group I'm going to click on the refresh button so all of a sudden I get errors and it's Telling me that it can't find the data source file the actual Excel file so when we move that file out of the working directory power bi does not update a change like that so right now we're kind of stuck we wouldn't be able to get much done on this file without resolving that error so the way to

resolve the era is like this we're going to close that refresh box and then we're going to go to the file tab And we're going to click on options and settings so we were in options earlier when we looked at the check boxes that enable power bi to be able to load relationships in to itself when bringing them in from a database file or in our case a power pivot file as well but we're going to click on data source settings this time so when the data source setting dialogs box opens you'll notice that it's

Pointing to the path that it was originally in on your computer before we moved it out into the desktop that stays with the power bi file so in this situation we have to tell it where the actual Source data file resides the sample Superstore Excel file doesn't reside in that path anymore what you're going to do this time is in the lower left hand corner you're going to select the change source button And when we click on that button it opens an Excel workbook and this is particular to excel right at this point we're only

dealing with that Excel file and notice that it has defaulting to excel workbook in open file as you have other file types down there so depending on what kind of file it is it normally will select the right type but always verify that it's the right one selected so where it has that path I'm going to click on browse And I'm going to browse to where I put my sample Superstore file which is just on my desktop now and I'm going to double click it so once it has the path it will be able to

continue now go ahead and click ok and it updates on the data source settings dialog so you can send a power bi desktop file that you create to someone else you can email it you can get it to them but if You do that and you're the one that created the file also send them the source data file because otherwise they'll be limited and what they can do in power bi it will let them load the data but as soon as they refresh or do a few other things you'll start seeing the arrows we're going

to close data source settings and it tells me there are pending changes in my queries that haven't been applied that yellow band at the top in This case this is different from loading data we want to tell it to apply to changes that we just made so I'm going to click on the apply changes button and it's kind of actually reloading the data into the file so when you open a power bi desktop file it loads the last data set that it had in it but if the data source has been changed to a different

location and you point to it it's going to have to reload the data If you'd already progressed to the point where you made visualizations and Report View and things of that nature they would all update with the Reloaded data so let's see if we got rid of this era on the Home tab we're going to click the refresh button again and we shouldn't get the errors this time because it knows the path to the data source at this point if we move the Excel file back into our working directory we would Have to update our

data source again so we're going to do that right now I've minimized my power bi desktop and I'm going to grab my sample Superstore Excel file and drag it back into my class file working directory folder and then I'm going to bring my power bi desktop back up I am going to click the refresh button on the Home tab at a ribbon and we get the same error some other errors might Say load this one is saying refresh because we click the refresh button I'm going to close that Arrow I'm going to go to the

file Tab and back to options and settings data source settings and at the bottom I'm going to click the change source button and use the browse button to get back to my class files working directory and double-click sample Superstore I'm going to okay And close and remember you're going to have to apply the changes in the yellow band at the top of your screen so it reloads the data in from the path that you just pointed it to now when I click on refresh I won't get the error and we will definitely be doing a

deeper dive into refresh in just a little bit another type of load error that you may get Is when you import a file that has power view sheets Power view is an Excel add-in and it allows you to create visualizations in an Excel file this is very similar to when we imported the powerpivot Excel file even if you don't have powerpivot or Power view power bi is able to read that data so we do have a Power view file called it spending analysis in the video description and it it's an Excel file with the Power

view sheets in it I'm Going to take a moment to open that file I don't have power views so you'll see what it looks like when you open it and you don't have power View foreign I pulled this file from the Microsoft site they have several sample files out there that you can grab and use in power bi some of them this one may be when you see the visualizations in power bi you might get some ideas for those same types of things on your data So I don't have power view like I mentioned and

this file contains multiple sheets just three of them that have the framework for the Power view report that I can't see without having to add in I'm going to go ahead and close this file and then we'll switch back over to power bi in power bi I want to go to file new don't want to bring that data from the Power view reports file into sample Superstore data set so we're launching a new instance we've done this before I'm going to go ahead and close the splash screen because we can't use get data to import

this data and we're going to go to file import we're going to select the fourth choice like we did before power query power pivot Power view and you're going to double click on that it spend analysis sample file we get the same import Excel workbook Contents dialog and we're going to click Start so the migration is completed there are nine queries that were imported nine data model tables and 15 kpis and measures as well as three Power view sheets the ones that we saw in the Excel interface I'm gonna go ahead and close this is

the error that I'm referencing so sometimes when you bring in something from Power view Power bi may not support that visualization anymore as shown here it either has a visualization and Power view that is not yet supported in power bi or is no longer supported in power bi so we haven't gotten to the chapter where you're going to be creating reports and putting visualizations on the report pages but this is another pretty Common Era so the fix forward even though you don't Know yet through watching these videos how to create these you can learn how

to get rid of them before you learn how to create them so I'm going to click on the framework of where visualization would be where the error message is and I'm going to Simply press delete on my keyboard and once you know how to build visualizations you can replace it with something that is supported within power bi The last thing we're going to go over in this module are the implications of where your Excel Source data files are stored and their performance and power bi if your Excel files are stored locally on your computer versus

in the cloud via OneDrive or SharePoint power bi will treat those as different source files when it comes to refreshing the source data I've made a copy of the sample Superstore desktop file and renamed it sample Superstore local just to make a distinction between my locally stored Excel file and the one that we'll get to put into Power bi that's stored on OneDrive if you have access to a OneDrive for business account go ahead and upload the sample Superstore Excel file that we used earlier right now I'd like to point out a few things so

that you can see the impact When we refresh the data from a local Excel file the first thing I'd like to point out since we're in data view is I'd like you to take a look at the top two sales figures in the orders table so the first one is 8617 and the second is 1527. I'd also like to point out that both of these orders are from a city in Utah named Kearns now in this file I've already set up one Small visualization and I'm going to show you what it looks like by going

to report View you'll learn how to create report visualizations when we get to module 7. but right now as I hover my mouse Over the Bar on the report it shows me the sales are two thousand four hundred and twenty eight dollars and 25 cents for the city of Kearns I've opened a sample Superstore Excel file that's stored locally on my Computer and I'm going to change the values of those two cells that we saw in power bi desktop I'm going to change the 8618 sale to 386.17 and I'm going to change to 1527 sale

to 215 27. and then I'm going to save the file foreign I've switched back over to the power bi desktop file and as you'll notice on my screen even though I made the change in the Excel source file it hasn't updated Those sales values as of yet the reason why is I have to tell it to the way I tell it to do it is by going to the Home tab of the ribbon and almost in the center of the Home tab there's a refresh button so notice when I hover over that button it says

get the latest data by refreshing all visuals in this report I'm going to click the button to refresh you'll notice that it has the pop-up on the window And when it's done and I go back over and I look at those sales figures they have indeed updated to what I put in the locally stored Excel file now that is in the power bi desktop if you make a change to a locally stored file Excel file in particular and you refresh in power bi desktop it will update the information but something different occurs in the power

bi online service And I'm going to show you that now I've already published that sample Superstore local file to the power bi service and it published both the report and the data set so when I come into the service and I look at the report it looks exactly as it does in the desktop with the same sales value of 2 428.25 when I hover over the data set I see a refresh button refresh now I'm going to Click that button and then I'm going to go back to the report the report is the same same

sales value I can also refresh at the report level by clicking this refresh button over here again nothing changes this is because the Excel file is stored locally and the online service cannot refresh from a locally stored Excel file At this point if I wanted to get the updated data set into the cloud Empower bi service I would have to republish it from the desktop now we're going to bring in an Excel file that's stored in a OneDrive for business account it's the same Excel file as the sample Superstore file we've worked with previously I've

just renamed it dashod to represent OneDrive the tricky Wicket with bringing in something from SharePoint or OneDrive is You can't just copy the link that's up here you actually have to open the file in the Excel desktop application so I'm going to go to the more actions button hover over open and choose open in app this is the only way to get a link that you can use to bring it into Power bi desktop just like you would bring in a web file once I have it opened I can then go to the file Tab

and click On info and I'll see the copy path button that's the way you have to get the path to the file where its location is in one drive I'm going to click on copy path and then I'm going to just close this file I don't need to be on one drive in this moment so I'm going to switch back over to power bi desktop because I want to bring this into a new instance we're going to go back to file New and launch a new instance of power bi desktop so we can just keep

these separate local versus OneDrive I'm going to access get data from the splash screen and when the get data dialog box opens on the left side I'm going to click on other and then double click web at the top of the list so the path is on our clipboard I'm going to do control Z to paste it in and The second tricky Wicket you're going to run into here is at the end of the path you'll have a question mark web equals one you need to delete that part of the path or this will not

work so I've deleted it and I'm going to click ok the Navigator window will open it's going to be the same three tables orders returns and users that we used before so I am going to put a check mark in front of each one and click the load button The normal evaluation process will take place you'll have your yellow band at the top okay it's loading the data to the model and if I look over to the right in my Fields pane I have the same three tables I'm going to switch to data View and

just as a navigational point I'm going to expand the orders table and click on the sales field and you'll notice that those top two Sales values are the same as in the original Data before we made any change in the OneDrive file we hadn't made any changes to the data so you have your 8617 and 1527 in terms of sales we're going to go to report View and I'm going to show you how to build the report the same one that I had in the locally stored desktop file so what I'm going to have you

do is over to your right in the visualizations pane You're going to click on that very first visualization thank you it is a stacked bar chart and then over to the right in your Fields list you're going to expand your orders table and I'm going to just drag the city's field over to the framework of the visualization and again you'll go into deeper detail on how to design reports in module 7. And then I'm going to go and grab the sales field and drag it into the framework of the visualization as well the last thing

I have to do is filter it for just that one city which was Cairns so in my filters pane where it says city is all I'm going to do the drop down arrow and I'm going to just in the search box I'm going to type Kerns k-e-a RNs And I'll just check the box when it comes up so there are eight sales that happened in the city of Kearns and that's how I developed the visualization you saw in the other file if you point to the bar it will tell you the sales value for that

city is 2428.25 just like in our regular file I'm Gonna Save this file and I'm going to call it sample Superstore Dash OD for OneDrive now that we've saved our file we're going to publish it to the power bi service the last icon on the Home tab at a ribbon is publish go ahead and click it it will ask you sometimes if you want to save your changes even if you've saved sometimes that prompt will come up not always and then you'll get a box that says Publish to power bi select the destination I have

multiple workspaces the one that we all have in common is my workspace and I'm going to go ahead and put this in my power bi video workspace but feel free to put it in your workspace we'll do a deeper dive into publishing and what workspaces are and how to use them when we get to module 8. so for right now select your workspace and it will let you know that it's publishing And it lets you know when it has successfully published we could get to the power bi service from this box but for right now

we're going to just click got it now at this point we want to make a change in the Excel file that's stored on OneDrive so I'm going to go back over to OneDrive and I'll need to open the file in the Excel desktop app again so I'm going to hover over open and open In app we're going to make the same change in this file the same two changes that we made in the other file so I'm in cell W 330 and I'm going to change that to 3 86 17. and I'm gonna go to

sew w332 and change that to 215 27. and then I'm going to save now at this point it is uploading and if you attempt to do the X to close the file you'll get a message on your screen That it's still uploading I'm going to wait till it's totally saved and then proceed so at this point I'm going to go back over to power bi desktop foreign once I'm in the desktop I can go ahead and refresh this power bi file by clicking the refresh button on the Home tab of the ribbon when I refresh

it's going to go through the dialog boxes where it's evaluating it's looking At all of the data and when I hover over the bar on the chart I see the sales value for that city has gone up by five hundred dollars it's now 2928.25 if I go to data View I will see that those sales values updated as well and this is the best part it will also update in the service since the Excel file is stored in the cloud on OneDrive Another way to get to the service is by going up to your login

information in the upper right hand corner and clicking on power bi service now when you get there over on the left side the next to the last button is workspaces and I'm going to just navigate to my workspace where I saved and publish this data once I'm there I'll see that I have both the OneDrive report and data set and the locally stored report and data set For the OneDrive data set I'm going to go ahead and click the refresh Now button and then I'm going to click on the link to take me to the

report when I get to the report it's now updated to 2928.25 so if a file is locally stored it will update in power bi desktop when you refresh it but it will not update in the service and you would have to republish it to the service In conclusion for module 2 getting data into Power bi you brought in data from multiple sources you learned about the implication of locally stored versus cloud-stored Excel files how to import from powerpivot and Power view Excel files versus using get data the difference between a data source and a data

set you learned how to import from an access database and from a website we went over a few performance Optimization issues and you'll learn more throughout the rest of the course and you learned about common load errors and how to resolve them the next module module 3 is about cleaning and transforming your data in power bi everyone I'm Trish Connor Cato and I'd like to welcome you to Microsoft power bi now it's time to get started in module 3 where you'll learn how to clean Transform and load data into the data model you'll learn the

process of profiling and understanding the condition of your data you'll learn how to identify anomalies look at the size and shape of the data and perform data cleaning and transforming steps to prepare the data for loading into the data model we'll be using the sample Superstore power bi desktop file that we created in The previous module during this module so feel free to pause the video and get that file open the first of the three lessons in this module is data shaping shaping data means transforming the data by renaming columns or tables removing rows setting

the first row as headers and so on in this lesson we'll be performing data transformation steps on the users table and the orders table we're going to start by taking a look at The data that is in the users table remember your view buttons are on the left side of your screen and the second view button is data View I am going to go ahead and click that button to get into The View when I get in there I want to go over to the fields Pane and I want to click on the users table

so I can see the data that's in it a couple of things to point out this table contains information about Managers and their regions so the first thing we're going to want to do is rename the table from users to managers we notice that column one and column two are not good column headings for a table it's not descriptive of the data in the columns the data that's in the first row region and manager would make better column headings and will make that change as well the final change we're going to make is We decide

we want to capture the manager's last names in this table so we'll be adding a column to the table and we'll input their last names let's rename the users table I'm in the fields Pane and I'm going to right click on it choose the rename options from the shortcut menu and I'm going to just type managers and press enter for it to accept the change Notice the organizational change in the fields pane all of your tables will always be listed there in alphabetical order we'll have to go into Power query editor to promote the first

row as column headings in order to do that we're going to go up to the Home tab of the ribbon in the queries group we're going to click on transform data power query editor opens in its own separate window Once you're in power query editor your tables are known as queries and they show up on the left side of the screen what we're looking at right now is similar to data view in the desktop in that we're seeing all of the columns in the orders table on the left side we're going to click on managers

so that's our table of Interest right now a few other things I want to point out On the right side of the screen you have a query settings group and in that group you have properties it includes the name of the query in this case managers and underneath your properties you have what are called applied steps just by switching to power query editor it performed three steps it looked at the source it did a navigation step and it changed the type you'll notice that power query editor Has its own ribbon and we're on the Home

tab of the ribbon what we're looking for in the transform group is the use first row as headers button when we click that icon you'll notice that it promoted region and manager as the headings and it got rid of column one and column two the most efficient way to create a column to hold the manager last names is To duplicate the manager column I'm going to right click on the manager column heading and I'm going to choose duplicate column from the shortcut menu now I have my original manager column and manager copy column we're going

to rename these columns I'm going to double click manager and I'm going to edit it so it says manager first and I'm going to press enter after I make that edit so it accepts the change Go ahead and rename manage your copy to manage your last the last transformation step we'll take on the manager's table is to replace the first names with the last names in the manager last column we have to do these individually in power query editor so I'm going to click the first manager last which is Chris I'm going to right click

on it and I'm going to choose replace values it's already in the replace with box and Chris's last name is Evans so I'm going to just type in Evans and click ok we're going to replace Aaron's last name and Aaron's last name is going to be Rogers I'm gonna just type that in replace with and click ok go ahead and change Sam's last name to Smith and William's last name to Jackson every time we transform the data you'll notice on the right side of the screen that the applied steps list got longer Starting from when

we promoted the headers it added an entry for that and everything after that is what we did duplicated column renamed columns replace values so on and so forth if you do a step in here and you realize that it was a mistake you can always go over to the applied steps and when you hover over them you'll see the Red X or if you right click on a applied step you can delete that step just by clicking delete or you can delete that step in all of The steps afterwards from the shortcut menu we're comfortable

with the changes that we made so we don't have to do anything with our applied steps at this point now we're going to perform some transformation steps on the orders query so on the left side of the screen in the queries pane I'm going to go ahead and click on orders the first thing we want to do is decrease the data set by removing some Columns I'd like to direct your attention to the ribbon on the ribbon you have a manage columns group and it has two icons choose columns and remove columns I very rarely

use remove columns this is why right now the row ID column is the column that I am in and if I click the remove columns button it's going to remove that column for me that may not be a column that I want to remove now Granted if I did that by accident I could go over to the right and there would be an applied step where I removed a column and it would let me reverse the effect of that and get that column back I use choose columns it gives more control I'm going to go

ahead and click the choose columns icon and the columns that we're going to remove I'm going to uncheck the boxes in Front of our row ID order priority product base margin and quantity ordered new you may have to scroll down to see that one so I'm unchecking the columns that I want removed and keeping the columns checked that I want to keep I'm going to click OK at the bottom if you look over at your applied steps you have one now because we remove multiple columns And it says removed other columns if we happen to

do that accidentally we could do the X in the front of that applied step to get those columns back The Next Step we're going to take on the orders query is to filter the data for just five particular States I'm going to scroll across using my horizontal scroll bar at the bottom until I see the state or Province field and similar to excel in power query editor you have Auto filter arrows Next to each field heading and you can use those arrows to filter and sort very similar to what you do in Excel gonna go

ahead and click the auto filter next to state or province and this is a good example of something to be on the lookout for it only loads a limited amount of data in power query editor so whenever you go to the auto filter drop down always look at the bottom of the screen and you may See a warning symbol and it says list may be incomplete if that's the case you would want to load the rest of the list before doing your sort or filter to make sure you get accurate results so I'm going to

click on that load more button in the bottom right and the warning disappears so I know that I'm looking at a list of all of the states that are available in this data set We are going to uncheck select all at the top of the list and we want to filter for Arizona so I'm going to check Arizona California Florida I'm going to scroll down until I see New Jersey and I'm going to check New Jersey and New York and now I'm going to click ok so now the data is filtered for just those five

states We decide that the last things we're going to do for right now is we're going to rename state or Province column to State and we're going to sort it in ascending order so I'm going to just double click where it says state or Province and I'm going to edit it so it just says State and remember to press enter or click away from that in order to save it and then I'm going to click on the auto filter arrow and right at the top I'm Going to click on sort ascending so now I have

Arizona first at this point all of the transformation steps that we took in power query editor for the managers and the orders query those changes are only in power query editor if we want to get the changes down to our data model Empower bi desktop we're going to use the first button on the Home tab of the ribbon if you click the upper half of the button it will close power query editor and Apply the changes in the desktop I'm going to click close And apply now it switches you back over to the desktop and

you'll notice the yellow band there were pending changes in your queries and now it's updated so we're still looking at the managers table in data View and we'll see the manager first and the manager last columns and the manager last names also you'll notice that the column headings are promoted remember we renamed the Table in the desktop on the right side I'm going to look at the orders table and you'll notice that well it's missing some columns The Columns that we decided to get rid of but you'll notice that your state column is filtered for

Arizona California and the other three states that we chose what I'm going to do now is save the power bi desktop file our next lesson enhancing the data model is where we're going to merge Information from the managers table into the orders table specifically the two tables have a field in common and that is the region field you remember in our managers table the managers are listed by the regions they represent we decide that we would like the manager data in the same table as the orders data in order to do that we're going to

need to go back to Power query editor so on The Home tab of the ribbon go ahead and click transform data and the power query editor window will open you'll notice on the Home tab of the ribbon in the combine group you have two choices that are available merge queries and append queries you also have sub choices for each of those when you click on the merge queries drop down you can see that you can either Merge the queries or perform the merge and create a new query we're going to select merge queries as new

we want to retain our original orders and managers queries and create a new one that gives us all the order information and the manager information in the same query when we get into the merge box you'll notice since I was on the orders query the top one is orders it has a small sample of the data set And I'm going to scroll across until I see the region field and I'm going to just click in it letting it know that that is the common field underneath that I'm going to select the drop down and select

the managers table and in the managers table I'm also going to click in the region field you'll notice at the very bottom of the screen once you make those selections it lets you know that it matches 2428 rows from the first table It also has above that join kind now table joins can be a really intense topic and we're going to spend a few moments talking about them before coming back into this box before we perform the merge let's take a moment to review the different join types that are available in power bi this PowerPoint

presentation is also in your video description so you can reference it in the future the default join type is left outer that Means it will return all the records from the first table and only the matching records from the second table our first table is the table that we listed first which was the orders table a right outer join is where it will return all of the records from the second table and only the matching records from the first that's the type of join we're going to ultimately use the other join types are listed on

the slide if you use a full outer join it Will return all the rows from both tables an inner join would only return matching rows so on and so forth so in our merge dialog box we want to change the join kind from left outer I'm going to do the drop down and select right outer you'll notice that the selection at the bottom the check mark updated it matches three or four rows from the second table we're going to go ahead and click ok thank you And now you'll notice that you have a new query

on the left side of your screen and it gives it a default name of merge one that is the merge query we still have our original orders and managers queries intact and that's because we chose merge as new in a new query the information from the managers table is going to be in the far right column so I'm just scrolling across And the last column is called managers it doesn't have an auto filter drop down arrow it has an expand button in the column heading and right now the column is populated with the word table

what we want to see in the column is the region for each order so we're going to click the expand arrow to the right of the column heading and you'll notice this list it says expand or aggregate you'll learn about Aggregates in a later module Even though we already have a region column in the orders table we want the region manager first and manager last names to come in so we're going to just click OK in here as they're all checked and you'll notice if you scroll to the right you now have three additional columns

manager's region managers first and managers last take a moment to change the managers.manager first and the managers.manager last column headings to just say manager first and Manager last foreign you'll notice that the first record is for the central region and it's a null record so we decide we're going to filter that out I'm going to go to the managers.region auto filter Arrow I'm going to load more at the bottom because the list is incomplete and I'm going to uncheck Central and click ok so we're not seeing the null record and we'll go back to Auto

filter funnel Now okay and we'll sort the regions again the list may be incomplete so load more and then sort them in ascending order so we'll have East at the top of the list to rename this query we could right click on it in the queries Pane and choose rename or because we're in power query editor we can rename it under query settings and properties so I'm going to click on the name merge one There and select it and I'm going to name it ordersby region and press enter we want to go ahead and close

and apply our changes so we're going to click the first button on the Home tab and it will take us back to the desktop and apply all the changes that we just made go ahead and save your sample Superstore desktop file Now you'll get to try it yourself you're going to want to merge the orders and returns tables together into a new query and you're going to want to show the return status in that merged table go ahead and get started on that you can see my results on my screen and compare them with yours

I filtered out the null values in the returns.status column just to clean up the data a bit more once you're done with that go ahead and Close and apply your changes and save power bi desktop file again at this point in your Fields pane you'll notice that you have your orders by region merged query and your returned orders merge query showing over there the last lesson in this module is data profiling power query editor has profiling tools that you can use that will give you an in-depth assessment of the quality of your data We're going

to go back to the transform data button on the Home tab of the ribbon and when we get into Power query editor go to the view tab on its ribbon and you have a data preview group on The View tab with several different check boxes one of the check boxes is always checked and that's just showing white space what we're going to do is we're going to review the check boxes and their impact On your data the information that can be gained from using them I am going to check the box that says column quality

and I'll notice right underneath my column headings for each column it's telling me whether the column is valid whether there are errors in the column or whether there are empty things in the column by percentage good insight into the data that's in your columns the next check box is column Distribution I'm going to leave column quality or checked and I'm going to check column distribution and it expands that area underneath the column headings it's showing me that I have 11 distinct values and zero unique values in the discount column for example each column is listed

with distinct versus unique values and you have the opportunity there to remove duplicates if necessary The last check box is column profile that I'm going to cover that opens the panel at the bottom of the screen underneath your data set and it's giving you column statistics the count errors whether they're empty distinct unique not a number so on and so forth and there's a little graphic that's showing the value distribution in that particular column now the column that I'm in right now is the discount column the first column in the orders Table so if I

wanted to see distribution information for unit price I would just click in that column and that's how you can profile your data in power query editor I'm going to go back to the Home tab of the ribbon and this time I'm going to click the close and apply drop down the first drop down and I'm going to just close power query editor without applying the changes and I will save my power bi desktop file Notice how frequently I saved the desktop file that's where your data model is stored to recap module 3 we applied data

shape Transformations by removing columns promoting column headers we duplicated a column renamed columns and replaced the data in a duplicated column we also learned how to filter and sort columns we enhance the structure of the data by merging queries into a new query so we Could combine data from multiple tables and we ended by profiling and examining our data through the power bi power query editor profiling group module 4 teaches the fundamental concepts of Designing and developing a data model for proper performance and scalability will also understand and Tackle many of the common data modeling

issues including relationships security and performance we'll continue using the Sample Superstore power bi desktop file that we created in previous modules so what is data modeling it can be defined as making the data you use in power bi as accurate and intentional as possible it is a series of processes as you'll see play out in this module this module has several lessons we'll begin with working with tables move on to dimensions and hierarchies continue with creating model Relationships and reviewing the model interface and end with enforcing row level security also known as RLS lesson one

is working with tables working with tables can mean many things for starters we're going to work with formatting some of the columns in the orders table the first thing we're going to do is take a look at the data that's in the orders table in data View which is the second view button on the Left side of your screen when you go into Data view you'll notice that it's showing the data for the first table in the fields pane to the right click on the orders table in your Fields Pane and now you're seeing the

data in that table some of the formatting changes that we're going to want to make here is we're going to want the discount column to be formatted as a percentage we're going to want unit price and shipping Cost columns to be formatted as currency just to name a few of the changes we're going to want to make we can do that right from the desktop we don't have to do this in power query editor and the way that you do it from the desktop is go ahead and click anywhere in the discount column to select

it and you'll notice you'll get a new tab on the ribbon called column tools everything that we're going to want to Do we're going to do from the column tools ribbon tab to get started you've already selected anything in the discount column so that is the active column and on the column tools tab in the formatting group you're going to go ahead and click the percentage icon very similar to excel so now the discounts are formatted as a percentage and if we decide that we don't want any decimal places right underneath Percentage we're going to

change that to to a zero and you can see the impact on the data now I'm going to click in the unit price column and in the same formatting group I'm going to click on the dollar sign icon which is the currency format go ahead and do the same thing for the shipping cost column format it and currency I'm going to use the fields pane on the Right to navigate to the next field that I want to format so I'm going to click on order date and it will take me to that column and have

that column selected we decide we don't want the day of the week as part of our date format so on the column tools tab same formatting group this time we're going to go to the format drop down and you're going to click on the format that just says March 14 2001. and the order date is formatted that way navigate to the ship date field and apply the same format the last thing we're going to do in this lesson is categorize some location fields for mapping purposes so we have region state city and postal code fields

in this data set later on when you learn how to make visualizations on report Pages there are mapping visualizations that will give More data about a location if those location columns are categorized so we're going to just select anything in the region column and on the same column tools tab in the Properties Group you'll see the data category is uncategorized select the drop down arrow and you'll see the different things that you can categorize so we're using location fields we don't have a street address we don't have a place in our data but we do

have a city We have a region we have a state and we have a postal code we'll use those fields in just a moment but if you had latitude and longitude Fields you'd be able to categorize them if you want a web URL to be within your data you could use a web URL and categorize it if you have a URL for an image and you want that image to show you can use the image URL and it also allows for barcodes We're in the region field let's go ahead and navigate to the State field

and go back to the uncategorized drop down we're going to categorize the state as state or province go ahead and categorize the city and postal code Fields appropriately because postal code is a numeric field it only gave you the option of postal code or barcode The only effect you'll see of your categorization is in the fields list if you notice city has a globe icon in front of it as does postal code and state again when you learn how to create report visualizations you'll see how the categorizations help on maps lesson two is dimensions and

hierarchies a subtopic of working with tables to get started we're going to talk about breaking down your tables it's an optimizing performance tip And when applicable it's very useful one large table is not the answer for any sectors data model so what does that mean you break a large table down into two or multiple tables let's talk about fact tables first fact tables keep numeric data that might be aggregated in reporting visualizations for example sales and profits Dimension tables keep descriptive information that can slice and dice the Data in the fact table they require a

key field which you'll learn about very shortly so for example data that should be in dimension tables would be customer information and product information the golden rule for usable data is that you should not have fact and descriptive fields in the same table by breaking down your tables more efficiently you'll optimize the performance of your data set Let's go ahead and save our file we're going to start breaking down the orders table if you'll notice the orders table has customer information in it product information in it and it also has order information in it if

you scroll to the right this is a good example of a large table that contains both fact and dimension columns we're going to transform this data by working in power query editor so on the Home tab of the ribbon go ahead and click on transform data to open that window we are going to make a copy of the orders query and transform the copy into a customer's Dimension query in the queries pane on the left go ahead and right click on orders and choose copy right click anywhere in the queries Pane and choose paste We're

going to remove the columns that we do not want from the orders to instance the pasted version and we're going to do that by using choose columns on the Home tab of the ribbon as we did in a previous module foreign the first thing I'm going to do in choose columns is uncheck select all columns and then I'm going to select the columns that we want in the customers Dimensions Query customer ID customer name customer segment we're going to grab region state city postal code and go back up to the top and select discount as

well and click OK at the bottom so now we only have those fields and we said that in a dimension table you need to have a key field a key field also Known as primary key is a unique identifier that makes each record unique so we already have one in this table it's the customer ID field each customer is assigned their own unique ID and we'd like that to be the First Column so we're going to click and hold on the customer ID heading and drag it to the left so it's the first column go

ahead and make customer name the second column then City State zip or postal code rather and region and again this is the order of the columns and then we'll have customer segment and discount as the last column the last thing we'll do is give the query a more appropriate name so over to the right in the Properties Group I'm going to select orders 2 and you'll see this convention sometime so I'm going to Type capital d i m for Dimension and then Capital C customers you'll see it identified as a dimension table by the name

on occasion I'm going to press enter to make that change happen now you get a chance to try this on your own make another copy of the orders table and you're going to want to choose the four columns that begin with the word product And include them only in a new table I'm going to rename the query Dem products over in the Properties Group and press enter now we said that a dimension table has to have a key field in this case we don't have a key field I'm going to show you how to create

one in just a moment first let's rearrange these columns we want product name to be the first column after that we need another column that we can assign a numeric value to each Unique product name in order to do that we're going to go up to add column tab on the ribbon and in the general group you're going to click on the drop down next to index column and we're going to choose custom we're going to say we want the starting number for our products to be zero zero one and we want to increment by

one and click ok So notice it dropped off the zeros and that's fine we're going to move that index column so it is the first column in this data set and each product name has its own unique index number the last thing we need to do here is save our changes and load them back into our data model in power bi desktop so we're going to go to the Home tab of the ribbon and click close And apply now that our data is loaded into the Data model let's expand dim products table in the fields

Pane and we decide we want to rename that index field that we added we want to rename it product ID so in the fields pane I'm going to right click on it and I'm going to choose rename and just type product ID and press enter and we'll go ahead and save our desktop file the next part of this lesson is creating a hierarchy We're going to create a hierarchy based on the region field in the orders table a hierarchy is a container of sorts a way of grouping related fields together when you're creating a hierarchy

you want to start with the broadest category in terms of column and end with the narrowest category so in order to do this we're going to right click on region in the fields pane under the orders table and we're going to select create hierarchy If you notice now right underneath the region field there is a collapsed region hierarchy and I'm going to click the expand arrow and you'll see that it only contains the field that we base the hierarchy on in this case region so now we want to add the next broadest category after region

would be state so in the fields pane I'm going to right click on the State field and I'm going to hover over add to hierarchy and click on region hierarchy We're going to add two more fields to the hierarchy we're going to go ahead and right click on City add to hierarchy region hierarchy and do the same for the postal code field now when you look at the region hierarchy it contains region state city and postal code the only indication you have of the hierarchy is the symbol in front of Region hierarchy and this is

something that you won't see until we get to reporting visualizations in a future module it gives you the ability to drill down on a visualization through the levels in a hierarchy go ahead and save your file in module 2 you learned that power bi has the capability of detecting existing relationships when you bring data in from a database or maybe from an Excel power pivot file Sometimes though when you're bringing in from a regular Excel file it is not going to be able to detect relationships because they don't exist we're going to do a deeper

dive in this lesson creating model relationships you'll hear the term relationships and cardinality used interchangeably basically there are four types of relationships that are supported in power bi One-to-one relationship represented by one colon one as seen on the slide an example of that would be one manager has one region if the manager's tables and the regions table were Separate Tables the one-to-many relationship is the most common it's represented by one colon asterisk an example would be one customer has many orders two separate tables the many to one relationship is the same as one to many

it's just in the opposite Direction and it's represented as asterisk colon one many to many relationships are the least common they're represented by asterisk colon asterisk it we don't have a situation on our data that would require a many-to-many relationship but the example would be many students are in many classes I'm going to switch back over to the sample Superstore power bi file for this Lesson let's start by reviewing the relationship settings in power bi I'm going to click on the file tab options and settings and then options in the options button underneath current file

I'm going to click on data load we briefly reviewed these settings in module 2 but they're worth going over again now under relationships if you're bringing It in from an access database or from a power pivot data model the first check box is defaulted to being on so power bi will automatically import relationships from data sources on first load as you saw when we loaded the access database the third check box is also a default it will auto detect new relationships after data is loaded so if we load data and then we change some of

the data and it creates a Relationship with another table it will auto detect that for you I always like to check the second box which is update or delete relationships when we refresh the data in power bi again that's just for the file that you're working in the top two check boxes are defaulted to being checked gonna go ahead and click ok next we'll take a look at model view and that's the third view button on the left where you can see the relationships that Have been created or where you go to create relationships so

we're going to go over to model view and we can see that some relationships have been created in this file first thing I'm going to do is collapse the properties and the fields panes so I get more space and I'm going to scroll to the right and see if there are any other cable cards that are out of my view and I'm going to just move them as far Left as possible so I don't have to continue to scroll you'll notice that power bi already created a relationship with our new dim customers table and we're

going to explore that relationship now the join line is the line that's going between the two tables one thing that might make it easier is I'm going to just move the customer's card underneath the orders card And you can see that it's joined to the orders table it is also joined to the managers table if I hover over the join line between dim customers and orders you'll notice in dim customers it's highlighting the customer ID field that is the matching field between the two tables thank you we decide that we're going to focus on our

original orders returns and managers tables so I'm going to hide the remaining Tables in each table's title bar there is a little eyeball symbol and if you click on it it will put a slash through it so it's kind of hiding the table you'll see this when we go back to report view these won't show in the fields panes we're also going to hide returned orders dim products and orders by region and I'm going to just move those kind of over to the right the ones that I'm not Going to use right now notice the

relationship lines stay intact when you're moving them as we look at these three cards orders returns and managers I'm going to size them so I can see all of the fields in each card makes it a little bit easier to work with and you don't have to scroll for a field or it's out of your view and we see that we have a relationship between orders and managers It made it a many-to-many relationship as indicated by the asterisk on each card we want to create a relationship between orders and returns it has to be a

matching field in this case the order ID field is in both tables order ideas and orders and it's in the returns table to create the relationship I'm going to click and hold on order ID in the orders table and drag it and drop it on top of order ID in the returns table And you'll notice that it automatically created the relationship and it made it a one-to-many relationship one return can be on many orders is what that is saying if we want to see details about the relationship we can double click the join line in

between the two tables and you'll see in the upper half of the edit relationship screen it's showing a subset of the orders table data and Order ID is the matching field order ID Is also the matching field in the returns table and it's a many to one or one-to-many type relationship we can click ok to close the edit relationship window let's go ahead and review the model interface the first thing I want to bring your attention to is the manage relationships button on the Home tab of the ribbon let's go ahead and click on manage

relationships and the dialog box opens good tip about This dialog box if you go and do major table transformation come back to model view click on manage relationships and at the bottom click the auto detect button so it will let you know if it found no new relationships as in our case or it will create the relationships is it found any we're going to close that dialog box on the right side of your screen expand both the properties Pane and the fields pane In the fields pane expand the orders table and click on the order

date field you'll notice that the properties pane has updated to indicate that you're looking at properties for the order date field in those properties it has a formatting section so when we changed our date format for the order date and the ship date we did that in data view we could also do a similar thing in this View I've selected the returns card and when I look at the properties you'll notice that it's about the returns card actually the returns table you'll see that that table is highlighted in the fields list as well if you

wanted to you could put in a description for your table you can put in synonyms for your table the synonyms tie into a feature that you'll learn when we get to creating visualizations called q and a question And answer it's an analysis feature that's built into Power bi it's available in both the desktop and the service and so if someone were to type in a question using any of your synonyms they would get results foreign the row label field is used for Q a as well key column normally it detects the key column in a

table there are instances where it might get It wrong for example in the orders table you have the order ID you have customer ID they're both key columns one is for the orders table one is for the customers table so in this case it's getting it okay but if you needed to change it you could change it here we already saw what happens we hid the tiles by using the eye icon in the upper right corner of their title bars and feature table is an interesting Feature that you can use here it's actually pretty cool

once this data set is published to the service then the featured table takes effect a featured table is an easy way to share specific table information with other users either through workspaces in the online spaces or you can actually get stuff into Excel this way via data types in Excel let's go ahead and save our file again and switch back over to data View Now that we're back in data view we can see in our Fields pane to the right that the four tables we marked as hidden in model view have that eyeball with the

slash through it in the fields pane if we go to report view our first view button we'll notice those tables are hidden from our view our final lesson in this module is row level security RLS and power bi can be used to restrict data access for given users filters Restrict data access at the row level and you can Define filters within roles in the online service members of a workspace have access to data sets in the workspace RLS doesn't restrict this type of data access in the desktop you set up the security roles and in

the service you would assign users to those roles as you'll see in this next lesson we're going to set up a role that allows users that are assigned to that role to View order information just for the east region to do that we're going to be using the modeling tab on the ribbon and in the security group go ahead and click on manage roles to create our role we'll start by clicking the create button and we're going to name the role East in the center of the manage rolls box you're going to click on the

orders table to let it know that you're using a field from the orders table Over to the right you're going to put in a simple Dax expression Dax is data analysis expression it's a formula language and you'll learn more about it in the next module in the meantime you're going to click in that table filter Dax expression box on the right and you're going to type an open square bracket as field names need to be enclosed in square brackets you're going to type region And a closing square bracket then you're going to type the equal

sign and then double quotes because it's a text-based field you're going to type East and close the double quotes so we're saying for this role that we're calling East only allow users that we assign to it to see the Eastern region information from the orders table in the lower right hand corner you're Going to go ahead and click save go ahead and set up another role for the west region in the orders table power bi is really good about letting you know when you have errors and expressions but when you want to check before it

notifies you in the upper right corner of the manage roles box I can click on the check mark which says verify Dax expression if it found anything wrong with my expression it would let me know at this Time I'm going to go ahead and Save to test your roles also on the modeling tab right next to manage roles button is view as when you click on view as you'll see your East and your West you'll also have other user go ahead and check the box in front of East and click ok you'll notice the yellow

band at the top of your screen that says now viewing as East go back to data View and when you click on the orders table You'll see that it's only showing the east region we're going to click on stop viewing in the yellow band and now we're seeing the full data set we're going to save our desktop file and then the last button on the Home tab is publish let's go ahead and publish it to the online service you can use my workspace that's the one we'll all have in common I'm going to go ahead

and publish it to my power bi Video workspace and then click the select button you'll have the publishing the power bi splash screen on your window and when the data set is successfully published you'll see success you can click on the link right underneath success to go ahead and open that file in the cloud-based service power bi service opens in report View and we don't have any reports what we want to do is go to our workspace so the Bottom icon on the left side will take you to your workspace that you saved this stuff

to so it saved the report which is currently empty it also saved the data set to assign roles to our East and West that we set up in the desktop we're going to hover over the data set and we're going to go to the vertical Ellipsis more options button and we're going to select security On the left side of row level security we'll see the two groups that we created East and West they both have zero members in the group in the members area you can use distribution groups male enabled groups security groups individual email

addresses you just cannot use groups created in power bi I'm going to enter an email address from my organization and click the add button after I do that I'm going to click save At the bottom so it saves that person to my East group go ahead and add someone from your organization to your West group and Save switch back over to your desktop file and click got it on the publishing the power bi window go ahead and save your file to recap this module you learned about the basics of data modeling through working with tables

we implemented Dimensions and hierarchies we defined relationships and cardinality and enforced row level security setting up the roles in power bi desktop and assigning the users in the power bi service hi everyone I'm Trish Connor Cato and I'd like to welcome you to Microsoft power bi in this module we're going to create model calculations using Dax you'll be introduced to the world of Dax And its true power for enhancing a model you'll learn about aggregations and the concepts of quick measures measures calculated tables and calculated columns to solve calculation and data analysis problems you'll also

learn about time intelligence functions and key performance indicators first let's talk about what is Dax Dax stands for data analysis expressions and is the formula language used in power bi The structure is somewhat different than basic Excel functions as you'll learn in this module Dax is a collection of functions operators and constants that can be used in a formula or expression to calculate and return one or more values Dax context enables you to perform Dynamic analysis in which the results of a formula can change to reflect the current row or cell selection and any related

data We'll be using the sample Superstore and retail sales analysis desktop files in this module again you can find the files in the video description and we started building the sample Superstore desktop file in module 2. okay we'll be starting with the sample Superstore desktop file if you want to get that open this module has Five Lessons we're going to start by creating calculated tables then we'll create calculated columns You'll learn about quick measures and measures and how to create them then we'll work with time intelligence functions and end up using key performance indicators let's

get started we're going to start by using the distinct function in Dax to create a calculated table that will return one column of all of the distinct order IDs from the orders table to do this we're going to go to the Modeling tab on the ribbon and in the calculations group go ahead and click on new table you'll see two changes on your screen you have a formula bar that says one table equal and over in your Fields pain to the right you have a new table called table right now we're going to name that

table distinct orders count so go ahead and double click the word Table in the formula bar and we'll just type distinct order count and we're going to click after the equal sign to build our distinct Dax function what we really want is a count of the distinct order IDs in the orders table so distinct means here the total number of different values regardless how many times those values appear in the table we're going to start by beginning the type the function name distinct It will come up on the list I usually stop typing at this

point so I make sure I don't make a typo I can double click distinct from the list or if it's already highlighted like it is in my case I'm going to press the tab key on my keyboard to tab it in so it gives me the distinct function and an open parenthesis right underneath your formula bar in bold it says column name or table expression that is the only function Argument for distinct so we want it to be for the orders table and in particular the order ID field I'm going to start typing orders as

in the table name and you'll notice the orders table shows up on the list as well as every field within the orders table when you see orders order ID you can double click it or you could go down and highlight it and tab it in the syntax here is the name of the table and then the column within that table is Enclosed in square brackets at this point we can press enter and it will perform the calculation and return our one column table since we're in report view we're not going to be able to see

the data so on your left side go to your second view button which is data View foreign and you'll see your distinct order count table on the right side in the fields pane when you click on it you'll see That the table contains one column and it only contains the distinct order IDs if you look all the way at the bottom of the screen in the status bar it tells you there's 1746 rows so there is 1746 distinct order IDs in the orders table in your Fields pane click on the orders table and look at

the status bar and you'll see that there's 2428 rows So the Dax distinct function returns a one column table populated with distinct values go ahead and save your desktop file our next lesson will be about creating calculated columns like calculated tables calculated columns become part of your data set we're going to use the date diff function to calculate the difference in days between the order date and the ship date in the orders table And we're going to name the column days to ship so to get started what I'm going to do we can do this

from the ribbon or we can do it by right clicking in the fields pane so what we're going to want to do is I'm going to right click on the orders table in the fields Pane and I'm going to select new column thank you the same effect happens when we did a new table where you in your formula bar You get one column equal and in your Fields pane you have a column called column in this moment well we're going to name it dates to ship so in the formula bar I'm going to double click

the word column and I'm going to type days to ship and then I'm going to navigate to after the equal sign we're using the date dis function so I'm going to start typing d a and when I see it on the list I stop typing again you want to avoid typos Wherever possible in this case I'm going to use my down arrow to highlight date diff in the list and I'm going to use my Tab Key to tab it in again just like with the calculated table right underneath the formula bar you're seeing the syntax

of the date diff function the first argument would be date one the second one would be date two and the third would be interval it's waiting for The date one argument right now which is why that's in bold so I'm going to start typing orders for the name of the table and I'm going to arrow down in the list until order date is highlighted and I'm going to tab it in and we want the entire date so a subsequent menu comes up date is already selected and I'm going to tab that in now we're going

to use a comma which separates the arguments you'll notice Now that date 2 is in bold as that's the argument it's looking for we want the orders ship date for that second date so I'm going to start typing orders again and I'm going to just down arrow until ship date is highlighted and tab it in and again we want it to assess the full date so I'm going to tab in date and I'm going to type a comma the interval we want the difference in dates in is days so day is already Highlighted I'm going

to tab that in and I'm going to press enter okay you'll see your screen flicker while it's doing the calculation and now if you look all the way to your right the last column in the orders table is days to ship in the interval of days go ahead and save your desktop file we created the days to ship column by right-clicking on the orders table and choosing new column Since we're in the days to ship column we have the column tools tab on our ribbon and that is another way that we can create another calculated

column we're going to create a calculated column that shows a ranking by sales so the highest sales values will have the rank of number one and the lowest ones will have the rank of a higher number depending on how many sales values are in the orders table we're going to be using the rank X Function to do this and it has several variations we're going to go through three of them on the column tools tab of the ribbon the last icon is new column make sure you're anywhere within the orders table in the fields Pane

and then click the new column button it's going to do the same thing as when we right clicked on orders and chose new column in the formula bar We're going to go ahead and rename the column so I'm going to double click the word column and I'm going to call it sales ranking highest sales ranking highest and then I'm going to get myself after the equal sign and the function we're using is rank X so if you start typing it r a n you will see it on the list and you can double click it

or highlight It and tab it in so notice this particular function has five possible arguments whenever you're looking at the syntax uh arguments that are optional will be in square brackets so there are two required arguments and three optional ones for this example we'll be using the two required arguments table and expression for the table argument we're going to reference the orders table so it has a Separate table argument so I'm going to just start typing the letter O and the orders table by itself shows up on the list and I'm going to tab that

in now I'm going to do a comma so I can get to the expression argument what we're assessing here is we want a ranking of the sales field in the orders table so I'm going to start typing orders again and then it'll show me the table as well as all the fields in the table and I'm going to highlight orders Sales and tab that in we're ignoring the three optional arguments right now and we can just press enter here so it performs this calculation in the newest column which is all the way over to your

right remember the highest ranking sales would be numbered starting with one the lowest sales values would be higher numbers at this point go ahead and save your desktop file We're going to edit the rank X function we just did because we want to rename it sales ranking lowest and we're going to use another argument so that the lowest sales values have the lower rankings starting with one and the highest sales values have the higher ranking numbers we're going to do our edits in the formula bar so the first thing I'm going to do is I'm

going to double click the word highest and change it to lowest and inside the parentheses I'm going to click after order sales after the closing square bracket and in between the parentheses and I'm going to type a comma there and the syntax will show up again when we type the comma it's waiting now for the optional value argument and we want to skip that argument so we're Going to type another comma and it will advance to the order argument we have ascending and descending it's defaulting to descending which is why we're getting the lowest sales

having the higher numbers and the higher sales having the lower ranking numbers we're going to switch the order to ascending so ASC is already highlighted I'm going to tab it in and I'm going to press enter and you'll see that the sales ranking Lowest column it's now doing the opposite the lower sales values are having the lower number rankings and the higher sales values are having higher number rankings let's talk about how rank X handles ties so imagine we had two sales values of I'll just say one dollar each because we have it in ascending

order now if those were the lowest sales values they would each be ranked number one And then it would skip the next number since you have two at number one you wouldn't have anything ranked number two it would move to the next number which would be three unless you tell it otherwise and that's the last optional argument so in order to demonstrate this I'm going to go ahead and open the Excel sample Superstore file so we can make a change in there we're going to change one value in this Excel file to force a tie

in sales values I'm going to use the name box up here in the upper left corner and I'm going to just go into the name box and I'm going to type w369 and press enter and it will take me to that cell so cell w369 is selected and I want to change that value in that cell to 52.97 mimicking the value above it so I'm going to just type 52.97 press enter And I'm going to go ahead and save and close this Excel file to get that change to show in desktop I'm going to have

to refresh and that's on the Home tab at a ribbon in the queries group go ahead and click the refresh button so it will bring in the changes we just made in the source data file now let's take a look at the order IDs that we have the tide sales values in so I'm gonna go to the order ID Auto Filter drop down and in the search box you're going to type 8853 and you'll see several orders come up order IDs with those numbers in it what we want to do is we want to uncheck

select all and you're going to check 88538 and 88539 and press ok you'll see that both of these orders have the same sales value And the ranking is the same 546. we're going to clear our filter from order ID Now by going to the funnel and selecting clear filter so we get our full list back and go to your sales ranking lowest Auto filter and when you get in there go into the search box and type 547 so that would be the next ranking we Had two at five four six right and you'll see there

is no ranking five four seven because it's going to skip that number if we had three at the same value they would all be five four six and then it would skip two numbers change your search to 548 and you'll see that that ranking is in the list and you can just cancel the auto filter box skipping is the default with the rank X Function so again when you have sales values that are the same and they tie and their number in the ranking is like 546 and there's two of them at that number it's

going to skip 547 unless you tell it not to do so by using the optional last argument so we're going to modify this rank X function again we're going to first go up to the formula bar and we're going to change it after the word Lois put no skip so we'll know this is the One that's not going to skip any numbers and after that you're going to click after ASC and inside the closing paren and type a comma so you can see that last optional argument is the ties argument if you don't use this

argument it defaults to skip and we've seen that behavior we're going to tell it to use the dense argument so it won't skip any numbers so since it's already selected I'm going to tab it in and I'm going to press enter so it recalculates now keep in mind since it's not skipping numbers the rankings have changed if you want to look up order IDs 88538 and 88539 you'll see that they're still tied but at a different number the subsequent number if you filter for sales ranking lowest no skip will be the next number it won't

skip any numbers go ahead and check that out and then save your file So far in this module we use the distinct function to create a calculated table it returned a table with one column of distinct values then we created a few calculated columns we used date dis and the rank X function to create these columns both the table that we created and the columns that we created become part of your data set now we're going to switch gears and talk about measures measures could be called virtual calculations they don't become Part of your data

set they only calculate when you add them to a report visualization so if file size is an issue you may want to use measures instead of calculated columns or calculated tables there are two variations of measures there are quick measures which are templates of sorts and then there are measures which you build from scratch we're going to get started using quick measures We want to create a quick measure in the orders table that will give us the average sales based on customer segment so you'll notice you know in the orders table we have the sales

field we also have a customer segment field to create the quick measure in the fields pane I'm going to right click on the orders table and I'm going to select new quick measure the dialog box will open for quick measures and on the left side you have Calculation and a drop down and on the right side you have your fields from all of your table instances just like you do in other views where it says select a calculation on the left side we're going to go ahead and do the drop down you want to take

a moment and look through they have different headings in here aggregate per category filters time intelligence so on and so forth The one that we want to use is we're looking for one under the total section and we're going to select total for category filters not applied that's the calculation we want to use for the base value we're going to expand the orders table on the right side and we're going to just drag the sales field into the base value text box and it says sum of sales but we want the average of sales So

I'm going to go over to the ellipses The more options button and instead of sum which is the default I'm going to click on average and for category in the fields list we're going to grab the customer segment field and drag it into the category box so a quick measure allows you to make choices in this dialogue and it will create the calculation for you in the bottom right corner go ahead and click ok And again it won't become part of your data set so when you do a calculated column it puts it at the

end on the right of your data set this is a virtual calculation but if you look up at your formula bar you'll see based on the Selections in the quick measures dialog box it built a nested calculation for you it used to calculate function with a nested average and a nested all function foreign Pane and notice the icon in front of it That represents that it is a measure now you won't be able to see this like in data view you can only see it on a report visualization we haven't gotten to visualizations yet but

let's go ahead and build a simple one so we can see the result of your measure so on the left side I'm going to go to report view the first view button foreign view just go to your Fields Pane and drag your average of sales total for Customer segment measure directly into the center of your screen where it says build visuals with your data again we'll be doing a deeper dive in reporting visualizations in a later module but I just want you to see the results of your measure also in the fields pane you're going

to go ahead and grab the customer segment field and drag it to the axis box right in the visualizations pane drag it and drop it We're going to do one other field for this we're going to grab product category field and we're going to drag it to the small multiples box in the visualizations pane and I'm going to just use the sizing handles on my visualization to make it wider and you can see the average of sales total for customer segment and broken out by product category so if I hover over the First Column under

Furniture you'll see the tool tip it tells me the average of sales 2139.07 for technology when I hover over it you'll see the tool tip update so on and so forth so it didn't calculate that quick measure until we added it to a visualization and with your visualization still selected and you can see the sizing handles around it just go ahead and press delete on your keyboard to get rid Of it and save your desktop file now we're going to create a measure from scratch to calculate the average sales per product category for the orders

table so I'm going to just right click on orders again in the fields pane this time I'm going to do new measure instead of new quick measure and it just brings up the formula bar so we're going to name this measure average sales per product category Remember whatever is before the equal sign is the name of the item so average sales per product category and navigate to after the equal sign so we can use the functions that we're going to use we're going to start with the calculate function so start typing it in and by

the way when it's highlighted on the list to the right of the highlight it tells you what the function does so this evaluates an expression in a context Modified by filters so we're going to go ahead and tab in calculate the first argument is expression we want to calculate the average of orders sales field so for the expression argument we're going to use the average function start typing average and when you see it you're going to go ahead and tab it in just plain average and we want the average of the sales field in the

orders table so I'm going to start typing orders and I'm Going to just do my down arrow until orders sales field is selected and I'm going to tab it in now we want to get back to the calculate function at this point so after order sales we're going to go ahead and type a closing paren and it takes us back to calculate we're still in its expression argument and now we want to advance to the filter argument which is optional so we're going to type a comma and it takes Us to that filter one argument

for the filter one argument we're going to use another function the all selected function so I'm going to start typing all when I see it on the list I'm going to highlight it so it tells you that all selected returns all the rows in a table or all the values in a column ignoring any filters that might have been implied inside the query but keeping filters that come from outside let's talk about what that means for a moment In a later module when we do a deep dive into reporting visualizations you'll learn how to use

the filters pane in report view to apply filters for your visualization that would be considered filters applied inside the query a filter that comes from outside the query would be for example the slicer visual visualization which would be a separate visualization so it would be considered outside of the query a slicer is a way of visually filtering so when You're using all selected it would ignore any filters inside the query for example like on this filters pane but it would keep filters that you can control from outside the query in our case it's going to

return all of the product categories we don't have any filters so it's going to return all of them so I'm going to go ahead and tab in all selected and I'm going to go to the orders table so start typing Orders and we want the product category field this time from the orders table and I'm going to get that in we're going to need to type two closing parentheses at the end of this and press enter so when you do that it sets up the measure for you it shows in your field Pane and again

it will only show on a reporting visualization as it is not part of your data set Foreign Superstore file the final lesson in this module is using time intelligence functions and key performance indicators we're going to get started with time intelligence functions in order to use these types of functions in power bi you have to have what is known as a date table in your data model we don't have a date table in our data model so we will Create one there are two different Dax functions that you can use to do this one of

which is named calendar for the calendar function to work you have to provide it with a start and an end date and it will build a table for you with one column with all of those dates calendar Auto is the second function and that one can scan your data and determine the earliest date and the Latest date in your data model we're going to use calendar Auto it will return one column with all of the dates in the data model after we create that table we're going to amend it by adding other columns so let's

get started in your Fields pane make sure the orders table is selected and then on the table tools tab of the ribbon you're going to click new table in the formula bar we're going to double click on the word table and we're going To name it dates and navigate to after the equal sign we're going to use calendar Auto so I'm going to start typing CA when it shows up on the list and you'll see that it says it returns a table with one column of dates calculated from the model automatically I'm going to go

ahead and tab it in and we are just going to do a closing parenthesis at the end and press enter So if you look over in your Fields pane you'll see the dates table when you expand it it has one column called date and you can take a look at the dates if you go over to your left go to data View and you'll see that it has a date range starting from January 1st 2010 and it goes all the way through 12 31 2014. so our earliest date and the latest date in our data

model and every date in between is in one column in this New table foreign to having the full date column we would like in this table to have the year of each date in a separate column as well as the quarter and the month in two different ways so we are going to Nest our calendar Auto function within an add columns function and to do that you're going to click after the equal sign right before calendar Auto in the formula bar And you're going to type start typing add and add columns function comes up it

does exactly what it sounds like it's going to add more columns to this table I'm going to go ahead and tab it in now we're going to click after the closing parentheses after calendar Auto and we're going to type a comma so this is now part of the add column syntax it wants the name of the column that we're adding we're going to have to Put it in double quotes because it's text so in double quotes I'm going to type year and close the quotes and type a comma and I'm going to use the year

function to extract the year of the date so I'm going to start typing year it shows up on the list I'm going to tab it in and what you're going to do is you're gonna just in square brackets you're going to type date well when you type the squared brackets you'll see Dates show up on the list so I'm going to select it from the list instead of typing it so to extract the year of the date now I'm going to do a closing paren to close out the year function and I'm going to type

a comma now I don't want this to be one long run on function so I'm going to press shift enter to get down to the next row to continue this function the next thing we would like to extract Is the quarter so we want another column in double quotes we're going to name it quarter comma and we want the letter Q before the quarter number so in double quotes we're going to type the capital letter q we're going to use the Ampersand for concatenation meaning combine the Q with the quarter number and we're going to

use the quarter Function so go ahead and start typing it and get it in on that bracketed date field so I type the square bracket and it popped up on the list we need a closing parenthesis another comma and you're going to shift enter again to get down to the next line we want to extract the month of the date in two different ways we want the short name of the month or the full name of The month actually and we want the month number in two separate columns so we're going to name another column

month and that's going to be in double quotes comma and we're going to tell it how to format the date so we're going to use the format function and you can get that in there and we're doing it on the date field so I type the square bracket and I'm Grabbing it from the list we're going to do a comma and now we're going to tell it what format so in double quotes I'm going to Type 4 lowercase M's to indicate give me the full name of the month if we did three M's it would

be an abbreviation of the month we're going to close the double quotes closing paren one more comma and shift enter We also want to extract the month number so we want to have a column called month number we're going to do that in double quotes comma we're going to use the month function here the month function extracts the number of the month and we're going to do it on that bracketed date field which is the only field in this table so far we're going to do a closing paren And I'm going to do shift enter

one more time and type one more closing paren now when you press enter the dates table will update and it will show the additional columns that we added so calendar Auto only returns one column the date column we use the add columns function to add more columns to this table go ahead and save your file Once you have your date table created you need to mark it as a date table so power bi will know which table to reference when you're using time intelligence functions we're gonna in the fields pane we're going to right click

on our dates table and offer the shortcut menu you'll see Mark as date table hover over that and then click on Mark as date table the dialog box opens and it needs you to just select the column in this case the Column to be used for the date in our new dates table we only have one column and that is the date column that it automatically created the column that holds the full date so that's the only one that's going to show up on the list here it tells you it was validated successfully and you

can click ok so we've just created a date table added more columns to it and marked it as a Date table the last thing we need to do is we need to relate the date table to our orders table let's go to model view on the left side last view button and we're going to do it based on the order date field in the orders table so I'm going to click and hold on the order date field and drag it and drop it on top of the date field in the dates table And let it

create that one to many or many to one relationship many order dates can be in the dates table is what's that what that is saying go ahead and save your file foreign table related to our orders table let's go back to data View and we're going to scroll to the right since we did that relationship you'll notice that our days to ship calculated column is filled with arrows Because we're telling the orders table to look at the dates table it's not recognizing the dot date portion of the order date or ship date fields anymore so

in order to resolve that we're going to get rid of them in the formula bar so in your date diff function after orders order date you're going to delete the dot date that's in Brackets after that so we want orders order date And then just a comma and we're going to do the same for orders ship date we're going to get rid of the dot date after it the qualifier and we're going to press enter after we do that and it will recalculate now sometimes it'll try to put the dot date back in there if

it does that just go back and delete it again and press enter and it will update so now you have your days to ship the reason that happened is We did that calculation between dates before we created a dates table and related it to the orders table so now it's looking at the dates table for information we're going to add two more calculated columns to our data set for the orders table we want one to show the end of the month for each order and another to show the end of the quarter for each order

go ahead and right click your orders Table in the field Pane and choose new column and we're going to name this column in the formula bar you're going to name the column end of month and position yourself after the equal sign well one at a time intelligence functions in power bi is end of month so start typing end of month when you see it on the list you Can tab it in and it wants to know the end of the month for what date so it's the orders order date so I'm going to start typing

orders and then I'll see the table as well as all of its fields and I'm going to just go down until I have orders order date highlighted and tab that in foreign you can go ahead and press enter and you'll see the end of month column Populates and it has the last day of each month for each order that's listed in the orders table we're going to do another new column in the orders table and this one is going to be for the end of quarter so I'm going to right click on orders again choose

new column I'm going to name the column end of quarter and get after the equal sign and there Is an end of quarter function which I'm going to use on the same orders order date column and press enter so we just increased our data set by adding two more calculated columns end of month and end of quarter let's go ahead and format these two columns so they match the order and ship date column formats I'm going to click in the end of month column and on the column tools tab of the Ribbon I'm going to

access the format drop down and select the date format that says March 14 2001. do the same for the end of quarter column we're going to use a different desktop file for our final Topic in this module so go ahead and save and close your sample Superstore desktop file open the retail analysis sample desktop file that you grabbed from the video Description and we're going to use this file for key performance indicator visualization this file has more detail than a sample Superstore file on the left side let's go ahead and go to data View and

if necessary expand the sales table in the fields pane we have comparative columns in this data two of which of our interest to us for our key performance indicator There's a calculated column called total units last year and there's one called total units this year that gives us the ability to compare our progress I'm going to collapse the sales table and expand the time table the timetable has a fiscal month column and we're going to use that to be able to see a comparison between last year this year by fiscal month a key performance indicator

commonly Known as a kpi is a critical or key indicator of progress toward an intended result it's a visualization type in power bi let's go to report View again that's the first view button on the left hand side and when you get to report view do the plus sign at the bottom of the screen to create a new page when we get to module 7 we'll do a far deeper dive into reporting Visualizations but for right now we're going to use the fields that we looked at to start creating our kpi now before you create

the kpi you actually start building the report without the framework of a kpi the reason why is once you convert the report that you build into a kpi you won't be able to have sorting capabilities so this is how it works In the fields pane we're going to expand the sales table and we're going to grab the total units this year calculated column and drag it right into the center of your screen onto the canvas now we're going to expand the time table in the fields Pane and we're going to drag the fiscal month field

into the highlighted box on the canvas at this point this is not a kpi visualization it's a clustered column Chart let's expand the chart so it's a little bit wider we can use the sizing handles and if you want to move it you can click in a blank area and you can move it around on the canvas this is the point where you would want to perform a sort before you turn it into a kpi visualization we'd like to sort this in ascending order by fiscal month so in the upper right hand corner of the

Visualization you'll have the more options vertical Ellipsis icon and we're going to click that and hover over sort by at the bottom you're going to click on fiscal month and you'll see that it's sorted it by fiscal month if we go back to the ellipses you can see that it's sorted it in descending order and we're going to want to click on sort ascending now we have it sorted in ascending order Go ahead and save your retail analysis sample file and with your visualization still selected you can tell it's selected by the sizing handles around

it we're going to convert it to the kpi visualization in the visualizations pane you want to find the kpi visualization it looks like it has a green triangle and a red triangle on it kind of looks like a table and I'm going to point to it now on my Screen when you find that visualization go ahead and click on it and it converted our column chart into a kpi visualization when you look in the visualizations pane now you'll see that it put total units this year as the indicator it has fiscal month as the trend

access and now we just need to add a field for Target goals so we're comparing total units this year to Total units last year In the fields pane I'm going to grab total units last year and drag it into the target goals box and now you'll see our kpi visualization is complete in terms of the comparison in the visualization the Shaded area in the back is your goal area it tells you the value and then it tells you the goal and along with the goal it gives the Percentage difference in this case we're negative and

so it also indicates a negative response with the exclamation point to the right of the value the last thing we're going to do here is rename this page page one we're going to double click on it and we're going to name it kpi foreign press enter so it will accept the page name change and go ahead and save your retail Analysis sample file in this module we explored the world of Dax data analysis Expressions we learned that it's the formula language used in power bi we use Dax for simple formulas and expressions and we created

calculated columns and tables based off of Dax functions we also created quick measures and measures both virtual calculations we worked with time intelligence Functions after creating a dates table and you learned how to create a key performance indicator visualization in the word document that's in the video description it's called website links and more information there are links to more information about Dax functions from various sites module 6 is optimizing model performance you'll be introduced to steps processes Concepts and data modeling best Practices necessary to optimize a data model for Enterprise level performance we'll be using

the retail analysis sample desktop file from the previous module and the sample Superstore desktop file we created in module 2. in this module we'll use the direct query method to access a data source will understand the importance of variables and how to use them in Dax functions and we'll cover some other optimization Techniques so far we've imported data into Power bi when doing so we've loaded all the data from the data source or a large subset of the data from this data source into Power bi desktop in some cases this creates a large file size

and causes some performance issues when you refresh in power bi desktop it literally reloads all the data back into the data model depending on the size of the model this could be a lengthy Process that you perform multiple times a day if file size is a consideration and the source data is very large and or data is changing frequently and reports must reflect the latest data you would use direct query direct query connects directly to data in the original Source repository for example SQL server or Azure analysis services and no data is actually imported into

Power bi when visualizations are created queries are sent to the underlying data source to retrieve necessary data upon refresh the necessary queries are recent for each visual for updating when publishing reports to the service you will see a data set as well as the reports however no data is included in the data set the data resides in the source Repository there's additional detailed information In the word document website links and additional info in the video description it'll let you know all of the data sources that you can use direct query on we're going to get

started by using the retail analysis sample desktop file where we created our kpi in the previous module one of the data sources that you can use direct query on is a power bi data set in order to use a power bi data set for a direct query that data set needs to be Published to the service so we're going to go ahead and publish this data set to the service we did this in a previous module and again in a later module we'll spend more time examining the service when we set up our dashboards but

in the meantime the last button on the Home tab of the ribbon is publish go ahead and click on it to start the process so we all have in common a workspace Named my workspace if you don't have any other workspaces available to you you can use that one I am going to use my power bi video workspace by selecting it on the list it may take a few moments to publish this as this is a fairly large data set when it's done publishing you'll get the success check mark and you can get to the

service by clicking the link that says open retail analysis sample.pbix in power bi go Ahead and click the link it opens the report which is all of the pages that are in our report view in the desktop because I was on the kpi page that's the page that it's on here in the service we want to navigate to the workspace where we sent this data so on the left side of your screen almost toward the bottom you're going to hover over the workspaces icon and select it I'm going to click on my power bi video

workspace and you'll notice that it put the report which is what we were just looking at as well as the data set the underlying data in the service because the data set is now in the service we'll be able to use direct query in a new instance of power bi desktop I've switched back over to the desktop and I'm going to click on got it on the Publishing the power bi screen we want to access direct query from a new power bi file so we're going to go up to the file tab of the ribbon

and select new on the left hand side to start a new instance of desktop when it opens go ahead and close the splash screen before we access our power bi data set that we published I just want you to know that the data sources that are Supported with direct query depending on which one you choose you're going to have to do something different maybe so for example SQL Server is a data source that's supported by direct query on the Home tab of the ribbon in the data group go ahead and click on SQL Server and

you'll notice that it has a data connectivity mode section it defaults to import so if you want to use direct query to Connect to SQL Server data you would have to use the option button for direct query we're going to go ahead and cancel that dialog when you're bringing in from a power bi data set it automatically is in direct query mode so you won't have to make a choice like you would have for SQL Server so on the Home tab of the ribbon we're going to go ahead and click on power bi Data sets

and it's only going to show you the data sets that are published to the service we're going to click on retail analysis sample and then the create button in the lower right corner before we do anything else let's save this file and we're going to name it direct query right now there doesn't appear to be a difference between using import or direct query to bring data into the Desktop on the right side you still have your Fields pane if you expand the sales table you'll see all of the fields but what I want you to

notice is on the left hand side on the left side you no longer have data view you just have your report view which is default and you have modeling view there is no data view because it didn't actually bring in any data from the underlying source if you go to modeling View You'll be able to see the fields in the tables and the relationships that have been created in this data I'm going to go back to report View and we're going to recreate the kpi report that we did in the previous module so we're going

to expand the sales table in the fields pane and we're going to grab the total units this year field and drag it onto the canvas And then we're going to expand the time table and we're going to drag fiscal month into the framework on the canvas as well next we're going to access more options in the upper right hand corner of the visualization and we're going to hover over sort pi and we're going to click on fiscal month we're going to go back to more options and click on sort ascending so remember you have to

sort before you Turn it into a kpi because the kpi doesn't allow sorting I've resized the column chart a little bit and now in the visualizations pane I'm going to go to the kpi visualization and convert this into a kpi so when we did that it actually sent a question to the underlying data source to retrieve the information for this visualization go ahead and Save and close your direct query file and Also close your retail sales analysis sample desktop file navigate to your working directory where you have saved your direct query desktop file and the

retail sample analysis file and look at the difference in file size retail sample analysis is over 9000 kilobytes has a lot of data that we imported into Power bi desktop direct query however didn't bring any data in and so therefore that file is only seven kilobytes if file size is a Consideration and your data source is supported by direct query it's recommended that you use direct query our next lesson in this module is about variables we're going to be using the sample Superstore desktop file if you want to pause the video and launch that file

so what are variables let's get some background information you've already been exposed to Dax functions we've nested functions so you Can see as a data modeler writing and debugging some Dax calculations can be challenging if you get an error in your formula or function you have to debug it it's common that complex calculation requirements often involve writing compound or complex Expressions as you've already experienced compound Expressions can involve the use of many nested functions and possibly the ReUse of expression logic using variables in your Dax formulas helps you Write complex and efficient calculations so that's

the why you would want to use a variable what is a variable you can store the result of an expression as a named variable which can then be passed as an argument to other measure expressions once resultant values have been calculated for a variable expression those values do not change even if the variable is referenced in another Expression and we're going to focus on variables and how to use them in our calculations in this lesson so what can variables do for you why are they important they can improve performance they can improve the readability of

your functions and expressions they can simplify fixing them if they're broken also known as debugging and they can reduce the complexity Of a calculation we're going to start by creating a basic measure to calculate the total sales from the orders table so over to the right in the fields pane I'm going to right click on orders and choose new measure we're going to name the measure so I'm double clicking the word measure in the title bar and I'm going to name it total sales and navigate to after the equal sign And I'm using the calculate

function with a nested sum X function here so I'm going to start typing calculate and I'm going to tab it in from the list and then I'm going to start typing sum and I'm going to highlight some X and tab that in as well so the first argument for some X is the table I'm going to start typing orders and when I see the table on the list I'm going to tab it in I'm going to type the comma to separate Arguments and the expression is going to be on the orders sales field so I'm

going to start typing orders again and I'm going to just down arrow until order sales is highlighted and I'm going to tab that in and I'm going to press enter at this point if there was an error you would have red highlight up here and it would be letting you know and again with a measure it won't show until you use it On a visualization but if you look in your Fields list you'll see the total sales measure we created that measure so we can use it in another measure that we're going to create where

we're going to also use a variable now we're going to create another measure in the orders table that includes variables so I'm going to right click on orders again and select new measure In the formula bar I'm going to name this one 2010 total corporate sales 2010 total corporate sales and I'm going to position myself after the equal sign we're going to go ahead and press shift enter so we get another line this is going to be a multi-line function so you don't ever want to be in a position where you have to read a

long line from left to right especially when you're troubleshooting And we're going to use the VAR keyword to start our variable declaration that's where you name it and tell it what it's going to contain so I'm going to type VAR VAR is a keyword it will not show up on the function list they have variant functions that show up on the list we just want VAR plane so we're going to do shift enter afterwards to come down to the next line we're going to name this variable Corporate sales I'm going to type Capital C corporate

no space in between capital S Sales and an equal sign so this is where we use a nested function what we want is we want all of the corporate sales right now from the orders table the type of sale is in the customer segment so we're going to use the filter function go ahead and grab that with a nested all Function grab the all function and we're going to refer to the orders table customer segment field so when I start typing orders and I see orders customer segment on the list I'm going to highlighted and

tab it in and we're going to do a closing parenthesis so that orders customer segment is the table name or column name for the all function we're going to do a closing parenthesis to return to the filter function and we're going to type A comma to get to the filter expression argument so this is where we're going to tell it to filter only for corporate customer segment so we're going to reference orders customer segment field again and we're going to type in equal sign and then double quotes corporate because it's a text field it has

to be in double quotes and we're going to do a closing parenthesis Shift enter to come down to line four so right now we declared a variable called corporate sales where it's going to only bring us from the orders table all of the sales for the customer segment known as corporate now we want to declare another variable in this variable we'll tell it to only use 2010 dates so we're going to do our VAR keyword again and shift enter We're going to name this variable capital I included capital D dates and we need an equal

sign afterwards we'll go ahead and go down to the next row so I'm shift entering again and this is going to be another filter all so I'm going to start using the filter function and then I'm going to bring in the all function this time we're using our dates table year field so I see it on the list I'm going to grab It date year and I'm going to do a closing paren to come out of the all function a comma to advance to the filter expression argument for the filter function and we're going to

reference the dates year field again and this time we're going to type equal 2010 and a closing parenthesis shift enter so so far we've declared two variables And told it what the variables will contain and now the other part of declaring a variable is telling it what to return so what do we want our end result to be we're going to use the keyword return and shift enter foreign and we're going to use the calculate function here and we want to calculate the total sales measure that we created earlier so since it's a measure it

will show up If you type an open square bracket you'll see your measures that are in the orders table and we're going to grab total sales measure we're going to type a comma shift enter and now we're going to call those two variables so we're saying calculate total sales but we only want them for the customer segment corporate and we only want them for 2010. so all we have to do is type the name of the variable so corporate sales is the first one Notice the icon in front of it in the list that's the

X Y icon indicates it is a variable a name variable I'm going to tab it in type a comma and shift enter and I'm going to start typing included dates which is our second variable and I'm going to grab that and get it in there and a closing paren now at this point the return is what's really happening here it's going to calculate total sales But it's going to be filtered for the corporate customer segment and for the year 2010 order dates so when you do something like this or any Dax calculation really you might

want to get in the habit of putting comments in that explain what the calculation is doing if the file is being shared with other people they'll be able to see your comments and even you your future self Like six months down the line you might look at this and say what did I do comments will be helpful then as well so we're going to do shift enter one more time and we're going to type two forward slashes notice those slashes turn green that indicates that what comes afterwards is a comment when this calculation happens in

this measure when we add it to a visual it won't try to calculate comment lines so we're going To type this calculation is only for 2010 corporate sales if you wanted to be more detailed you'd explain the variables here and what they're representing we're going to go ahead and press enter and we shouldn't get any error messages for this and in your Fields pane you'll see that 2010 total corporate sales measure the variables we declared in the 2010 total corporate sales measure are only Available in that measure they're scoped to that measure if we want

to to create a similar thing but for 2011 total corporate sales we would have to recreate the variables instead of doing that it's more efficient to just copy the function for 2010 corporate sales so I'm going to go up to the formula bar and just select everything including the comments and I'm going to do control C to put it on the clipboard I'm going to right click on the orders table again in the fields Pane and select new measure and I'm going to use Ctrl V as in Victor to paste it now I just have

to update it so I'm going to start with the name of it I'm going to call it 2011. total corporate sales the corporate sales variable is fine the included dates variable needs to be updated to 2011. and the comment should be updated as Well and then I'm going to press enter so now I have two measures one for 2010 one for 2011. I'd also like the same thing for 2012 and 2013. so I'm going to give you the opportunity to create those on your own when you're done you should have all four of those total

corporate sales measures in your Fields Pane and now we're going to use a multi-card visual to see all of the numbers so you're Still in report View and what you're going to do is you're going to go to your visualizations Pane and you're going to look for the multi-roll card visualization and click it and I'm going to just expand the width of the visualization and then in the fields pane I'm going to click and hold on 2010 total corporate sales and drag it into the card visualization do the same with 2011. 2012 2013 and now

you have a card showing the differences in sales for that corporate customer segment over the four years go ahead and save your file the last lesson in this module is other optimization techniques so in the next module we'll start getting into Power bi reports and we'll go over these optimization techniques specifically geared toward your Visualizations when we get into the next module but for right now one of the things you can do to optimize your reports is apply to most restrictive filters to them instead of having one visualization trying to show everything you might want

to break them down by filtering you also want to limit the amount of visuals on any one report page I know that everybody's into grouping them together and that's fine unless it Becomes a performance issue for you and there are custom visuals that you can gain access to and you want to evaluate how they perform if they're not performing well then you probably don't want to use them when it comes to optimizing the environment the three things listed on the slide are things that typically the IT department will be involved with there is more detailed

information about capacity settings Gateway sizing and Network latency in the word document website links and additional information in the video description so to recap this module we started by using direct query for enhanced performance up until then we had been importing the data directly into Power bi which creates a large file size if you have a huge data set and when we use direct query it directly connects to the data when it needs to build report visualizations instead of Actually storing the data in a data model and you saw the file size comparison we use

variables and Dax functions to reduce complexity so the result of the expression is stored in a variable upon declaration it doesn't have to be recalculated each time it is used as it would without using a variable so that could be another performance issue that's avoided if you're using variables We reviewed a few other optimization techniques and again there's more detailed information about all of the lessons in the website links and additional info Word document in the video description hi everyone I'm Trish Connor Kato and I'd like to welcome you to Microsoft power bi we've built

some report visualizations along the way in this course but module 7 is going to take us on a Deeper dive of the full capability of Power bi's reporting feature you're going to be introduced to the fundamental concepts and principles of Designing and building a report that includes selecting the correct visuals designing a page layout and applying basic but critical functionality the important topic of Designing for accessibility is also covered in this module We'll be using the retail sample analysis desktop file we've been working in and a histogram Excel file that is in the video description

below we have multiple lessons in this module as you can see on the slide everything from designing and creating a report through accessibility take a few moments and review the lesson topics and then switch to your retail analysis sample file There are many methods used to design a report some people like to draw out a report on a piece of paper and design from that others like to look at previously created reports Maybe by other people that you see and you use those as a basis for your design in the retail analysis sample file let's

go to the overview page so maybe you're looking at this and Someone else created this report and you see a couple of things on here that you would like to design you can use these as a starting point for your visualization design other people like to design from scratch there's no right or wrong answer you can design from whatever perspective you want to design from we'll revisit these already created report Pages later in this module for some tips and tricks on how to recreate Some of these visualizations for now we're going to start a new

page by clicking the plus sign to the right of your page tabs and we're going to create a pie chart visualization in the visualizations pane you're going to locate and select the pie chart visualization and it puts the framework of the visualization on the canvas I'm going to go ahead and resize the framework so It's about the width of the canvas that's not a necessary step it's just something I choose to do now and in this pie chart we want to show Regular sales units and markdown sales units so in the fields paying to the

right you're going to expand the sales table and you're going to identify the regular sales units field and again if you want to see more of the field name you can expand the fields table By going in the border between it and visualizations and clicking and dragging so we're looking for the field in the sales table that's called regular sales units and I'm going to just drag it into the framework of my pie chart that's one way of getting that field in there now we want to look for markdown sales units field and this time

what I'm going to do is I'm going to check the box in front of it in the fields pane that's Another way of getting it into the pie chart let's go ahead and save our file and then we'll get into some formatting of this pie chart that we just created the first thing I want to draw your attention to is if you hover your mouse over any of the pie slices you'll see the value and percentage associated with that slice that is known as a tool tip whatever Fields you use in the visualization will automatically

show up In the tool tips but you can add other fields other pertinent information you'd like to see when you hover over a slice of the pie if you'll notice in the visualizations pane you're in what's called right now the fields well those two boxes with the yellow underline if you hover over it it will say fields for a pie chart you get Legend details values and Tool tips Fields depending on The visualization type that will change in the visualizations pane what we want to do is over in the fields pane we want to find

two different fields and we're going to add them to Tool tips again the fields that you're using in the visualization automatically show up as tooltips when you hover although they don't show in the tooltips field In the fields pane find regular sales dollars in the sales table and drag that field to the tooltips Box also find markdown sales dollars and drag it into the tooltips box and you can drag it underneath regular sales dollars now when you hover over a slice you'll see the markdown sales units that's the red slice I'm hovering over that is

the field that we used in the visualization and you'll also now see Regular sales dollars and markdown sales dollars because we added those fields to Tool tips now we want to apply some formatting to our pie chart specifically we'll have a conversation about whether we want a legend on the chart or the detail labels that are currently showing where it says markdown sales units and regular sales units on the pie chart we also want to change the title of the Chart so it's more succinct we want to give the chart a background color and a

shadow effect in order to do all of those things we need to get out of the fields well in the visualizations pane and go to the format well the format well is represented by the paint roller so you'll notice the options that are available here in the format well for a pi chart visualization different types of visualizations also come with some Same and different formatting options so right now we don't have the legend on on the chart so where it says Legend you can toggle off to on on the switch and now you'll see underneath

the title in the left corner of your chart is the legend when I have a legend on my pie chart I often find a detail label to be redundant so I decide I want to just go with the legend and there's another format option for Detail labels I'm going to turn that off by using the toggle switch I'm going to expand the title format option by clicking the down arrow and I want to change what the title text says so what I'm going to do is I'm going to click in title text and I'm going

to do control a to select everything that's in there and I'm going to just type regular sales and markdown sales units thank you And as you're typing it's updating the title on the chart you have other title formatting options I decide that I want the chart title to be Center aligned so I'm going to click on the second alignment button and you'll see that it instantaneously happens on the pie chart now I'm going to collapse the title format category and I'm going to expand the background category and enable it by using the off Slider so

that it turns the feature on I'm going to do the color drop down and select a comparable color for the background of my pie chart visualization the last thing we're going to do with the pie chart formatting is apply a shadow effect to it for more visual appeal so I'm going to expand the shadow format category enable it and I'm going to do a slightly darker Color for the shadow and you'll notice there is a shadow effect on the bottom and the right side of the visualization we should probably rename the page from page one

that's not a very descriptive report page name so I'm going to double click on the page one tab and I'm going to just type reg for regular versus markdown and press enter Go ahead and save your file when you deselect your report by clicking on a blank area of the canvas and you go back to the format well by clicking the paint roller you'll notice that you have different information than we had for the pie chart visualization for example if you wanted to change the size of the report page you could expand page size go

to the drop down next to type you can choose something off the list or custom And put in your own size so depending on whether a visualization is selected the format well will change and when you have multiple visualizations on a report page it's important that you have the right one selected before you go to the format well we're going to create a clustered column chart on a different page now so go ahead and click the plus sign so we can generate a new report page And in your visualizations pain you want to find the

clustered column chart and click on the icon I'm going to expand the size of the chart you can do this before or after or both doesn't matter and we want to use information that is also in the sales table we want to see this year's sales last year's sales the total sales variance and we want to see it by Chain So what I'm going to do is in the sales table in the fields pane I'm going to expand this year's sales if it's not expanded do the arrow in front of it and I'm going to

check the box in front of value we want this year's sales value then I'm going to go and check the box in front of last year's sales and it adds that into the clustered column chart framework as well Go ahead and check the box in front of total sales variance and that gets added to the chart now the chain resides in a different table so expand your store table in the fields pane and you can check the box in front of chain or you can click and hold and drag chain to the axis box in

the visualizations pane at this point we decide we'd like to see The territories as well in our visualization in the visualizations pane we have a small multiple fields which we did not have with the pie chart visualization and the territory resides in the store table so in that table I'm going to grab territory and drag it into the small multiples box and I'm going to expand the size of my chart notice it now has a scroll bar in It in order to see all of the data you would have to scroll and we decide we

don't want to have to scroll in this chart so we're not going to put territory in small multiples we decide that we're going to add it as a filter and your small multiples box to the right of Territory go ahead and click the X to remove it in power bi you can filter a report visualization By a field that you are not using in the visualization what we're going to do is if your filter's pain is collapsed go ahead and expand it and in the fields pane right click on the territory field in the store

table hover over add to filters and you'll notice there are three different choices there visual level filters will only apply to the visualization that's selected in our Case the clustered column chart page level filters will apply to all visualizations on a single report page and Report level filters apply to all visualizations on all report pages we're going to select visual level filters so we only want it to apply to this particular visualization in your filters pane it already has chain and last year's sales this year's sales and total sales variance they're All set to show

everything is all and those are the fields that we're using on the visualization we're not using territory but it allows us to filter by it so in that filter box for territory I'm going to just check the box in front of Georgia and you'll notice now that the visualization is only showing information for Georgia I'm going to uncheck Georgia and it's back to the way it was where everything Is showing all of the territories when we created our kpi in a previous module we had to sort it before we turned it into a kpi we

decide that we want to sort our clustered column chart here we want to is sort it by this year's sales in ascending order so sorting can be a two-step process in the upper right corner of your visualization you'll see the more options Ellipsis button you're going to Click on that hover over sort by and click on this year's sales then you're going to go back to the more options button and you're going to click on sort ascending so now the visualization is sorted in ascending order by this year's sales we decide that we want to

add data labels to this chart that wasn't an option in the format well For the pie chart so in your visualizations pane we're going to go ahead and click on the paint roller to get into the format well and you can enable data labels by clicking on the off slider so they're on now you can see the value of each column in our visualization that's what a data label does take a few moments and go ahead and format this chart with the background color of your choice and a shadow effect We're going to name this

report page t y for this year comma l y for last year and variance and press enter so it accepts that change go ahead and save your file at this point we decide that we're never really going to have to filter this particular visualization by territory so we're going to remove the territory filter from the filters pane again Territory is not a field that we're using on this visualization so it will allow us to remove it from the filters pane the ones that are on the visualization you cannot remove from the filters pane if you

hover over territory the top its title bar to the right you'll see the X that will allow you to remove that filter we decide instead that we would like to be able to filter this visualization by Chain as well as the visualization on the reg versus markdown report page and the kpi report page we'd like to use a visual filter known as a slicer so what I'm going to do is I'm going to make room for the slicer on this page I'm going to just leave more space on the right side where I'll be able

to pop the slicer visualization in And since we're going to want to use the same slicer on two other report pages kpi and reg versus markdown go to each of those pages and leave about the same amount of space on the right side of those visualizations when you're done making those adjustments come back to your t y l y and variance page make sure your visualization is not selected you can click in a blank space on the canvas to make sure it's Deselected and in your visualizations pane you're going to find and select the slicer

visualization icon it looks like a table with a funnel on it and it popped the slicer over on the right side of the screen if it didn't place it there you can move it over there and now what we're going to do in the store table in the fields pane we're going to just drag chain over to The slicer and we realize the slicer doesn't have to be that tall so I'm going to resize it doesn't need to be take up that much space and I decide that I'm going to do a little bit of

formatting to this slicer with the slicer selected I'm going to the format well in the visualizations pane and I'm going to just enable a Background color for the slicer expand background and choose a complementary color I'm also fond of putting borders around slicers just so they stand out a little bit more on the page so I've collapsed the background format option and I'm going to enable border and expand it and I'm going to leave the Border black but I'm going to make it about 30 pixels In radius so it stands out and now when I

click away from the slicer you'll see the Border around it Let's test the slicer on this page before we sync and make it visible on the other two pages so in the slicer I'm going to just check the box in front of Fashions Direct and it filtered the visualization for that chain I'm going to uncheck that box so I get both chains showing now with your slicer still selected You're going to go up to the view tab of the ribbon and the last button on The View tab is sink slicers you really don't have to

sync slicers if you want a slicer just on one report page and only functional on one report page you can do that but in our case we want to use the same slicer on multiple pages when you click on sink slicers you get a sink slicers pane in between your Filters and visualizations panes on the right side it will show you all of the pages in this particular report file and there are two columns the column with the circular Arrow that represents whether the slicer is going to be synced so if it's on this page

and I sync it with another page that means that when I filter on this page the other page will be filtered as well And vice versa the other column looks like an i and that's whether the slicer will be visible on the other pages so notice the slicer is already visible it's already checked this is the page that we created it on and we're also going to check the sync button after pyly and variant so it's synced and visible on this page we're going to select both check boxes for the reg versus markdown page And

we're going to select both for the kpi page and usually when you sync it a new update has it automatically checking that it's also visible but if that's not happening you'll have to click both check boxes so what does that do at this point we don't need sink slices pane open so to get rid of some of the Clutter I'm going to close that pane by using the X and on t y l y and variance page I'm Going to go ahead and use the slicer to filter for the Lindsay's chain when I go to

the reg versus markdown page you'll notice the slicer is visible and this page is also filtered for Lindsay's on this page uncheck Lindsay's and filter for Fashions Direct and when you go to your kpi page you'll notice the numbers have changed as it's only filtered for Fashions Direct if I go back to t y l y and variance Page that is also filtered for Fashions Direct so I'm going to clear the filter by just unchecking Fashions Direct in the slicer and now if you look at the other two pages they're showing all of the data

not just by a particular change in this next lesson we're going to focus on creating a drill through page with drill through and power bi reports you can create a page in your report that focuses on a specific entity such As a category store or territory when your report readers use drill through they right-click a data point in other report pages and drill through to the focus page to get details that are filtered to that context you'll see this play out in this lesson we're going to put our drill through report on a separate page

so I'm going to go ahead and click the plus sign to get a new page and I'm going to double click Page one and name the page drill through remember to press enter after you finish typing in the name so it accepts the name for this type of report that we're going to build we're going to use the multi-row card visualization and your visualizations pane locate the multi-roll card icon and click it we're going to use three fields from the sales table to populate this card the First field we're going to use is last year's

sales so I'm going to just click and hold and drag it over into the card framework I'm going to grab the value field from this year's sales and drag it over thank you and the last field is we want total sales VAR percent once I drag that into the card I'm going to widen the card so it only displays one row And I don't need it to be that tall so I'm going to make it less tall by using the sizing handles at the bottom of the visualization pane regardless of the type of visualization that

you select you have a drill through section we're going to use that section now to add a field as a drill through field and then you'll see what happens with our visualization we want to use the category field from The item table as I'll drill through field so I'm going to locate it in the fields pane I'm going to click and hold and drag it to the box that says add drill through Fields here at the bottom of the visualizations pane the category field displays like a filter in the drill through area also on your

multi-row card you have a back button that's a navigation button that you can use to go back to whatever Report page you perform the drill through on let's go ahead and save our file we'll Circle back to formatting the multi-roll card but right now we want to proceed so you can see the functionality of using drill through we're going to create a stacked bar chart on a new page go ahead and create a new page and name that page sales by category in your visualizations pane the stack Bar chart is typically the first icon go

ahead and locate it and click on it to put the framework on the canvas from the item table we're going to drag category to the access box in the visualizations pane and we're going to drag from the sales table this year's sales value to the values box In the visualizations pane and we have our stacked bar chart now I'm going to make the bar chart just a little bit wider and we'll come back and format it later and I'm going to click away from it because we want to add two slicers to this page one

for chain and one for buyer so in your visualizations pane locate your slicer icon and click on it and I'm going to just move the slicer Over to the right side of the canvas it doesn't need to be as wide as it is and you can size yours accordingly thank you so I'm going to just pop it over here on the right side and you'll notice the red dashed lines those are like your guides when you're trying to align things so when the line is on the bottom it means it's aligned with the bottom of

the visualization and I'm going to go to the store table And drag chain into the slicer and we decide that we want this slicer instead of having everything displayed like it is now with the two check boxes we want it to be a drop down so to the right of chain you'll see the drop down arrow it says select the type of slicer and we're going to choose drop down so now when you do the drop down it defaults to all and you'll see the two Choices this is particularly useful tip when you have a

lot of entries in the slicer instead of having to have a long long slicer you can just make it into a drop down we'll do more formatting on the slicer as well a little bit later right now I'm going to click on a blank area of the canvas and I'm going to grab the slicer icon again I'm going to size it down and I'm going to move it over To the right of the chain slicer and this time in the item table I'm going to drag buyer into the new slicer make that one a drop

down as well and for right now I'm going to just move the buyer one down a little bit we're going to go ahead and save our file and then we'll explore how to use drill through let's go back to the drill through page And take a look at last year's sales 23 million and change we can navigate back to sales by category if we right click on any of these data points so we have men's shoes these are the different categories now that we have a drill through Page we'll see drill through on the right

click menu so what I'm going to do and it even tells you that when you hover over a category It says right click to drill through I'm going to hover over shoes and right click on it hover over drill through and it shows the target page I'm going to click on drill through and now when I look at this data it's only representing the shoe category if you look at the bottom of the visualization pane you'll see it's as if you filtered it for shoes so the numbers reflect that You can use the back button

the back arrow if you hold down your control key and click it it will take you back to your Source page we're going to filter this report by a buyer so I'm going to go to the drop down next to all in the buyer slicer and I'm going to check the box in front of Barrett Galvin so now this report has updated It's just showing his sales I'm going to right click on the women's category hover over drill through and click on drill through and now we'll see last year's sales are blank and we have

this year's sales so Galvin didn't sell anything last year he's not responsible for as a buyer of buying anything that sold last year he may have Just started with the company this year but I want to draw your attention to the drill through Section now when it was filtered by category which is the field we use as our drill through field it looks like a regular filter and you can see now that it's filtered for women's that's what we right clicked on underneath that we have another filter and this one is in italics that's Because

it's not a drill through field we use the buyer slicer on the source report so it appears different in a drill through section we're going to use the back button to go back to our source report we're going to do the buyer slicer drop down and uncheck Barrett Galvin so we get the full information back in this visualization now we're going to spend a little bit of Time formatting the drill through and sales by category reports let's start with the drill through page now even though we cleared the buyer slicer on the source page sales

by category you'll notice that the data hasn't updated you have to clear it on your drill through report page as well so in the bottom of the visualizations pane we're going to clear the women's category now you can either Uncheck it or you can use the little eraser that says clear filter and then underneath that we have the buyer filter is still there even though we cleared the buyer slicer and we're going to do the X to just remove that filter so now we have our full number set back make sure your multi-row card visualization

is selected and we're going to go over to the paint roller in The visualizations pane to go back to the format well we don't need data labels we're seeing the numbers it's a multi-roll card right we don't want to put a title on the card maybe we do want to give the card a border for some visual interest so I'm going to toggle border to on and expand the category and I'm going to give it a border color like a bluish color and I'm going to make the Border 30 pixel radius and when I click

away from the card I can see the effect it's a little bit more visually appealing I'm going to reselect the card and go back to the format well and I decide I'm going to give the card a background color so I'm going to toggle background to on expand the category and I'm going to choose a lighter color and I'm going to click away from the Card in terms of the arrow that comes when you do a drill through page you can actually swap that out for something else of your choice it's actually a second component

it's kind of sort of on its own outside of the card so if you click on the Arrow you'll notice that it selects just that you'll see the sizing handles around that and you can press delete on your keyboard And we're going to use another back arrow for our back button so what we're going to want to do is go up to the insert tab of the ribbon and the last group is the elements group you can insert a text box on a visualization page a variety of buttons if you look at the buttons you'll

see the back button that's the one that came as a default when we created this drill through page And they have other Arrow buttons in here we can use an image if it's stored on your computer what we're going to use is shapes so under shapes they have block arrows and we're going to select the arrow left block arrow puts it in pretty big so I'm going to make it really tiny and I am going to move it so that it is right above My multi-row card and with the Black Arrow still selected you'll notice

you have another pane you have a format shape pane that shows up where the visualizations pane used to be and in that pane you have an action option we're going to enable the action option and then expand it and you'll see that the default action is that it serves as a back button just like the one that came when we did this visualization and made it a drill Through you have other choices there we're going to leave it on back and now I'm going to just click away from the arrow hold down my control key

and click on it and it should take me back to the sales by category report page go ahead and save your file foreign a few moments in the format well and give your stacked bar chart data labels add a background color and a Shadow effect if you'd like we want to apply the same formatting to both of our slicers so there's a time saving technique that you can use I'm going to click at the very top of the chain slicer hold down my control key and click at the top of the buyer slicer so both

of them are selected in the format well with both slicers already selected I'm going to expand selection controls and make sure select all option is toggle to on I'm going to collapse selection controls and I'm gonna expand the background and choose a background color that's complementary to the rest of the page so see how it impacts both slicers at the same time because I chose a dark background color I'm going to expand items and change the item color the font color for the items to White so they show up a little bit better I'm going

to click on a blank area of the canvas to deselect both of those slicers and I just need to resize my buyer slicer so it's the same size as the chain one for consistency go ahead and save your file just like Excel power bi has a conditional formatting feature let's navigate to our t-y-l-y and variants page and the first thing we're going to do is resize the column chart visualization so It's not as tall as it is we're going to want to put another visualization underneath it so we're just making space for that conditional formatting

in power bi lends itself to the table visualization or the Matrix visualization in a visualization pane we're going to make sure that we don't have anything selected on our canvas and we're going to select the table visualization icon From the sales table in the fields pane we're going to want to add last year's sales to the table so I'm going to just drag it into the table framework and we're also going to want to add this year's sales value into the table so we just have two columns in the table and that's fine for what

we're trying to do so in order to do conditional formatting you have to access the field that you want to conditionally format from the Visualization's pane so if you notice we have last year's sales and this year's sales in the values box we want to conditionally format this year's sales so I'm going to right click on this year's sales hover over conditional formatting and I'm going to click on background color so the background color for the specific field that we right click on this year's sale dialog box comes up on the screen Take a moment

and explore the format by drop down we're going to leave it on color scale but you do have other options there and also take a look at the apply to drop down that's defaulting to values only and we're going to leave it like that it tells you it's based on this field and that's also a drop down so you could search for other fields from in here if you happen to right click on the wrong Field we have a minimum and maximum area and before we start filling in any numbers here we're going to check

the diverging check box so now we have minimum Center and maximum areas to fill out the scenario we're using here is we want to say that we have a tolerance for what this year's sales should be we're comfortable with a minimum of 20 million We would like to have a maximum of 25 million and we're kind of okay in the center if the center is 23 million five hundred thousand dollars so we're going to start putting those numbers in boxes under minimum where it says enter a value you're going to put in your 20 million

for the center enter a value box it's going to be 23.5 million And the maximum is going to be 25 million before we click OK let's take a look at this year's sales value 22 million 51 952. so it's not at the minimum certainly not at the maximum it's a little bit below Center so when you're diverging there is a color scale there if we were directly at Center it would Be this yellowish color but we're a little bit less than Center so go ahead and click ok and look at the color that it used

to highlight this year's sales now keep in mind if our data changes is we add more sales for this year to our data set that color on this year's sales will change accordingly as well that's an example of a conditional format another useful efficiency technique is using the bookmarks feature in your Power bi reports let's go to the sales by category page and the first thing we have to do is let's go to where the drop down would be in the buyer slicer and remember we changed the item color the font color to white so

we're not able to see the items I neglected to have you change the background of the items to a darker color so I'm going to click on the Chain slicer to select it hold down my control key and click on the buyer slicer and go To the paint roller to get into the format well I'm going to expand items and do the drop down next to background color and I'm going to choose a dark color if I click on a blank area of the canvas and go to the buyer all drop down I'll be able

to see all of the buyers names clearly now because it's a contrasting background color we're going to select Evangeline bright So now our chart is filtered for just Evangeline bright let's say that this report page you come to often and you filter for different buyers you can save your filtered report as a bookmark to do that you're going to go up to the view tab on the ribbon in the show panes group the last group you're going to click on bookmarks And it opens the panel to the left of your visualization pane with the report

filtered for Evangeline bright the way it is we're going to click add at the top of the bookmarks pane and it adds a bookmark it calls it bookmark one to the right of it we're going to go to the more options ellipsis and we're going to choose rename and we'll just call it Evangeline bright The buyer's name and press enter now go ahead and close your bookmarks pane by using the X in the upper right hand corner okay access the buyer slicer drop down and choose select all at the top the next sign that you

want to see it filtered for Evangeline bright you don't actually have to do the filter you just need to access the bookmark we're going to go back to bookmarks on The View tab And in the bookmarks pane you're going to click on Evangeline bright and it does the same filter for you so imagine if you set up a bookmark for those buyers that you want to track frequently you can have multiple bookmarks set up and access them anytime you need to go ahead and save your file and you can close the bookmarks pane and do

your buyer slicer drop down again and go back to select all our next lesson is about accessibility Features in power bi reports there are two main categories of features built-in accessibility features that require no configuration and built-in accessibility features requiring configuration the features that don't require configuration are keyboard navigation a lot of users want to navigate using their keyboard through the reports the screen reader compatibility if you have high contrast colors set in Windows those high contrast colors will Come over into Power bi reports and be applied to your reports you have Focus mode so

you can fill up the whole canvas and just focus on the visualization that you're looking at and another one that requires no configuration is the ability to show a data table of the underlying data for that particular visualization you'll get to see some of these as we go through this lesson we also have features that do require Configuration in this lesson we'll go through alt text which is text that a screen reader will read out to the user making sure that the tab order is appropriate especially for those users that want to navigate a report

by using the keyboard there are titles and labels that can be configured as well as markers and you'll get to see some of the report themes The first thing we're going to do is configure alt text for our bar chart on the sales by category page so I've just selected the chart and I'm going to go to the format well in the visualizations pane and expand General if you scroll down you'll see alt text at the bottom of the general category so if you don't put in alt text any screen reader will give a generic

Description of whatever object the end user selects so it'll just give it a generic description you want to give a more detailed description so in that alt text box you're going to say you're going to actually type this is a bar graph representing this year's sales broken down by category so when the screen reader responds to this selected visualization That is the text it will read another accessibility feature that needs to be configured via a shortcut key combination is the ability to add a data table to a visualization a data table shows the underlying data

in a table format that's causing the visualization to be drawn so with that same bar chart selected the shortcut key combination for adding a data table is alt shift f11 and when you do it you'll notice on the Bottom half of the screen it has a table a data table showing a listing of the categories and each category sales for this year that can be useful when someone needs to review a report you'll notice at the top it has a back to report button and when you click that button it just gets rid of the

data table and takes you back to the regular report page another useful feature available for Every report visualization is the ability to go into Focus mode in the upper right hand corner of the bar report you'll see the middle button is focus mode I'm going to click on it and this is similar to the type of screen you get when you put in a data table it just doesn't have the data table it makes sure that the visualization fills up the screen so you can direct your focus to that We're going to click the back

to report button again to get back to the report an equally important feature to consider is the tab order for those users that are going to be using the keyboard to navigate through the report you want to make sure that the tab order is correct go ahead and press the tab key on your keyboard and it should select the bar graph when you press tab again it should go to the chain slicer and when you press it again it should go to the buyer Slicer let's say for some reason you don't want it to land

on the Chain slicer maybe you don't want end users to be able to filter by Chain on this report or if the tab order is out of order you need to fix it both are found in the same place on The View tab of the ribbon in the show panes group you're going to click on selection and it opens the selection pane At the top you can look at the layer order or tab order click on Tab order so we're seeing the tab order here it's saying the first thing it's going to go to is

the slicer that we're currently on and then the second thing is the second slicer and we want to change the order here so to ensure that it goes to the bar graph first I'm clicking on the number three and I'm going to use the up Arrow to Move it to the first position I'm going to click on the number two and say that should be moved to the last position now we'll leave that one second and for the third one we actually really don't want this one to be in the tab order we'll leave it

there for right now an equally important feature to consider is the tab order on a report page I'm still on the sales by category page and I'm going to press the tab key on my Keyboard and you'll notice that it is selecting the bar chart this year sale by category bar chart when I press tab again it goes over to the buyer slicer and again it goes to the chain slicer so the tab order is not correct it should flow and for people that are accessing your report using their keyboard to navigate it tab order

becomes particularly important so I'm going to show you where we would go to make sure the tab order Is correct it's going to be on The View tab of the ribbon in that last group again you're going to click selection and it opens the selection pane at the top of the pane it defaults to layer order and to the right you're going to click on Tab order so we want to make sure this year's sales by category is in position number one which it is I'm going to click on number two slicer and that's the

buyer slicer and I want that to be in the last Position so I'm going to go above the names of the objects and I'm going to click the down arrow button to move that one down now when I test test my tab order I'm going to just click anywhere on the canvas of my report press my Tab Key and it selects the bar chart press it again it selects chain and again it selects buyer I'm going to go ahead and close the selection pane And save my file the last accessibility feature we're going to cover

in this module is the use of a theme one in particular so on The View tab you have a gallery of themes and we're going to access it by using the drop down arrow to the right under power bi there is a theme and when you hover over them you'll see the screen tip showing you the names and my screen it's in the second row and It's second column under power bi there is a theme called colorblind safe I'm going to go ahead and click on that theme and apply it when you choose a theme

it supersedes any formatting that you've done on the report pages so if you go to your drill through Page you'll see that that has the same colorblind friendly theme other report Pages has it as well there are also themes available to you in the online service and you'll see That in the next module our next lesson is how to create a histogram visualization the histogram visualization does not default to being in the visualization's pane it's a visualization that you're going to have to add so in order to do that let's go over to our visualizations

Pane and after the last visualization you'll see the vertical Ellipsis icon and it says get more visuals We're going to click on that and then click on get more visuals when you do this it brings you into Power bi visuals and it defaults to the app Source tab at the top of the dialog box you also have a my organization tab so your power bi admin can add visuals for the entire organization and when that happens you can access them from the my organization tab we're going to go back to the app Source tab You'll

notice they have categories of visualizations and we can click in the search box and type histogram and when you press enter or click the search button the magnifying glass it will show you the histogram chart there's a couple of different kinds in here from the appsource store we want the default histogram chart so we're going to click the add button on The right and depending on your licensing you may not have the ability to get more visuals it will let you know that it imported this custom visual successfully and I'm going to click OK on

that dialog so the histogram icon shows up underneath all of the other visualizations if we were to open a new power bi desktop file the histogram visualization icon will not be there if you wanted to Retain over different files you need to pin it so I'm going to right click on that histogram icon and choose pen to visualizations pane and it will ultimately move it up so it's amongst all the other visualizations in that pane now that we've pinned the histogram visualization icon to our visualizations pane we're going to start a new power bi desktop

file and bring in information from an Excel file I'm going to go to the file tab on the ribbon and choose new so it will launch a new instance of power bi desktop and because you pinned the histogram visualization it shows up in the new file as well on your splash screen we're going to click on get data and when the dialog box opens on the right side we're going to double click Excel workbook And we're going to use the histogram Excel file that you grabbed from the video description earlier I'm going to just double

click it and let it connect and this file only has one sheet in it called employee salaries I'm going to click the check mark and you'll see it just has generic employee data and their monthly salaries in dollars we're going to click the load button at The bottom this data is ideal for a histogram and you'll see why in just a moment okay let's take a look at the data in data View so it just shows the generic employee identifiers and their monthly salary and dollars the reason why this is good data for a histogram

because a histogram is a representation of data points into ranges data points Are grouped into ranges or bins is what they're called in a histogram making the data more understandable so what we're going to do is go back to report View and we have this blank page here let's go ahead and rename page one so it's called histogram and the first thing we're going to do is create a table visualization using the employee salaries data so in your visualizations pane find your table icon And click on it expand your employee salaries table in the field

pane and we want to do the employee drag the employee field into the table and then drag monthly salary in Dollars into the table so just in a table format we're able to see this information and what's going to happen is when we put this information into a histogram It's going to do groupings of employees known as bins based on their monthly salary so for example it may create a bin for the count of employees that are in the salary range of say 2 000 to 2500 another bin would be 2501 to 3 000. so

on and so forth and you'll see this play out right now go ahead and press delete to delete the table visualization and in your visualizations pane you're going to click on your newly added Histogram icon and you can go ahead and expand the width and height if you'd like of the histogram framework and if we look in the visualizations pane you'll notice that we have a values field and a frequency field for the histogram we're going to use the monthly salary field from employee salaries table as the values field and we're going to use the

employee as The frequency field and when you drag employee into the frequency field it says first employee we want a count of employees so we're going to do the drop down arrow next to first employee and we're going to click on count so what you're looking at I'm going to just make my histogram bigger so the frequency is over on the left and we're going to format that so it doesn't have any decimal points in a few moments But what's happening is it created these are your bins so it did groupings of the salaries so

you have 2.02 000 through that's the beginning of the first bin the second bin begins with two point five one thousand the third one is three point zero zero thousand so on and so forth so when you hover over a bin which is a column in the histogram you can see the frequency so that's saying there are 25 employees that fit into that first bin and it gives you the range of the bin that is what a histogram does now we want to format that axis on the left which is known as the x-axis the

one on the bottom is known as the y-axis to get rid of the decimal places so I'm going to just go to the format well and I'm going to expand x-axis and where it says decimal place is 2 I'm going to change the 2 to a zero And press enter and I did that on the wrong axis so I'm going to change that back to two decimal places I do this all the time and I'm going to collapse the x-axis and expand the y-axis I always get them backwards and change the 2 for decimal places

to zero and now you can see the change for the frequency doesn't need decimal places over there it's a count Go ahead and we're going to close this file if you want to keep it you can save it and just call it histogram so I'm going to go ahead and do save and it'll prompt me and to give it a name and I'm going to call it histogram and then I am going to close it and you still have your retail analysis sample file open I mentioned earlier that another way to get great report ideas

is by viewing Reports that have been created by other people we have lots of them in this particular file let's go to the overview page and if you see a report that someone else designed that you liked you can select the report so I'm going to select let's see we'll select the total sales variance percent report in the lower right hand corner and when I look in the visualizations pane I'm going to go ahead and collapse The fields pane for now when I look in the visualizations pane I can see which visual was selected in

this case a scatter chart so if I wanted to recreate this I know that I can by using a scatter chart visualization I also have all of the fields that were used and where they were used so we have district and store number in the details section we have District in the legend so on and so forth So I can use that information to recreate this type of chart and I can even use different fields when I recreate it the easiest way of doing this is copying the visualization when you copy a visualization you can

only copy it within power bi desktop so from one report page to another you can't copy it outside of the application what we're going to do is we're going to right click in a blank area of that visualization in the lower left And we're going to hover over copy and choose copy visual let's do the plus sign to create a new page and click anywhere on the canvas and you're going to do control V as in Victor to paste so now we have this visual on its own page and we can go over to the

visualizations pane and swap out Fields if we want to we're going to delete the page That we just pasted the visual on so I'm right clicking on page one I'm choosing delete page it will always confirm if you want a deletion go ahead and confirm the deletion save your file and this extensive module you learned how to design enhance configure and format report visualizations how to create and configure sync slicers how to create a drill through Page apply Conditional formatting on a field in a visualization create and use bookmarks for efficiency how to create a

histogram and we reviewed some accessibility options in the reporting module now that we've created and assembled our report Pages we're ready to move on to module 8 which is creating dashboards once you put your data together on a dashboard it gives you the opportunity to give a compelling story about your Data you'll also learn about the features and functionality of dashboards and how to enhance them we'll be using the sample Superstore OD data set that we published to the power bi service in module 2 as well as the Microsoft forms and power automate apps in

this module you can access those apps by logging into your Microsoft account going to the waffle in the upper left hand corner and You should see all the apps that you have access to we'll start this module by creating a dashboard by pinning visualizations to it in the power bi service we'll move on to real-time dashboards where the visualizations update in real time because we'll be accessing a real-time data set and we'll also get to use a form and the power automate feature during that lesson we'll go into enhancing a dashboard Which could mean adding

a video to it text box a theme we're going to configure a dashboard tile alert so if changes happen to that dashboard tile you can be alerted and we'll use a feature named q a for analysis to finish off this module dashboards are not created in the desktop they're created in the service as mentioned they can contain report visualizations videos text boxes audio files and web content including other Dashboards or reports you can share dashboards have conversations about them both in the service or in teams you can even subscribe yourself and others to email alerts

regarding dashboards you'll learn how to do all of those things during this module we'll start by navigating to the power bi workspace where we publish the sample Superstore OD report and data set we're going to get started by accessing The sample Superstore OD report that we publish to the service in module two first thing I want to point out here is the difference in icons when we publish from the desktop to the service it published both the report which has a blue icon and it looks like a column chart on it and it also published

the data set which has an orange icon and it looks like a database icon on it we want to access the report so sample Superstore OD is a link and we're going To click it and it will navigate us to the report so this was a simple report that we put together earlier and we're going to edit it we want to add three visualizations to it and we can really get rid of the visualization that's on it so once you have published to the service if there are any changes to your reports or even if

you want to create new ones you can do it in the service and the way that you do that is By coming up here to the menu and clicking the more options button choose edit and this view should look very familiar to you it's almost exactly the same as report View and power bi desktop so you have your fields and visualizations and filters pane on the right let's start by clicking on a blank area of the canvas and we're going to want to add another visualization here And so we're going to use the table visualization

find it in your visualizations pane and we're going to expand the orders table in the fields pane and we want to add customer segment field to our table the other field that we want to add to the table is sales so in our table we're seeing customer segment By sales and we can go ahead and resize that table doesn't need to be as big as it is to show that little amount of data yeah before we add our other two visualizations let's go ahead and select the bar chart that was already on this page and

press delete on your keyboard to get rid of it I'm going to then move the table up to where the bar chart was so it's in like the upper left hand corner of the page And I'm going to click on the blank area of the canvas again before I add my next visualization let's grab the Stacked bar chart visualization and we're going to use the same two fields in it that we used in the table so go ahead and check customer segment in the fields Pane and check sales we're going to click on a blank

area of the canvas and the last visualization we're going to add is the card Visualization and we want to just have that show the total sales I'm going to move the card visualization so it's to the right of the table and you can see the same red guidelines that are there as when you're in power bi desktop these three visualizations are defaulting to interact with each other by filtering so in the table if I click on consumer you'll notice that the card Updates to just show the sales for the consumer segment and that the bar

chart also updates I'm going to just click on a blank area of the canvas and now we want to save this report and you can do that from the file drop down and just choose save we won't worry about any formatting on this report right now the greater picture is to get it onto a dashboard now we're ready to create a dashboard by pinning this report to it On the task pane going across the top of your screen you'll notice there's an icon that says pin to a dashboard we're going to go ahead and click

that icon and it opens the PIN to the dashboard box if you have existing dashboards in here it will list them in alphabetical order we're going to create a new dashboard and we're going to name it sample Superstore and then we're going to click the pen Live button and it tells you pin live page enables changes to reports to appear in the dashboard tile when the page is refreshed so we're going to go ahead and click on Pin live and it tells us it pinned it to a dashboard and if you catch that pop-up quick

enough you can go to the dashboard from that pop-up if you don't catch it quick enough you can get to the dashboard by Going back to your workspace there's a couple of ways you can get back from your report to the workspace you can come right up here at the top in the title bar and click on your workspace or on the left side the next to the last button will take you to your workspace either way is fine when you get back to your workspace you'll notice that now for sample superstore we have we

still have for OD to report in the data set but now we Also have a sample Superstore dashboard and just take note of the different icon so this one is like a greenish icon it looks like it has a gauge inside of it to get to the dashboard we're going to just click on the link sample Superstore and now you're seeing your dashboard after we create our second dashboard you'll learn how to enhance dashboards in the meantime let's go back to our workspace where the dashboard resides And I'd like you to go back to the

sample Superstore OD report because we were in edit mode when we pinned it to the dashboard we have that huge set of options going across the top what if you want to not edit a report and just add it to a dashboard I figured I'd show you how that process would work so that's when you would just use more options up here and you would have the PIN to a dashboard Choice we've already pinned this report to a dashboard so we Don't have to go through that step just wanted to show you how to find

it when you're not in edit mode in report View in this next lesson we're going to create a dashboard based on streaming real-time data your dashboards will update in real time when you use this method and the data can be from various sources we create these real-time data sets in the service as you'll see play out in this lesson When you're using real-time data there are three primary types of real-time data sets that you can use in the service there are push data sets streaming data sets and Pub nub data sets let's take a look

at the differences between the three of them in terms of the capability of what they can do we're going to end up using a push data set for our next example and also just a reminder this PowerPoint Presentation is in the video description so you'll be able to refer to it in the future so the push data set all of them update in real time as the data is pushed in you'll notice that the push method allows data to be stored permanently in power bi for historic analysis when it's streaming the data is only temporarily

stored for an hour to render visuals and pubnub doesn't store any data at all Because the data is stored permanently for the push method you can use your power bi reports atop the data so we'll be using the real-time push data set because it creates a database which maintains history we can use the power bi visualizations on that data is still be real time in your dashboard so no need to refresh there's a difference here you cannot Pin live Pages like we did in our previous Example we would have to pin each individual visual to

the dashboard in order for it to be effective and refresh in real time it's counter-intuitive pinning lies and real-time refresh so we won't be able to use the pen live feature that we just saw but you'll see how this plays out we're going to start this lesson by creating a form in Microsoft forms that will be used to push the data into the data set and create the database for us So I've signed into my Microsoft account and I'm in Microsoft forms if you need to get yourself into Microsoft forms you can use the Microsoft

waffle in the upper left hand corner of your screen and that will give you access to all of your apps when you click on the waffle you'll be able to see forms in the list if you don't see it in the list you want to click on all apps and then you should be able to find it in your list of apps go ahead and launch Microsoft forms and Your screen will look similar to mine we're going to create a short three question form that will be used as the vehicle to push data into the

data set we're going to create in the upper left corner go ahead and click directly on new form it may ask you to pick an account sometimes it even asks you to sign in where it says Untitled form we're going to click and we're going to just call this streaming survey We don't need to enter a description it is optional we're going to go ahead and click the add new button to add our first question and the type of response is going to be a choice so we're going to click on choice the question is

which VI technology are you most interested in learning about so I'm going to just type that in next to the number one which bi technology are you most interested in learning about And I'll give it a question mark at the end we're going to click where it says option one and the first option is power apps option two is power automate and we're going to add another option using the add option button and that one is going to be power bi to the right of the add option button we're going to add the other option

so it gives an opportunity to select other And put in a choice that's not on our list we only want them to choose one answer so we're not going to toggle multiple answers on and we're not going to make this question required instead we're going to add our second question so click your add new button and the second question will also be a choice so click on choice this question is what is your current experience level on the technology you Selected what is your current experience level on the technology you selected we're going to click

on option one but if you notice right above option one it gives us some suggested options and in that list of suggested options we're going to click on beginner for option two we're going to select the preset intermediate and we're going to add an option again So we have three options and this one we want to be expert and there's a preset for that there as well again we only want one answer it's not a required question we're going to go ahead and add new to get our third question and I got this pop-up every

time something is updating they let you know I'm going to just get rid of it by doing got it has nothing to do with what we're doing right now so I'm going to go back To add new and this one is going to be a text type response the question here is where are you from and we want to be helpful and give them information about the format we want the answer to be in so after the question in parentheses I'm going to type provide answer in City comma s t for State format and I'm

going to give an example For example I'll put in San Francisco CA and close my parentheses and so those are the three questions that we want on this form the responses to these questions will be pushed into our database in the cloud if you'll notice since this is online the form has already been saved I can click back on forms in the upper left corner and I'll see my streaming survey form We're going to navigate back to our workspace so that we can start creating our streaming data set when we switch back over to the

service we're still seeing the sample Superstore OD report in my case it's in my power bi video workspace and I decide I want to put our streaming data in a new workspace on the left side of your screen the workspaces icon looks like a stack of papers And that's how you can start the process of creating a new workspace I'm going to go ahead and click that icon and at the bottom I'm going to choose create a workspace so the panel opens up on the right to create the workspace we're going to name the workspace

streaming data set so you can just type streaming data set in there and for the description contains all components Of our streaming data if you wanted to learn more about workspace settings you could click this link it would open a new browser tab we're going to go ahead and click save at the bottom and you'll notice you are in your new streaming data set now we just have to create the data set so we have the workspace that's empty at this point we are going to create the data set by using the new button on

the left side of The screen and on that list of things you're going to select streaming data set and the panel on the right opens up we talked about the three methods of getting streaming data real time streaming data into Power bi API is the method we're going to use that's for the push data set streaming would be Azure stream and then we have pubnub we're going to leave it On API and click on next we're going to name the data set streaming data set just to be consistent they'll have different icons so you'll be

able to tell and it tells you whether it's a report versus a data set so on and so forth for the values from the Stream we're going to leave all of them defaulting to text but go ahead and do the drop down next to texting you can see it could be text number or date time Our first value we're using three values from the survey form that we created so our first value is going to be technology that was referenced in the first question so we're just going to call this value technology and again we're

going to leave it on text it's putting it in it's showing you some of the behind the way coding it's a range so we're naming it technology and it's going to pull whatever value is put In the response for that question from the form ultimately that data is going to be pushed into this data set our next value that we're going to use here is going to be experience and the third value is going to be location and you can just see that it gives you another space for a new value we can ignore that

it expanded the range technology experience location and Underneath that you'll see it's historic data analysis by default it is off we want to toggle that on so it creates the database retains all of the data from all of the responses from that streaming survey form that we created go ahead and click the create button it lets you know that you're streaming data set has been created it gives you the schema for it right you're looking at the raw data here and it gives you a push URL we're going To just go ahead and click done

in the lower right hand corner even though we don't have any data in our streaming data set yet we're going to go ahead and create our reports so that when we do fill out our form and push data into the data set the report visualizations will automatically populate to do that I'm going to use the more options button to the right of the data set and I'm going to click on create Report and it opens up the reporting interface that you're used to we saw this when we edited in the previous lesson and we saw

this when we were working in power bi desktop notice in your Fields pane it just says real-time data and we're going to expand that table so we're going to create a few visualizations here and they'll populate when we push data In to the data set so the first visualization we're going to grab the table visualization icon and we're going to just add technology to the table as a field and then we'll add let's see we'll add technology We'll add after technology let's go ahead and do experience and location in the table and then we're going

to click on a blank area of the canvas and we're going to use a map Visualization and your visualizations pane the map visualization looks like a globe go ahead and select that and we're going to use location field from the fields pane as the location we're going to use experience as the legend for the map visualization and we're going to use technology actually let's flip that let's use the X to get rid of experience in the legend and let's put technology in the legend And then for experience we're going to put a count of experiences

and that goes in the size box for the visualization so I'm going to make that map visualization bigger again it's not showing any of our data because we don't have any data in our data set and maybe what I'll do is I'll move the map to the right side of the table and then size it from there I'm going to click on a blank area of the canvas again and this time we're going to add a stacked column chart so I guess that's the second icon in the visualizations pane and we're going to use for

the axis we'll grab technology and we'll use experience for the legend and again we're not seeing anything in these visualizations because we don't have any data yet we're going to add one more visualization and We're going to use the card visualization for that so make sure you're on a blank area of your canvas click on the card and for the field we're going to use technology and notice how when you put it into the fields box it says First Technology we're going to do the drop down arrow next to First Technology and choose count distinct

so how many responses are there for that Technology now we need to save this report so you're going to go up to file and you're going to choose save and it's going to want you to name it we're going to just call it streaming and click save so I mentioned earlier when you're using streaming data you can't use the pin live experience for dashboards because it's counter-intuitive it's real time streaming data which means your Dashboards will automatically update as new data is pushed into the data set so what we need to do to get these

onto a dashboard is pen them each individually if I hover over my empty technology experience location table you'll see the controls for that and the first control is pin visual go ahead and click it notice it shows a little snapshot of the data in our case we don't have any data yet It defaults to a new dashboard because we are in a new workspace that doesn't have any dashboards and we're going to call this dashboard streaming dashboard notice the pen button does not say pen live it knows that it's streaming so it's not going to

offer you that option we're going to go ahead and click pen and we don't have to go to the dashboard yet you can just close that pop-up if you'd like or to disappear in a moment and then I'm going to hover over the map And find its control buttons which are showing up underneath I'm going to grab the pen visual and now it's on existing dashboard and it's the one that we have in this workspace so we're going to just click pin I'm going to hover over the technology and experience chart and use the PIN

and I'm going to do the same for the card visualization and use the pen So now our setup is done in terms of we have our dashboard we've already pinned our visualizations to it we created our data set but it is not populated before we begin the process of populating our data set let's switch over to our workspace so the streaming data set workspace and let's open up the dashboard that we created we want to make sure everything is organized on the dashboard accordingly so you'll notice when we Were looking at the report the card

for the count of Technology was over in this area we want to move it so that we don't have to scroll to get to it on the dashboard so to move a visualization on your dashboard you want to look for its more options button and you can click and hold that and drag it to a new location notice that as you drag it it might cause other dashboard tiles to move that's because they're taking up the space so you'll notice there's a Gray box that pops up and says hey that's an empty space it might

be great for this tile so once I've done that now what we need to do is start the process to populate our data set we're going to use power automate to create a flow flow is kind of short for workflow the flow that we will create will tell the system to grab all the responses from our survey form that we created and push them into the data set So we're going to go access power automate you can use if you want to stay in power bi that's fine but you can go to the Microsoft waffle

and you'll see all of the apps if you want to open power automate in a new window you can use the vertical Ellipsis and say open a new tab so go ahead and get yourself into Power automate it should be the same login you're using for power bi when you access power automate you have The option to start a flow from a template or you can start from a blank we're actually going to start from a blank so on the left side you have the menu and on that menu you're going to go ahead and

click on create foreign so this is where it gives you choices you can start from blank you can start from a template and you can start by using a connector which is typically another app we're going to be using Connectors when we start from a blank one so the type of blank flow that we want is an automated Cloud flow we have power bi in the service which is online we're using a form that we created in Microsoft's forms which is also online so we're going to do an automated Cloud flow and the first thing

we have to do is give it a name it says add a name or we'll generate one we're going to name this streaming data flow Now flows respond to triggers something happens that causes the flow to respond the trigger we want is a Microsoft forms trigger and if it's not showing on your list when a new response is submitted that's the actual trigger we're going to use you can click in the search box and type forms and it will show all the different types of form triggers so we're going to go with when a new

response is submitted and we're going to Click on create so now for this particular trigger you have to tell it which form do the drop down next to pick a form and your streaming survey should show on that list so that's the first step to trigger this flow that we're creating will respond when a new response is submitted to our streaming survey we need to add another step so go ahead And click the new Step button and this is another Microsoft form step so in the search box go ahead and type forms just to narrow

the list I really can tie promise and you'll see that you get some actions so when a new response is submitted is a trigger now what do you want to happen when that trigger is activated we would like to get the response details from that form so again it needs the form ID And you can do your drop down and select streaming survey and click in the response ID field you'll get this pop-up on the right that says add Dynamic content from the apps and connectors used in this flow there's only one choice there and

it's response ID go ahead and click on it so what it's saying is when a new response is submitted on the streaming survey form get the response details by response ID Each respondent is assigned a response ID so each person that takes the survey is answering three questions there are three responses will have the same ID we have to add one more step so go ahead and click your new Step button again this time we're using the power bi connector so in the search box type power bi and you'll see a list of all the

actions for power bi and the action that we want is ADD rows To a data set for that one we have to tell it which workspace so do your workspace drop down and you're going to choose your streaming data set workspace we named the data set itself the same thing so access that drop down and make the choice and then do the drop down for the table notice workspace data set and table all have a red asterisk in front which means they are required The name of that table we saw it when we went and

looked at the reports is Real Time data so once we select the table we get three Fields technology experience and location and we have to tell it which question maps to each field so when you click in technology on the right side you'll have that Dynamic content box and it'll show you the questions it'll also show you some Other things but it'll show you the questions that we created in our form so the first one is which bi technology are you most interested in learning and that's the one we're going to click on for experience

click in the Box and what is your current experience level on the technology is the question and for location you're going to grab where are you from and now we've completed our flow a couple of things If we had problems with anything we did in our flow the flow Checker would be red go ahead and click on the flow Checker you see we have zero errors and zero warnings which is good we don't have any problems we need to save our flow so you can either save it up on the top or you can save

it on the bottom and it lets you know when it's saved so now you have that green band that says your flow is ready to go we recommend you test it What I'd like you to do now is use the Microsoft waffle and open what I'd like you to do is reopen forms in a new window or on a new tab go ahead and do that so I've arranged my screen so I have Microsoft forms on one side and my dashboard my streaming dashboard on the other side what I'm going to do is I'm going

to be a respondent for my own survey and I'd Like you to do the same thing what you're going to do is click on your survey if it's not there you can go to all my forms to find it so I'm going to click on the streaming survey and in order to respond to it I'm going to click the preview button up here on the bar so now what I'd like you to do I would like you to submit five separate responses to your own survey after you finish the first one it'll give you the

Opportunity to take the survey again make random answers when you're going through this process and go ahead and get started on doing that now I'm going to review the streaming dashboard you'll notice that it populated with my choices from my survey responses so the table populated the map populated and you can see the locations on the map The count of Technology card populated but the technology and experience report did not so let's take a look at the report itself to see what's going on with that I'm going to just return to my streaming data set

workspace and I'm going to select a streaming report once it loads I'm going to go up to more options and edit it so it looks like report view in the desktop I'm going to select that empty Visualization I can see that it is a stacked column chart and the reason it's not populated is because we only filled in the axis and the legend we need a value for that chart to populate so in your Fields pane expand real-time data and drag location to the values field because it's a non-numeric field it does a count of

locations so now you'll see that the chart has populated The thing is at this point we have to pin the updated visual to the dashboard so go ahead and select the pen visual push pin above it and we're going to pin it to our streaming dashboard go ahead and click pen and then I'm going to navigate to the dashboard from the pop-up it's going to ask me if I want to save changes to the report and I'm going to choose save So now on the dashboard I have both the empty one and the populated one

I'm going to remove the empty one by going to its more options icon and choosing delete tile I'm going to scroll to the right so I can see the more options for the populated chart and I'm going to click and hold on that and just drag it over to where the other one was on the left of the count of Technology card So now you've created your push data set let me go over the process we went through for this we started by creating our form and then we came over here and we created our

push data set we created a workspace a premium workspace to hold it and we also designed our reports they were empty until the data got pushed in when we responded to our survey after we designed our reports we created our automated flow so that it's Triggered when a response is submitted on the form and it grabs the data and dumps it into our data set one of the things we can do to enhance our dashboard is to apply a theme to it this is very similar to applying a theme to a report in the service

and we can access the themes by going to the edit icon up at the top of the screen and we're going to select dashboard theme The panel opens on the right now there are not as many themes in the service as they are in the desktop so when you do the drop down next to light which is the default theme you have dark colorblind friendly and Custom I'm going to select doc and you know that could be hard to read the part of the dashboard that I see behind it could be good for some people

I'm going to go back to the drop down and select colorblind friendly which Really looks a lot like white in here but apparently just like in the service they have a colorblind friendly theme and then I'm going to go back one more time and choose custom so you can use a background image for your dashboard you can change the color of the background so I'm going to do that by accessing the color drop down and I'm going to choose you can make the color choice that you like but I'm going to choose like this light

teal color You can have a background color for the dashboard tiles so the things that are on the dashboard if you like why don't you take a few moments and go through those settings and figure out the way you want it to look foreign making your choices click the save button in the lower right hand corner another enhancement feature is to add a video or audio or even text boxes to your dashboard we also use the edit Button to access those features and you're going to choose add a tile in the add a tile panel

you'll see that you can add web content if you click on web content and choose next you can see that you can have a title and subtitle and you would have to embed the code here I'm going to use the back button you can add an image a text box or a video here I'm going to go ahead and click on video and then I'm going to click next So it adds the add video tile box here and you have to have the video URL and right now it will only accept videos that are on

YouTube or Vimeo open up a browser window or a new tab and navigate to YouTube at the top of the screen click in the search box and type how to use web apps and at the end type learn it all one word learn it press enter so if you scroll down just a little bit you'll see Office 365 how to use web Apps it's by learnit training and you're going to click on that so that the video page comes up now I just stopped the video from playing because what I want to do is go

all the way to the top of the screen and see the address bar where the URL is so I'm going to select I'm going to click in the address bar at the top of the screen and I'm going to do Ctrl C to copy that URL And I've switched back over to my supplier quality analysis sample dashboard so we can fill in the details in the add video tile pane on the right we're going to check the box that says display title and subtitle the title is going to be how to use web apps we

don't need well we'll put learn it in the subtitle just so you can see how that looks And in the content section for the video URL you're going to do control V as in Victor to paste and now you're going to click the apply button you saw the pop-up that said the content was added to your dashboard so everything you add to your dashboard at this point is going to go to the bottom so I'm scrolling down and you have the video there on your dashboard now this particular video it's a very short video Um

that's why I chose it it's also from learned another reason why I chose it but it has nothing to do with the supplier quality analysis data but just to give you the information on how to add videos to your dashboard to enhance them is what the important takeaway is here another feature you can use on your dashboard is setting a dashboard tile alert a tile alert will notify you when data On a dashboard changes above or below limits that you set right now tile alerts can only be set on card kpi engage visualizations and power

bi I'm going to scroll back to the top of my dashboard and I have a total defect quantity card if I navigate to its more options button and click it you'll see that I have manage alerts there because it's a card visualization it allows me to feature I'm going to go ahead and click manage alerts and the manage alerts box pops up on the right side it lets you know for which card it's for because you know sometimes when you're working fast you might select the wrong thing so it gives you a reminder at the

top we're going to click on ADD alert rule it defaults to being active you can have alerts set up and inactivate them that if you don't want to use them for a While and then just reactivate them when you want to use them again we're going to leave the default title it again repeats and then here it has the name of the field the actual name of the field and the condition defaults to above you have a drop down where you can choose below and the threshold we're going to put in 35. on the card

right now we're at 33 million so this would be at 35. You get to select your notification frequency it defaults to every 24 hours if you want to get alerts every hour you can change that way they will only send if your data changes so you'll get alerts in your notification center in the service and you can opt to have an email sent to you as well I'm going to uncheck to send me an email to box it's recommending that you can also use power automate where we set up our flow To trigger additional actions

we're going to do save and close at the bottom so you can only do alerts on cards gauges and kpi visuals at this point if you opt not to get an email your notifications for your alerts can be found to the left of your login information up here at the very top right hand corner depending on your resolution it might already be expanded but I'm going to Click on that settings button and then I can click on notifications so if you have notifications you can get them from the notification center so when that total defect

quantity gets to 40 or above you'll get the notification there again you can opt to get it via email as well there's an artificial intelligence visualization that you can use both in the desktop and the service and I'm going to introduce it to you now it is Called q a for question and answer you've noticed since we've been in the dashboard in the upper left hand corner it says ask a question about your data that's where q a resides in the service you could also apply it as a visual on a report it functions a

little bit differently in the desktop than it does in a service but the end result will be the same it's going to perform analysis on your data if you click in that on that line that says ask a question About your data it'll say it's preparing your q a and when you get in here you'll see a series of tiles questions that power bi determined to ask about your data if you have a link on the right that says show all suggestions click it and it will display more tiles if there are any so you

can review these tiles and see what they're saying the thing is is there may be questions that it comes up With that you look at these and you say you know I should have thought of that that is a great question you can click on any of these tiles and see the result I'm going to click on the tile that says show defect quantity supplies and total defect quantities and when I click on that it creates a visualization for me so it's showing me the information I requested Now if I want this information to be

on my dashboard in the upper right hand corner I can click on pen visual and it's on our existing dashboard so we're going to do pin and in the upper left hand corner of the screen you'll see an arrow and it says Exit q a you can go ahead and click that and again everything you add to your dashboard will come up at the bottom so you can scroll down and see that new Tile that was added if you want to spend a few moments going back to q a looking at different tiles and pinning

them or not feel free to do so there's a data analytics feature that's built into the service called quick insights where power bi will analyze all of your data and offer you a wide variety of visualizations in order to get access to Quick insights we need to go back to our workspace So you can use that next to the last button on the left hand side to get back to your current workspace quick insights are performed on data sets so we're going to go to the more options button for the supplier quality analysis sample data

set and you can click on get quick insights or view insights if you've accessed this before if you're getting quick insights it'll have a pop-up box in the upper right Hand corner letting you know that it's getting them and when they're ready when they're ready you can go back to your data set and click on view insights foreign and it tells you these are quick insights for supplier quality analysis sample a subset of your data was analyzed and the following insights were found there is a learn more link there is help every step along the

way in power bi which is a good thing So it gives you these insights impact has noticeably more total downtime minutes total downtime minutes max and total defect reports so it gives you visualizations as well as the written insight and just like q a you may come upon things that you decide you know what I really should have that visualization on my dashboard so the one I'm looking at total defect reports by material type That might be good to put on the dashboard it has the pin in the upper right hand corner and I'm going

to go ahead and pin it to my dashboard you can scroll through these other insights and if you see anything else that you want to add to your dashboard go ahead and pin it I found another one the count of plant by category that I'm going to add to my dashboard and then I'm just going to go back to my Power bi video workspace and access my dashboard again and remember scroll down and toward the bottom you will see the insights that you just pinned to your dashboard I love quick insights in Q a oftentimes

I see things that I hadn't thought about so they're very helpful features there are just a few other dashboard features I'd like to show you one of which is setting a featured dashboard when you set a dashboard you can only Have one at this time as featured whenever you go into the service it will open with that dashboard already displayed if you have multiple dashboards that you're working on you can set the one that you're currently working on as featured and then set the others as favorites which gives you easier access to them I'm going

to show you how to set this dashboard as a featured dashboard you're going to do it by using your more Options button and choose set as featured it wants you to confirm that you do want to set this as your featured dashboard so I'm going to confirm it and now it lets you know that it's featured so if you go out of the service and back into it this dashboard would show now for the others that you work on frequently you can make them favorites let's go back to our workspace And hover over your retail

analysis sample dashboard and use the star to add to favorites do the same with your sample Superstore dashboard it's easy to navigate to favorite dashboards because on your left hand side in your navigation pane you have the favorites icon and when you click there you'll see any dashboards that you have set as favorites if you end up setting a lot of Dashboards as favorites instead of scrolling through what could be a potentially long list you can use the search at the upper left hand corner let's go back to our workspace and let's go back to

our supplier quality analysis dashboard we're not going to actually go to the dashboard we just want to be able to see it on the screen to the right of it you have your more options go ahead and click that And choose view usage metrics report this is a good report to have I mean you can come in here and you can see all of the ways that this dashboard may have been distributed um the platforms how many views per day unique viewers per day shares per day it gives you a synopsis over on the right

side for your total views viewers and shares it does a ranking in here comparing this dashboard against other dashboards in Your organization and it will also show you the total dashboards in your organization and the views by user so as people once you share and people start viewing your dashboard you'll be able to look at the dashboard usage metrics you're able to print your metrics from the file menu you can also export so you can analyze the metrics in Excel you can even pin your metrics to a Dashboard the last thing I'm going to cover

in this module is how to configure your dashboard for mobile view nowadays A lot of people are accessing information from their phones or tablets so it's important that they be able to access your information in that way we're going to go back to our workspace and we're going to open up the supplier quality analysis sample dashboard again and by the way before we do this If you go back to more options you can see that you can disable this as the featured dashboard if you're pretty much done working on it and you want another one

to open up when you go into the service this is how you disable it again you're only allowed to have one featured one but to get to the mobile layout we're going to go to the edit drop down and we're going to click on mobile layout and it changes the views so it kind of Looks like a cell phone there and what you can do is from here it has some of the tiles from the dashboard it just picks ones that it think will fit in this mobile layout if you wanted to unpin all of

those tiles you could from here and if you do that then you can reset them what we're going to do is we want to keep the top two tiles because those are cards cards are good to put in a mobile layout They don't take up too much space so for the map I'm gonna hover over it and hide the tile so it unpins it and for this column chart I'm going to do the same and you can see the unpinned tiles here now there's more on here we really just want to keep the cards so

it pretty much grabbed everything but you could only view a certain amount in the mobile layout if you want it to view more that's fine but we want to get rid of everything that's not a card Foreign so take some time and do that and when you're satisfied with your mobile view it's already set you can get back to the other view by clicking on this mobile layout button and going back to web layout we'll do more work on workspaces in a later module this module was action-packed we started by creating a dashboard and pinning

visuals to it We went in depth with real-time dashboards so we started by creating a form we set up a push data set created some visuals we used power automate to generate a flow that would take the data from responses on the form and push it into the data set and we saw our visualizations populate we moved on to enhancing a dashboard by adding a custom theme and video to it we configured a dashboard tile alert again that's only for gauge card and kpi Visuals at this time you got introduced to q a and quick

insights for analysis and we added some of the results to the dashboard you learned how to set a featured dashboard and how to unset it because you're only allowed one and you learned that you can add multiple dashboards as favorites so they're easy to navigate to you also learned how to configure a dashboard for mobile view hi everyone I'm Trish Connor Kato and I'd like to welcome you to Microsoft power bi module 9 will guide you through how to create paginated reports and power bi these types of reports are designed to be printed and or

published they can also be exported to PDF PowerPoint and they are formatted to fit well on a page they will display all of the data in a table even if it spans multiple pages and when you export them or print them You will see that that table will span multiple pages with regular report visualizations in power bi if you use the table visualization on a regular non-paginated report and that table spans multiple pages when you print it or export it it's only going to print or export what it sees on the screen meaning you won't

get the whole table printed or exported paginated reports are only available in premium workspaces and in this module You'll learn how to make a workspace a premium workspace and we'll be accessing a sample data set from within the power bi service for this module in order to create paginated reports in power bi service you need power bi report Builder it's a separate application when we get into the service I'll show you how you can gain access to it and download it it's the only authoring tool for Paginated reports this is the only game in town

where you can create them you use the report Builder to develop reports preview them and then you end up publishing the reports to the service you can add those reports to a dashboard in your service report Builder is a developer tool it's not meant as a report consumption tool so once you publish your paginated report to the service you can put it on a dashboard and distribute that so users Have access to it so when we get into the service I'll show you how you can download report Builder or I've already put a link in

the website links for additional information document in the video description so in this module we're going to use the power bi report Builder to create paginated reports we're going to design a multi-page report layout We're going to define a data source and a data set within report Builder we'll publish our report to the service and then we'll use the export feature so you can see how it exports as a PDF so I'm back in the service and I'm in my power bi video workspace and the first thing we want to do is bring in some

sample data from Microsoft if you look in the lower left corner you'll see that arrow that seems to be pointing right and that Arrow if you Hover over it it says get data we're going to click on that Arrow if I scroll to the bottom of this screen under more ways to create your own content I am going to click on samples and these are some sample data sources that they have within Microsoft we're gonna look for the supplier quality analysis sample tile and click on that and it brings up a subsequent screen you can

learn more about it it tells you about this data that's in here And we're going to click on connect so it says in your upper right hand corner it's importing data it could take a little while in my case it didn't take very long at all and when I look in my power bi video workspace I have both the supplier quality analysis report and the supplier quality analysis sample data set let's click on the link to go to the report so this one comes with a lot of visuals already in it as you'll see on

the left Side we have three pages of visualizations and I'm going to navigate to the top bottom analysis page just to point out there's a table on this page and it has a scroll bar in it so you know all of this table data would not fit on one page if you were to print it so let's just see that this is a regular power bi report we haven't learned how to create the paginated report yet but we want to see what this will look like If we were to print it to show it in

print preview I'm going to do the drop down for file and choose print this page and it will open up the print preview window for me so when I scroll down print preview you see that it's not showing everything in that table this is print preview it's showing the scroll bar but you can't scroll and print preview for that so this is the difference between a basic Power bi report visualization and a paginated report and I'm going to just cancel the print preview now I'm ready to turn my workspace into a premium workspace so that

when I build the paginated report I can publish it to a premium workspace so what I'm going to do on the left side is I'm going to go to my workspaces icon I'm going to hover over my power bi video I don't know if you're using my workspace or another one but whichever One you're using where you put the sample data is the one that you want to make a premium workspace I'm going to go to the more button on the right side of my workspace and access workspace settings on the top I'm going to

click on the premium tab and I'm going to choose premium per user so you have two premiums there you have premium per user premium per capacity in the first module we talked about the Different licensing and subscription types I have premium per user so I'm going to go ahead and click save and it's letting you know that you're changing workspace access so only people who have premium per user licenses will be able to access this workspace anybody that has any type of Premium access licensing will be able to access this workspace but the reason why

we're doing this is because you can only publish paginated reports to a premium workspace So I'm going to click on continue and when I go back to my workspaces you'll see that it has the little Diamond icon next to power bi video and that indicates that it is now a premium workspace I'm going to navigate back to my workspace my power bi video premium workspace and I'm going to just use the link in the upper right hand corner to get back to it in order to access report Builder we're Going to click on new and

when you click on paginated report if you do not have report Builder a splash screen will open with a download button feel free to pause this video so you can download it and we'll continue with the module since I already have it when I click on paginated report it's going to launch power bi report Builder okay on the splash screen for new report They have four they actually have three Wizards there table or Matrix wizard chart wizard map Wizard or you can do a report from scratch we're going to use the table or Matrix wizard

so I'm going to just click on that and the first screen that comes up is to choose a data set we're going to connect to the supplier quality analysis data set that's in our premium workspace so we're going to just do the next button in the lower right Hand corner and it says choose a connection to a data source we don't have any connections listed here so we're going to click on the new button and we're going to name our data source here where it says data source one we're going to just name it supplier

quality analysis and underneath that where it says select connection type it defaults to Microsoft SQL Server we're going to do the drop Down and we're going to choose power bi data set and the connection string area to the right you're going to click on build so it's going to bring up this screen select the data set from the power bi service right it's looking at it has a listing of all your workspaces on the left I'm going to go to my power bi video workspace and I'm going to click on supplier quality analysis sample and

then choose The select button in the lower right hand corner so now you'll notice that the connection string is dimmed out but it's filled with data and we're going to test the connection before we continue by clicking the test connection button it's always a good idea to test your connection it lets me know that the connection was created successfully so I'm going to click ok and then I'm going to click OK again and Now it shows supplier quality analysis as my data source connection and I'm going to do the next button in the lower right

hand corner in the new table or Matrix dialog box it says design a query build a query to specify the data you want from the data source we're going to do just that if we had this in power bi desktop these would be all of the things that you would see in the fields pane under model On the left hand side in that list we're going to expand Vendor by clicking the plus sign in front of it and then we have another vendor that shows up that also can be expanded expand that one as well

and then you'll actually see the field name vendor it has like one little dot in front of it we're going to click and hold on that field and drag it right into this space over Here and drop it so now it just shows vendor there on the left side under model we're going to expand measures and then expand metrics and there are two that we're going to want to choose from here and we're going to drag it into the query window the first is total defect quantity so we're going to grab that and drag it

to the right of vendor notice it puts a guideline there so it won't let you Overlay it and the other one we want the other measure that we want is total downtime minutes I'm going to drag that into the query window as well these are the three fields that we're going to want so in the middle of your query window you can click on click to execute the query and it will show you the underlying data we're going to go ahead and click next this screen allows us to arrange the Fields to group the data

in rows columns or both and choose values to display in the available Fields list on the left you're going to double click on vendor and it goes into row groups if you wanted it in column groups you could just drag it from available fields to column groups but it defaults to rows and we're going to use our two measures as values it knows their values so if we double click them it will put both of them in the values box and the default Value calculation is the sum function if you do the drop down next

to either of those measures you'll see the other functions that are available we're going to leave it on some and we're going to choose next at the bottom so this one is just preview of the report so it's going to have the vendor the total defect the total defect quantity and total downtime in minutes for each vendor And now we're ready to go ahead and click finish once we clicked finish it puts us in what's known as design view in power bi report Builder we have a little bit of a quick access toolbar all the

way at the top in the upper left let's go ahead and click save and you're going to save this report as supplier quality analysis notice the type of report is report definition language RDL go ahead and Save so it updates the title bar we know that we saved the work that we've done so far the first thing we want to do is we want to sort our report in descending order by the sum of the defect quantity so in that design grid I'm going to right click in the second you can see where it says

sum total defect the one that's not bolded I'm going to right click on that and on the shortcut menu I'm going to Hover over row group and click on group properties on the left side of the group properties dialog I'm going to click on sorting and I where it says sort by vendor I'm going to do the drop down and I'm going to choose total defect quantity and to the right of that in the order I'm going to do the drop down and select Z to A for descending so we could have clicked on any

of these fields and done this I tend to stick to The field that I'm trying to effect change on when I work in here and at the bottom I'm going to click ok so when we ultimately run the report it will be sorted in descending order by total defect quantity go ahead and save again you want to save as frequently as you can when you're working with the report Builder we can always run the report to see what it will look like at whatever stage of development it's in if you look up at Your Ribbon

the Home tab of the ribbon the first button is run go ahead and click that button and you'll see that it loads the report and so we'll see what it looks like at this time a couple of things I notice is we might not want our headings to wrap within the cell like that so we might want to make those columns a little bit wider you'll see that it is sorted in descending order at this point by total defect Quantity which is the sort that we instituted and you can use control end to get to

the bottom and you will see the Total Line at the bottom of the report to get back to design view you only have the Run tab up here now and in addition to the file tab but the Run tab the first button is design and it will take you back to design view now we're going to address our column width so if you click in the little Table area there what you're going to want to do is you want to click above where it says total defect quantity when you hover your mouth in between the

dividing line between total defect quantity and total downtime minutes your mouse will change to a double-headed Arrow I'm going to click and hold and I'm going to just drag it so I see the full title total defect quantity and it's not going to wrap in the cell like it did on when we ran the report I'm going to also widen the column for total downtime minutes so we have the same effect where it's not doing a word wrap in there and I'm going to click away from it the next thing we're going to do is

we're going to replace this report title where it says click to add title right we're going to replace that with a text box with our report title in it and we're going to cause that text box to repeat On every page of the report so what I'm going to do is I clicked where it said click to add title but I'm going to click on the border of that so you get the sizing handles around those little circles and squares those are sizing handles and I'm going to press delete on my keyboard because I want

to insert a text box in that area and we do that from the insert tab on the ribbon in the report items group you're going To go ahead and click on text box and as you move your mouse over your report design you'll see it looks like a plus sign with a box attached to it I'm going to click as far as I can get into the upper left corner of the report design canvas I'm going to click and hold and I'm going to just draw a text box about that size foreign is already in

the text box so we just need to type the title of this report And that's going to be total defects and downtime minutes per vendor so I'm going to just type it in that text box total defects and downtime minutes per vendor and I'm going to select the text and on the Home tab you have a font group I'm going to use that big a to increase the font size so I clicked it once I'm going to click it again and again so I click that uppercase a to increase font three times For our title

once my title is formatted the way I want it to be I'm going to select the text box again it's selected when you have the sizing handles around it and you'll notice on the right side of your screen you have a properties window if your properties window is not showing you can go to the view tab on the ribbon and check the box in front of properties and then it will show over on the right so we're looking at the properties for That text box the properties are in groups and we want to find underneath

other you're going to click where it says repeat with click to the right of over there and when you access the drop down to the right of repeat with you only have two choices none and tablets one we're going to choose tablets one which is basically that table of data on design view So we're telling it to repeat wherever that table is so if the table spans multiple Pages the report title will repeat on every page go ahead and save your report Builder file we'd also like the column headings for the table vendor total defect

quantity and total downtime minutes to repeat across Pages as well that's a little bit more in depth to get that to happen than the text box that we just did for the report title so to make Those column headings from the table repeat on the bottom of your screen you'll see row and column groups if you don't see those boxes on The View tab you want to check grouping and they will appear at the bottom of your design view to the right of the column groups heading there's a down arrow you're going to click that

and select advanced mode and it just makes everything in those Two row and column groups you have a lot of things that say static now under row groups you're going to click on the first static and now you'll see on the right the property screen is different you have an other section for properties and you're going to toggle fixed data from false to true and there's a drop down to the right of false that you can use to select true you're going to toggle Keep Together From false to true and you're going to toggle repeat

on new page from false to true and I made an error in that selection so toggle Keep Together back to false and what I meant to say is you're going to toggle keep with group from none to after those are the settings we need to get those column headings to repeat on every page we need to say that it's fixed data That we want to keep them with the group that's coming after it and we want to repeat the heading on new pages we're going to return to the drop down to the right of column

group and turn off advanced mode and then go ahead and save your report again now if you look at the report data pane on the left side in design view you have a category called built-in Fields go Ahead and expand it you'll notice the first one is execution time when is the report executed when is it run and that's already in the footer as a placeholder let's go to the Home tab of the ribbon and run the report the first button and we'll see that we have our title at the top we have our column

headings again it's sorted in descending order by total defect quantity If we scroll down you'll get a sense of how much data is here so you know that this data is going to appear over multiple Pages we're going to use the first button on the Run tab to go back to design view and run it again this time when it loads the data do control end and it takes you all the way to the bottom and you'll see the footer that's the execution time built-in field that Was already placed in the footer section we can

go back to design view now you have other built-in fields that you can use you know if you want the page number and the overall total Pages the name of the page so on and so forth the report name that's kind of like placeholder stuff that you can use on your report I'm going to just save the report again and now I'm interested in publishing it to the workspace So on the Home tab the last button is in the Share Group and it's publish go ahead and click on your publish button remember you can only

publish to a premium workspace so when I'm on my workspace notice publish button is dimmed out that's because that's not a premium workspace I'm going to click on my power bi video workspace and then when I click in the file name box I'm going to type supplier quality analysis and notice my publish button is active here so I'm going to click it it lets me know that it's been successful and right from here just like when you're publishing from the desktop I can open supplier quality analysis in power bi I'm going to go ahead and

click that link And it's loading the report in the service notice it's in my power bi video workspace and it generated my report I want to start by going to the workspace so I'm going to go up here and use the link to get to my power bi video workspace and you'll notice if you scroll down if necessary whatever workspace you're in you have two reports with the same name supplier quality analysis notice the icons are different Though the one that looks like a chart like a column chart that's a regular power bi report icon

the one that looks like a page that kind of has its upper right hand corner folded that is the paginated report I'm going to use the link to get to that paginated report so we can go over it may take a moment to load it shouldn't take too long though and when you're looking at a paginated report you have a couple of different Views here the the second button on that toolbar at the top is view it's in default view right now and there's a page navigation area right here where you can click to go

to the next page which it will load notice that it's repeating the report title as well as the column headings on the pages so you can navigate through some more Pages and you'll see that in default view this is the view that if users are consuming this report in the service this is the view that they'll be using if I go to the file drop down and choose print report and a lower left hand corner there's a preview button at the top it tells you we'll create a printer friendly pdf version of your report I'm

going to click on preview And it may take a moment to load again but when I previewed a report and there's my page navigation at the top and I navigate to the next page it's going to load every time for a page you'll see that it's not repeating the report title it's just going to repeat the column headings the report title is a text box object so that's really if you want to repeat it it's only going to repeat when someone is consuming the report And not actually printing it or exporting it to a PDF

you don't really need that heading to print on every page but it will show up if I go back to default view then you'll see it on every page so an end user can consume it online like this and get that heading on every page but when it's printed or exported it's only going to repeat the column headings so for paginated reports end users consume it into service or you print it And distribute it or you can export it to any of these formats Excel PDF accessible PDF you can export it to PowerPoint word so

on and so forth and distribute it that way let's go back to the workspace that has the paginated report in it and let's say you look at your report in the workspace and you decide that you want to make changes to it if you hover over the paginated report and go to the more options button you'll See that the top option is to edit in power bi report Builder let's click that and it will relaunch report Builder for you and it brings up your report so if you wanted to make any changes in here let's

add something else to the footer area on the left side and I can maximize this screen so you're not seeing my workspace behind it on the left side I'm going to expand those built-in Fields again and this time I'm going to drag Overall page number into the footer section on the left side and I'm going to save this report click in the pane oh it's considering it already saved I'm going to collapse built-in Fields here and I'm going to just click the publish button again and it's bringing up because I've already published from here it's

bringing up supplier quality analysis and we're going to use the same one we're going to basically overwrite it by Publishing it lets you know that that item already exists you want to replace it we're going to click on yes and then you can open it again in power bi I'm going to just drag my scroll bar to the bottom and I'll see the page number there and then the date and time that was already in there for the execution date and time So in this module you learned what paginated reports are and that they're meant

to be consumed in the service or by exporting to a PDF Printing and distributing there's no option to put them on a dashboard they're not meant to be consumed that way you created the multi-page paginated report in power bi report Builder by creating and configuring a data source and a data set we use the table wizard to create our report We published the report and reviewed the print and Export options you also learned that you can only create a paginated report in a premium workspace so you learned how to make a workspace a premium one

in module 10 you'll learn how to perform Advanced analytics on your reports you'll be introduced to report features that perform analytical insights into the data you'll learn how to perform Advanced Analytics using an AI visual on the report for deeper and meaningful Data Insights we'll be working in power bi desktop after a brief task in the service and we'll be using the sales and marketing sample desktop file and we'll be able to grab that from the service we'll start with our Advanced analytics lesson which contains features like grouping binning drill down and up and analyze

We'll move on to using the key influencers AI visual then we'll get into creating an animated scatter chart which is really cool we'll use a visual to forecast values and end up creating a custom Advanced analytics visual now would be a good time to get back into Power bi service so earlier we accessed Microsoft supplier quality analysis sample data and we brought it into the service and It gave us the data set and the report we actually ended up putting the report onto a dashboard so what we're going to do now is we're going to

go back to that get data arrow in the lower left hand corner we're going to scroll down and in the lower left click on samples and this time we actually want to pull the desktop file not the report and data set into the service we actually want to just download the desktop file So we're going to click on sales and marketing sample tile and instead of clicking the connect button which will give us the data set and any reports we're going to click on learn more right underneath the connect button it's going to open another

browser tab it gives you all the in-depth information about the data that's in this sample and underneath get the sample you can see you can Download the dashboard report and data set if there is a dashboard already in with the sample data in our previous example it gave us the report and data set and we got that into the power bi service into our workspace this time we want to grab the pbix file which is the desktop file you'll notice you can also download the Excel workbook if you wanted to so it walks you through

you can get the sample from the service if you keep Scrolling down you can get the pbix file there's a link there to the pbix file so I'm going to click on that link and it's going to start downloading the file for me actually I have it downloading twice because I clicked the link twice but it's okay I can stop one of them and once it's done downloading I'm going to go to my downloads folder on my computer and copy that file into the working directory that I've been using For this class and then I'm

going to launch it and it's going to open up power bi desktop and it will have that data already in it foreign the first page in the desktop file is an info page so this data is provided by a company known as obvious which works in conjunction with Microsoft and we don't need that page so I'm going to just hover over the info page Tab and do the little X in its upper right hand corner It will always prompt you if you're going to delete a page so I'm going to go ahead and choose delete

on the prompt and it's going to start rendering the images on each of these pages so we have a market share page year-to-date category page a sentiment page and a growth opportunities page if it needs us to use Microsoft are open on a specific type of visual to render it it would notify you as you saw on the Screenshot in the slideshow the first thing we're going to talk about here is the concept of grouping and power bi you can group Fields together and you'll see how this works I'm on the growth opportunity sheet Tab

and in the lower left hand corner there's a column chart if I click on that chart and make it active I can see that they're using the segment field for the axis and total units for values so we're seeing all the Segments if we want to take a look at the data first let's go to data view on the left hand side and we want to click on the sales fact table and you can see some of the fields that are in that table it's a lot of measures in that table right we also have

a sentiment table which I'm going to click on in the fields pane so we can see what's in the sentiment table if we scroll down in the fields pane We'll have the date table we have a geographical table let's expand the manufacturer table or click on it and you'll see the data in there and we have one remaining table at the bottom which is product and the product table is where the segment is coming from we can go back to report View and that report is still selected on the growth opportunities page so we know

that segment is coming from The product table also in the fields list the tables that have the yellow check mark are the tables that are being used in the visual so segment is coming from the product table and total units is coming from the sales fact table grouping is something that's typically done on a visualization and for this column chart we decide that we want to group the youth and regular segments together so in the column chart I'm going to click on youth I'm going to hold down my control key and click on regular so

both of those segments are selected I'm going to right click on youth or regular and I'm going to choose group data foreign so it flashed on the screen really quickly that it was working on it and now it's done the grouping so if you look at your Legend right it says segment groups and that's because in your Fields pane it created a group With those two segments in it and it named it segments group and it added it to the legend in the visualizations pane so regular in youth are the darker color columns they're grouped

together and all the other segments have the blue columns so now when you're looking at that column chart you're actually analyzing the data in a different way because of the groupings go ahead and save your sales and marketing sample file Grouping is typically performed on data fields our next topic is about binning which is performed on numeric Fields we're going to start this by creating a new chart on a new page let's go ahead and click the plus sign to the right of growth opportunities and we want to select the clustered column chart visualization in

the visualizations pane and I'm going to go ahead and expand the size of that chart we're not going for exact here And I'm going to name that new page binning just so you have a reference later for what we did on what page in here if you want to you can go to the growth opportunities page and put slash grouping after the name so you'll be able to find your way back to the page where we did the grouping on so we're going to build this clustered column chart um we are going to in the

field pane We're going to expand the Geo table and we're going to drag region make sure your chart is selected here we're going to drag region to axis in the visualization pane we're going to expand the sales fact table and we're going to grab the sales dollars and put that in the values field in the visualization pane so now we want to create bins for years binning is very similar to Grouping except you don't do it on the visualization you do it from the fields pane and we're going to expand make sure your date table

is expanded in the fields pane and we're going to right click on year in the date table and choose new group so because it's a numeric field this screen looks different it has group type as a bin and the bin type is size of bins you only have two choices there For bin type size of bins and number of bins we're going to leave it on size so this is kind of like bending is kind of like grouping certain amount of year Fields together in one group and we decide we want our bins to be

a size of five which represents about five years go ahead and click OK after you change the bin size to five and just like a group it creates a new field in the fields pane so in your date table you have your year Bins I actually have two of them in there I'm going to get rid of one for some reason but you have your year bins that you just created and what we're going to do is we're going to drag year bins to the legend for this visualization and so now you'll see the legend

right the bins have coloration 1995 then the next one is 2000 the next one is 2005 and in 2010. each bin is consisting of Five years so when I hover over any column on the chart right it's telling me if I hover over the green color it's telling me it's in the 2010 bin the orange color is in the 2005 bin so on and so forth so binning is like grouping but grouping different numeric data together in our case we're grouping by five years you will have noticed that both the groups and the bins go

into the legend of a visualization Go ahead and save your file our next topic is drill down and up on a visualization in order for you to be able to drill down and up on a visualization the visualization must contain a hierarchy a hierarchy is a container of sorts for related fields so the first thing we're going to do is we're going to make a duplicate of our binning page so I'm going to right click on the bidding page Tab and I'm going to choose duplicate page I'm going to rename the duplicate of binning to

drill down slash up which is the name of the feature and the reason why we did this is because we're going to create another column chart that's very similar to this one so instead of starting from scratch we're going to just modify this one so I'm going to select the column chart and I can see that region is in the axis The year bins is in Legend and sales dollars is in values well we're going to create a hierarchy first of the region field and we want it to include the region and the state so

in your Fields pane in your Geo table what you're going to do is right click on region and choose create hierarchy from the shortcut menu so now underneath the region field you Have region hierarchy in your Fields Pane and you can expand that and you'll see that it only contains region because that's the field that we base the hierarchy on we also want to include the State field in that hierarchy so we're going to right click in the fields pane on the State field and choose add to hierarchy and then select region hierarchy so now

we have a hierarchical field which will allow us to use the drill Down and up feature in your visualizations pane do the X to the right of region in the access box to get rid of it and we're going to drag the region hierarchy field to the access box instead now a couple of things happened first though before I point them out go ahead and save your file now you have additional buttons on your visualization you'll see three of them In the upper left corner of the visualization and you'll see another one that just looks

like a down arrow over to the right so when you're using drill down up feature you have to enable it on the visualization and you enable it by using the down arrow button that's on the right side the upper right side of the visualization when you hover over that button the screen tip will tell you it says Click to turn on drill down so we're going to just click that button and it enabled the feature you can tell the feature is enabled because that button now has like a black circle around it let's talk about

the three buttons that are on the left side above your visualization and the upper left hand corner of the visualization you have three other buttons and those three buttons control how you drill down and up on your visualization So these three buttons right here do different things the first one if you hover over it it looks like an up arrow and it's currently dimmed out it's the drill up button we're at the top level in our visualization we're showing the regions right here East Central and West and we have our year bin so we have

that going on as well the next button it has the double arrow and that one is your drill down button the double down arrows is your drill Down button so we're going to go ahead and click that button since state is the next level in our hierarchy we are at the lowest level of the data because we only have two things in our hierarchy region and state so when I hover over that dimmed out double down arrow button it says you're at the lowest level of your data if I want to get back to region

I would use the up Arrow which is now enabled Which says drill up so I'm going to use that and now I'm back at the highest level which is region so by going through the different levels you're able to analyze your data in different ways right now we're analyzing it by region but we have a third button up there we actually have a third button on the upper left hand corner that's part of this feature set and that third button if you hover over It it looks like an upside down Pitchfork to me but if

you hover over it it says expand all down one level in the hierarchy when I click that button now I'm seeing the region and the state so it's combining things it's another way of analyzing your data I'm gonna go back to the up arrow button the drill up button and I'm back to my highest level which is region so the drill down and up feature requires a hierarchical field That you can utilize in the visualization and just by having a hierarchy in your visualization it will give you all the drill down and up controls on

your visualization go ahead and save your file at this point we decide that we want City to be an eye hierarchy as well so in the fields pane in the Geo table I'm going to just right click on City add to hierarchy region hierarchy and it updates so now we'll have another Level of drilling down the feature is still enabled so now I'm going to do my drill down button and it's going to take me to the state level and I should be able to drill down again to get to the city level and it's

not letting me drill down to that level so what it did is it added City to our hierarchy which we can clearly see in the fields pane but we need to get the city to be in the access Box as well so what I'm going to do is I'm just going to uncheck region hierarchy and then I'm going to drag it back into the access box so now we have region state and city in the access box I'm going to go back to enable to turn on the drill down feature so on the right side

and now I'm going to use my double arrows on the left side to drill down now I'm looking at the state again drill down again And I'm looking at City information and it's overwhelming the chart which is why we now have a scroll bar in there I'm going to use the drill up button to drill all the way back up to the top level which is region and then I'm going to use my upside down Pitchfork to expand all down one level in the hierarchy and so we're seeing the region and the state and now

that we have another field in the hierarchy we can click the Pitchfork again and now I'm seeing the region the state and the City go ahead and do your drill up till you get back to just region and save your file our last Advanced analytic technique that we're going to get into now before we get into artificial intelligence visuals is analyze it's on every single report page that You have in power bi desktop and to get to the feature I'm going to still be working on a drill down up page right now I'm going to

just select that visualization and I'm going to right click in a blank area of it and I'm going to hover over analyze and it says find where this distribution is different go ahead and click on that and it will bring up a bunch of analysis information that power bi just scanned all the data and came up with And so it's saying here are the filters that cause the distribution of sales dollars by region to change the most so California has 14 percent of the records Texas 6.5 percent of the records in Florida six percent of

the records and those three most affect the distribution so it's showing you California here right and there's a tab for Texas right there so I can see that that's in a different region and in Florida which is in the east region And notice at the bottom it says it's comparing proportions which it's doing now there is a scroll bar to the right of that and I can see other analysis information that it gave me so now it's looking at category rural has 36.6 percent of the record so on and so forth I can keep going

down and there it is by segment it did an analysis by segment there is a calculated column Manufacturer is van arsdell so that's coming up there and so no 77 percent of the records don't have that manufacturer is what that's telling you but this is just power bi looking at the data analyzing it and giving you these tiles with the analysis now let's say I want to keep a tile I'm going to just scroll back up to the top and I want to keep this first tile and it's upper right I'm going to click the

Plus sign to add it to this page now there's also a thumbs up and a thumbs down the power bi people at Microsoft are really committed to listening to end users so if these analysis are not good for you you can give it a thumbs down and say this is of no use or you if you really like it you can give it positive feedback I'm going to go ahead and click the plus sign so I add it to this page and then I'm going to click on a blank area of the page and now

I'm going to Resize my column chart and then resize the analysis that came in at the bottom I'm going to just make that a little bit taller so I can see it so that's a built-in feature in power bi desktop it's the analyze feature and you get it by right clicking on a blank area of a visualization now we're going to create an artificial intelligence visual and there are two ways you can access Them in the desktop one way is from using the insert tab of the ribbon and the other way is from the visualizations

pane I'm going to go ahead and click on the insert Tab and you'll see there is a group called AI visuals so you were already introduced to q a in module 8. and we're going to focus on creating a key influencers visual now I did a little bit of pre-work I created a new page named it key Influencers I also created three more pages for upcoming stuff but you don't have to do those now just create a new page called key influencers for right now and then we're going to go up and click the key

influencers icon on the insert tab we want to expand the framework so it fills the canvas and when we look in the visualizations pane there are three fields for it Analyze explained by and optionally expand by we're going to use something that's in the sales fact table to analyze and that is going to be the sum of Revenue so as soon as we do that you'll notice that it plays two tabs on your visualization key influencers which is the default Tab and then top segments and it gives you what influences some of Revenue to increase

and if you do the drop down You'll see decrease we will go over this in its entirety once we're completed the visualization we're going to end up adding three fields from the product table for the explained by field so I'm just collapsing sales fact expanding product and the first field we want to explain by is manufacturer so I'm going to just drag it and drop it in there now your visualization says no Influences found try adding some more fields in to explain by so we are we're going to add the product field underneath manufacturer and

explain by and your visual updated a bit more again we'll review it when we're done we have one more field that we want to drag underneath product and explain by and that is the category field so let's review this visualization right now we're looking at what Influences the sum of Revenue to increase the influences are when the manufacturer is one named Van Arsdale and when the category is Urban and it's showing you the average of the sum of Revenue increases by so it gives you the number there on the right side you have a chart

a column chart that's built it tells you the sum of Revenue is more Likely to increase when the manufacturer is Van Arsdale than otherwise on average and it's showing you the average that's the red dash line excluding selected and it gives you a value there and you're seeing Van Arsdale is the tallest column now at the bottom there's a check box it says only show values that are influencers and we had no change to our chart when we clicked on that The other thing you can do with this type of visualization is you can hover

over the bubble the 11.5 M bubble there and it gives you more detailed information basically just in text form this influencer contains approximately 12.61 percent of the data when I click on that it makes the chart disappear and then when I click on it again the chart reappears so let's go and see what it looks like when we change the drop down to decrease So now you have the same chart but different data and it's showing when the average of sum of Revenue decreases by and it's the other manufacturers and one category is mixed in

there yeah and you have the information also displaying in the chart and it gives you the Baseline average let's go to the top segments tab when is the summer Revenue more likely to be low and you have the other option Is high we found five segments and ranked them by averages sum of Revenue and population size you can select a segment to see more details so I'm going to click on the bubble for segment one and it opens up more information underneath it so you're getting the manufacturer is palmum and then it gives you more

text and graphic detail I can click on another one and it updates the bottom half and then I'm going to do the drop down next to low and change it to high and when I change it to high here it's only showing the highest segment I'm going to go back to key influencers and go ahead and save your file our next lesson in module 10 is creating an animated scatter chart let's go ahead and click the plus sign and we're going to name the page scatter chart again This file will be a great reference file

for you after you complete this video course so what we're going to do in the visualization pane you're going to locate the scatter chart visualization and click on it let's resize it so it takes up like the upper half of the paint I'll make it a little bit bigger than that make it as wide as the canvas and now we'll start adding fields to Populate this scatter chart so you notice they have several different fields for a scatter chart we're going to start at the top in the field in the visualizations pane and work our

way down so we are going to want to expand the product table if necessary and we're going to use the category field for details on the scatter chart we're going to add our segment groups field to the legend And from the sales fact table we're going to want Total units year to date so I'm going to expand that table so I can see the full field name and we're going to grab total units year to date and drag it to the x-axis field we still have more fields to add so we're going to use the

size field here and we're going to use total units from the sales facts table in the size field and one more for the play Axis we're going to expand the date table And we want to grab the year field not the year bins just the regular year field and put it in play Axis so now you'll see your scatter chart let's go ahead and save our file scatter charts and power bi come with a play Axis we added year to the play access field so there is a play button and the bottom left corner of

your scatter chart and you can click that and you'll see how the scatter chart is Animating based on the year in the upper right hand corner of the chart it's telling you what the current year is at any given point in time press play again and take a moment to absorb the animation and you see the current year again in the upper right hand corner of the scatter chart it's a really cool feature so far in this module we haven't done any formatting on any of the Visualizations we created we'll get to that at the

end of the module which is two lessons away from being able to format all of the charts we are creating during this module right now we're going to use a visual to forecast values let's go ahead and create a new page and name it forecast currently in power bi the only built-in visual that allows for forecasting is the line chart so in your visualizations Pane find and select the line chart we'll go ahead and make the chart about the width of the canvas and slightly taller and we can always size it after we complete building

it from the date table we're going to add the date hierarchy to the axis box and your visualizations pane add sales dollars from the sales fact table to the values field and we're not going to add any more fields at this time you'll see the line Chart is showing the years of data and when you hover over a data point it gives you the sales dollar value so far in this course we've used the fields well which we've been using now to add fields to our chart we've used the format well which is the paint

roller and again you'll get to go back there at the end of this module and we haven't used the analytics well we're going to click on the analytics icon right underneath your Visualizations and it opens a whole another set of categories one of those the second one from the bottom is forecast go ahead and expand the forecast category and we're going to click the plus sign for the add button so we can add a forecast to this visualization now there's defaults that are already filled out so you actually see the gray shaded forecast area on

the line chart it's forecasting by default 10 points Which for our purposes and our data equals 10 years because we've accessed formatting we can do the drop down arrow next to points and you'll see the rest of the date hierarchy as well as the time hierarchy that's within it we're going to choose years nothing's going to change on our chart we just decide that we want the forecast length to say 10 years now we're going to tell it To give us our forecast but ignore the last two years so it will use the data set

for the forecast except the last two years and that's the next setting down so ignore last in that box we're going to change to 0 to a 2 and you don't notice anything changing on the chart you need to scroll down and you'll see an apply Link at the bottom of those categories go ahead and click apply and now you'll see the change on the Chart the green line is representing actual data and the black line is representing forecasting data so we told it to ignore the last two years and that's why you have that

green line extending into the gray area so a forecast is an estimate the next category down is confidence interval it defaults to 95 percent the confidence interval tells you more than just the possible range around the Estimate it also tells you about how stable the estimate is let's see what happens if we change it to 75 percent there's a drop down arrow and you can select 75 percent and click the apply link again so it Narrows the forecast it's less confident and it's looking at less of a range of data the next category for forecasting

is seasonality it defaults to Auto points Seasonality refers to predictable changes that occur over a one-year period in a business or Economy based on the seasons including calendar or commercial seasons so we want it to look within a five-year cycle of our data we're going to change the seasonality to Five Points and again you're going to click the apply link to make that change show up on the chart so now it's really looking at a Five-year cycle within the data set and the shape of the gray shaded area has changed go ahead and save your

file now if you scroll down farther down underneath forecasts you'll see some formatting options there the color option will change the Shaded area as well as the color of the forecasting line I'm going to do the color drop down and I'm going to choose an orange color an Orange's color and you can see that the forecasting Line is orange and the Shaded area in the back which is showing what is called the confidence band right is also a lighter shade of that orange if you look at your line style drop down the line is solid

but you can make it dashed or dotted if you'd like the forecasting line the confidence band style is set to fill so you have that orange in my case orange filled background I'm going to do the drop down and select none there and It goes away I actually like having the confidence band so I'm going to do the drop down again and choose fill again and if I want that color that orange band's color to be deeper I can drag transparency to the left so it's not quite as transparent and when I let go of

the slider you'll see that it deepened the color So currently the built-in line chart visualization is the only one that allows for analytics and in particular The forecasting ability go ahead and save your file and create a new page so we're set up for our next and final lesson our last lesson in this module is creating a custom analytics visual in addition to the visualizations that are built in you can add custom visualizations in power bi that's pending permissions from your admin and the way to do it is if you Hover over the Ellipsis button

to the right of the last visual you'll see it says get more visuals go ahead and click on it and choose get more visuals it will take you into the app source which is the tab that it's on by default when you go in here you also have a my organization tab let's click on that tab so if you're a power bi administrator has added any visuals for the organization they would be showing on This tab if not and if you have permissions you'll be able to grab some custom visualizations from appsource so let's go

back to that tab you'll notice that there's a search box and there are categories it defaults to the all category and if you know the name of the visual that you're looking for you can use the search box let's go to the advanced analytics category and if you start scrolling down you'll See there's plenty of different custom visualizations in here some of them will say in blue underneath them but they may require additional purchase instead of searching through this list I know the name of the visualization we're looking for let's go to the search box

and just type violin as in the musical instrument and press enter and it comes up with the violin plot Visualization it's an advanced analytics visualization and it's used to visualize the distribution of your data we're going to click the add button to the right of it after a moment you'll get a message letting you know that it imported the visual successfully you can click ok to get rid of that message now when you look in your visualizations pane underneath the get more visuals icon You'll see the little violin and that's the icon for violin plot

it's underneath the visualizations pane which in power bi desktop that means that you can use it in this file but if you close this file and open a new instance of power bi desktop it will not be in your visualizations Pane and you would have to add it again the alternative is to pin it to your visualizations pane so when you find a custom visual that You're going to want to use over multiple files pin it to your visualizations Pane and you can do that by right-clicking the violin plot and choose pen to visualizations pane

it will move its position and it will end up being the last visual in your visualizations pane go ahead and click the violin plot visual so we can build this visualization I'm going to expand The framework so it fills the width of the canvas and I'll make it a little bit taller as well now for this visual we have three fields that we can fill in sampling measure data and category the category field can be optional and you'll see how that works as we start building this so from the products table we want to use

the segment field for sampling And notice it gives you a message please ensure that you have added data to the sampling and measure data fields you can also Supply an optional category to plot multiple violins within your data set we're going to use all three fields so the next field is measure data and from the sales fact table we're going to grab the sales dollar field for measure data and then for the category we're going to go back to the product table and use the Category field for category so that caused us by using the

category field that caused us to have multiple violins they're broken down by category they have different shapes if you notice the legend in the upper left hand corner so your sales dollars are the in my case aqua colored parts of the violin you'll see the median value is the white line in the violin and the mean value is the circle that's In the violin and sometimes they're in the same position so if you hover over your different violins you'll see that it gives you the name of the category the number of samples the maximum minimum

median mean and standard deviation values so a violin plot chart shows the distribution of your data go ahead and name that page violin And save your file now would be a good time for you to pause this video if you'd like so you can go back and apply some formatting options to the visualizations we created in this module starting with the binning page in module 10 you got to explore Advanced analytics by using grouping binning drill down and up and analyze features all of them give you different ways of looking at your data to analyze

it in Different ways you were able to create a Time series analysis with the animated scatter chart that was really kind of cool we use some AI artificial intelligence visuals some of which identified outliers in our data and that would include the Q a our decomposition tree the key influencers visual and how to summarize on a report all of those are really cool features that give you different insights into your data And we ended up by using the advanced analytics custom visual the violin plot which shows the distribution of data hi everyone I'm Trish Connor

Cato and I'd like to welcome you to Microsoft power bi we've already created workspaces in previous modules now we're going to focus on how to manage them that will include how to share content including reports and dashboards and how to distribute an app you'll also learn How to assign data set roles to your workspaces and the data that's in it we have three lessons in this module sharing and managing assets mapping security principles to data set roles and Publishing an app we're going to be conducting this module into service so go ahead and get yourself

into service we're going to get started by sharing assets by assets I mean dashboards or Reports so I'm already back on my supplier quality analysis sample dashboard and right up at the top I'm going to click on share and it opens the shared dashboard dialog you can enter multiple email addresses in here or you can enter groups in here and the thing about it is if you use an email that's outside of your organization none of those recipients would be able to re-share the dashboard So usually before I put in email address or addresses I

check the settings these are the defaults so by default recipients would be able to reshare your dashboard if you want someone to not be able to do that uncheck the Box they can also build content with the data set associated with this dashboard and the other option is to send an email notification so again if you use an email address outside of your organization Recipients would not be able to reshare your dashboard make your choices with the check box go ahead and enter an email address and click the grant access button at the bottom you'll

get a pop-up that says success access has been granted now what else can we do with this let's say you accidentally gave someone privileges to reshare your dashboard and you want to revoke those privileges you Simply go back to share and in the upper right hand corner you'll see the more options Ellipsis go ahead and click it from here you can manage permissions which we're going to do in a second or you can copy a dashboard link that you want to send to someone else go ahead and click manage permissions so this will show you

a list of everyone that has permissions that you shared your dashboard with and in the lower Right hand corner go ahead and click the Advanced Link I've blocked out the email addresses for privacy reasons but on the left side you'll see the related content that was shared so if you expand reports you'll see any reports that went along with this dashboard and we gave them access to the underlying data by sharing the data set with them foreign you'll see yourself as the owner of this Dashboard and you'll see their privileges that you granted the person

you shared it with so the ability to read and re-share to the right of that is the vertical Ellipsis and when you click on that you'll see that you can remove just the reshare privilege or you can remove the access totally I've moved back to my workspace and now I'm going to show you how you can share a report and the options that go along With it so I'm hovering over the supplier quality analysis report this is the power bi report remember it has the column chart icon versus a paginated report which has the page

icon so I'm hovering over the power bi report and you'll see the share arrow to the right of it I'm going to go ahead and click that Arrow very similar to the dashboard you can enter a name or an email address here multiple email addresses or you can Enter in groups you can add an optional message it defaults to sending a link and people in your organization with the link can view and share if you click on that you can say people with existing access or specific people and then the settings underneath control what rights

they have they can re-share the report or not that is a default and or they can build content with the data associated with the report so they Get the data set as well I'm gonna leave it on people in your organization and I'm going to click apply on this screen go ahead and enter an email address and then you're going to click the send button alternatively you could copy a link you could access Outlook to share the link or teams to do so I've already cleared the pop-up that let Me know the link was successfully

shared and so I'm gonna do just like we did with the dashboard for that report I'm going to go back to share and in the upper right hand corner I'm going to access more options and go to manage permissions it shows you who you shared it with just like the dashboard you have another access to another opportunity to copy the link up here if you want to distribute it to Other people and just like before you can click on advanced in the lower right hand corner and you'll see that in my case this link has

been shared with people in my organization and they have the ability to read and re-share the related content just like with the dashboard is on the side and you can look through those options and then just navigate back to your workspace Another way to distribute a report is to copy it and you can put it into another workspace and then give access to that workspace and we're going to do that now we're going to copy the supplier quality analysis report by going to its more options button and choosing save a copy the save a copy

of this report panel opens on the right side we're going to leave it with the same name and then you get to select a destination workspace That you have access to I'm going to go to a workspace called training workspace and then choose save so it gives me a pop-up where I could go to that report and I'm going to click go to report on the pop-up and notice at the top it tells me I'm in the training workspace so now if I wanted to give someone else or other people access to this workspace this

is how you do that you have to go to your workspaces option on your Navigation bar and you're going to hover over the workspace that you want to give somebody access to in this case it's my training workspace and I'm going to go to the more button to the right of it and I'm going to choose workspace access here you have different roles that you can assign and before we assign anything let's review those roles there are four roles that you can use admin member contribute and viewer I have several tables here that show you

the capabilities for each of these roles so members can add members with lower permissions they can publish unpublish and change app permissions notice everything else on the screen only the admin can do and again you'll have a copy of this PowerPoint in the video description so you can go over these capabilities by these four roles at your leisure so now admins and members can update Apps share items and apps allow others to re-share they can feature apps on colleagues home pages if they have permissions to do so and manage data set permissions contributors can only

do this these two items if allowed and the viewer can only perform this item if they're allowed so again I would say that you can spend some time at your leisure going through this table about the capabilities for the workspace roles in power bi There's also a link where it says add admins members or contributors where you can learn more about those roles so on this screen you're going to enter an email address you're going to choose the level that they have and then you're going to do the add button and close this screen go

ahead and perform that and then check out the actions ellipses to the right of each user and look at The options there you can change their permission level from that actions button and you can also remove somebody once you're done reviewing that go ahead and close the panel the last lesson in this module is how to create and publish an app an app and it can be configured with multiple dashboards and multiple reports as you'll see I want to go back to my power bi video workspace so I'm going to use my Workspaces icon on

the navigation pane and get back to Power bi video when I scroll across in this workspace I'll see that dashboards and reports have include an app column and they all default to yes so when I want to create an app it won't include a data set just dashboards and reports in the upper right hand corner you can go ahead and click on create app and so everything if you do it like this Everything that is toggled to be in an app will be in this app so what you want to do first before going in

here is you want to untoggle the things that you don't want to be in the app the dashboards and reports so we're going to cancel this screen and on your workspace screen you're going to scroll to the right the only thing that we want to put in our app is the supplier quality analysis data So for everything other than that I'm gonna untoggle this include an app I'm going to switch these to no and I'm going to keep scrolling down and make sure that I get everything that I want in here so there's my supplier

quality analysis it's doing both reports the paginated report and the power bi report and it has the dashboard selected so I'm going to leave those three on yes And now I'm going to go back into create an app and my app name is going to be supplier quality analysis description I'll just put includes a dashboard report and a paginated report you could put a site in here um where users can find help for something like this I might put in a Microsoft site about creating an app and you could do that through a URL if

you want you can upload a logo for your app you can give your app a theme color and in contact information I usually have Show app publisher to default but you can show item contacts from the workspace or specific individuals or groups up at the top there are two other tabs there's a navigation tab It shows you the dashboard link right if you want to change the order of things that are going to be in the app you can do that on the left side if you get to this point and you decide that you

don't want to show something in the app you can hide it here 's an advanced section where you can give it the default navigation with and then there's a permissions tab where you can give access to your entire Organization or specific individuals or group so I'll have you enter an email address there when we're almost done with this everything that people that have the app can do is these check marks check marks there so they can connect to the app's underlying data sets using the build permission if you want them to they can make copies

of the reports in the app and you can also allow them to share the App and their and the app's underlying data sets so permissions or permissions this is very similar to the dashboard and Report permissions you can also have it install this app automatically for those who have permission when they select it or they can install it themselves so what I'm going to do here is I'm going to go ahead and put in an email address and then I'm going to click the Publish app button when you click publish app it lets you know

that it can take within five to ten minutes or up to a day and then it published so it didn't take that long and I can go to the app from that screen foreign and I can see that it included what I wanted it to include so this is the power bi report here and then this one is the paginated Report that we created in the previous module you have some options at the top to file you have the file drop down so you can edit and power bi report Builder because we're on the paginated

report you can view so these are the same things when we we're looking at the paginated report outside of the app when I go back to the dashboard I have some of the same features there that you have with other dashboards And you have a high demavigation pane here so if you want to collapse that right and then you can expand it again and at the bottom of it you can go back so it takes you back to your home page at this point on your home page you have recents things that have been shared

with you and you have my apps so if I wanted to get back to that app I could I can make the app a favorite just like we can make different Dashboards favorites and it has a more options button where I can open the app or hide it from this screen in this module we focused on workspace collaboration you learned how to share dashboards and reports you also learned how to copy a report and put it in another workspace and then give people permissions to that workspace through workspace roles we ended up by Distributing an

app which Contained dashboards and reports and we learned how to toggle off the ones that in the workspace that we don't want to include in the app the final module in this course is how to manage data sets in power bi specifically you'll learn how to set parameters and how to use the data set refresh options we're going to be using the sample Superstore OD desktop file that was created in module 2 for these lessons Our first lesson will involve how to create a parameter we do that in power query editor then we'll publish the

file to the service and we'll learn how to view the parameters and change them from within the service and then we'll work on how to refresh your data set and then your visualizations parameter is really an efficiency Feature in power bi it serves to easily store and manage a value that can be reused they give you the flexibility to change the output of your queries depending on their value and they can be used for changing the argument values for particular transforms for example filtering and data source functions also they can be used as inputs and

custom functions you may want to go ahead and get the sample Superstore OD power bi desktop File open now a parameter is really an efficiency feature in power bi desktop it's actually in power query editor that you set it up it serves to easily store and manage a value that can be reused especially when you're filtering parameters give you the flexibility to dynamically change the output of your queries depending on their value and can be used for changing the argument values For particular transforms like filtering and data source functions as well as for inputs and

custom functions as mentioned we set up parameters in power query editor so on the Home tab of the ribbon in power bi desktop you're going to go ahead and click the transform data button to open the power query editor window a reminder in here your queries are on the left side and on the Home tab of the ribbon in Here you have a parameters group and there is the manage parameters icon go ahead and click that icon the manage parameters box opens and it's very tiny in here but we want to create a new parameter

and a new link is right underneath the manage parameters heading so go ahead and click on new you get to give your parameter a name so the parameter that we're going to create is going to be a parameter of the Regions that are in the orders query so we're going to just name the parameter regions we're not going to give it a description we're going to go down to type and we're going to select text for suggested values we're going to supply a list of values and you're going to click next to Row 1 and

type East when you click right underneath East it gives you Row 2 you're going to type Central for the next row you're going to type South and the following and the last one we're going to type West so after you type West click on the next line even though we're not going to add anything there once you click on the next line then the previous entry is accepted so when we go to the default value drop down we're going to default it to East And we're going to use the same for current value and now

we're going to click ok um in your queries pane on the left you'll see the Region's parameter and its default value of East notice the difference in icon for a parameter if you were to click manage parameter it would just open the same dialog box where we created it so if you needed to change anything you could do that What we need to do now is go to the view tab of the ribbon so we can tell power bi to allow parameters to be used in the parameters group you're going to check always allow and

now let's go over to our orders table and use our parameter so when we get to orders we're going to scroll to the right until we see the region field and I'm going to show you how to use the parameter that we just created to Filter that field we're going to do the auto filter drop down next to region and if it says list may be incomplete reminder you want to load more so you see everything that's in that list and we see the regions there we're going to hover over text filters and choose equals

where it says keep rows where region equals in between equals and enter or select the value you'll see an ABC icon That represents text we're going to click on that icon and we're going to choose parameter and notice it puts the name of our parameter regions in there so we told it to default to the east region when we click ok we'll see that it's now filtered for East but what's cool about this is once we publish this to the service we will be able to change it in the Service in order to be able

to change your parameter in a service the data type has to be either text or decimal number so we're good because all of our regions are the text data type what we're going to do now is go back to the Home tab and click our close and apply button to make sure those changes get down to the desktop and when we're done with this we're going to go ahead and click the Last button on the Home tab which is publish we're going to republish this to the service it's going to prompt you to save your

changes and I'm going to put it in my power bi video workspace so I've selected that one it lets you know that we've already published this so it says replacing this data set may impact one report in One dashboard and we're going to click on replace And it lets you know when it's successfully finished publishing it to the service go ahead and click the link to open sample Superstore OD in power bi right from your success pop-up it opens in report view in the service and what we want to do is go up to more

options and edit when we get into the report we're going to do the plus sign for a new page and we're not going to select a Visualization up front we're going to expand the orders table in the fields pane and from the orders table we're going to want to drag let's see what do we want to drag into this we're going to drag region directly onto the canvas when you do that without selecting a visualization it creates a table visualization and we see based on our parameter that it's showing just the east region our default

Region we're also going to drag sales into the framework of the table so now we're seeing the total sales for the east region and we're going to go up to file and save this report once the report has been saved you're going to navigate to your workspace where the report resides so at this point we're in a service and we decide that we want to change our Parameter we want to change it from east to west you can do that in the service and the way you do that is you're going to hover over the

sample Superstore OD data set and go to its more options and when you select that you'll see settings on the list and you're going to click on settings so in settings you have a category called parameters and you're going to go ahead and open it and you can see that it's set to our default of the East Which also showed up on the report that we created this is where it's useful as an efficiency tool we can change this so I'm going to just double click East and I'm going to type West because it's a

text data type we can change it we can only change text and decimal number data types in the service for our parameters once I change it to West I'm going to apply so now it updates and it's set to West I'm going to go back into my workspace and I want to go back to the sample Superstore report the OD one and notice it still says east on the report so what you need to do here is you need to do two refreshes I'm going to go back to my workspace and I'm going to hover

over the sample Superstore OD data set and you'll see the refresh Now button I'm going to Click it it's the circular Arrow and then I'm going to go back to the report and the report is still showing East at this point and now in the upper right hand corner close to where your login information is your profile information you'll see another circular Arrow this one is to refresh the visuals so we just updated the data model by refreshing it By refreshing the data set now we're going to click the refresh button for the visual and

it changes to West so that's how it's an efficiency feature you set it up in power query editor and then you use it in a report in your service after publishing and then you can go to the settings for the data set and change the parameter as long as it's a text or a decimal number data type the type of refresh we did is known as An on-demand refresh we physically click the refresh button for the data set and then for the visualizations let's go back to our workspace and we're going to hover over the

sample Superstore OD data set again and go back to more options and back to settings you also have the ability to schedule refreshes in power bi service so we're going to scroll down underneath parameters we're going to go to scheduled refresh And we're going to toggle it on so you can change the refresh frequency daily or weekly you can say what time you want the refresh to happen and you can send refresh failure notifications to the data set owner or these contacts so some people like to refresh like at noon every day something like that

whatever it is you need to do there is what you can set up so you don't have to Physically come in and do a Refresh on demand when your frequency is set to daily you have the ability to add multiple times so you can have it refresh daily multiple times a day I click the add another time link and I'm going to just do the Xbox the x button rather to get rid of the additional time if you change your refresh frequency to weekly you'll be able to pick the days Of the week that you

want it to refresh and you can have it add refresh multiple times a day by adding another time as well there so instead of doing refreshing on demand a lot of times for a lot of your data sets you're going to want to refresh them you know once or twice a day perhaps depending on how active that data is let's go back to our workspace so I can take you back to something that was Originally covered in module two hover over sample Superstore data set not the OD one just the plain sample Superstore one and

go to it's more options button and go to settings if we go down to scheduled refresh here we cannot schedule a refresh this is the data set that we created from the locally stored Excel file and remember you can't refresh a locally stored file it has to be stored in the cloud that's Why the od1 that represented one drive when we created it in a module two it's a Excel file that's stored in Office 365 Excel online so that one will allow a scheduled refresh this one won't because it's locally stored so just like at

the desktop level you can't really refresh the locally stored data you would have to re-import it into Power bi desktop and then if you make changes in the desktop you would have to republish it to the service that's the only way to Get it refreshed so in this final module you learned about parameters created them and you also learned how to change them in the service you learned how to manage your data sets in terms of looking at how to refresh them and we did a Refresh on demand and you also learned that you can

schedule a refresh for anything but a Locally stored Excel file in the service I have a last challenge exercise for you that you can complete on your own we've spent some time building visualizations in the sales and marketing sample pbix file and for your challenge go ahead and publish this file to the service create another workspace in the service first and then publish the file to your new workspace give it a theme maybe do some quick insights or add q a to it Even a video if you'd like that will be your final challenge thank

you for attending this video course on power bi I'm Trish Connor Cato and as a reminder all of the files and additional documentation are in the video description below for live class es in office ends professional development and private training visit learnit.com 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]