Transcript for:
Power BI Overview and Features

Power bi is the market leader in solving data management crisis. This tool is mainly aimed to help organizations and individuals to visualize and organize their data. Hi everyone I welcome you to this session on Power Bi Full Course which contains everything that you need to know in order to master power bi. Now before we move any further let's take a look at the agenda for today. The first module which is an introduction to power bi will help you understand the importance of BI the different tools that are there and why should go for power bi it also covers the basic fundamentals of Pavia the second module which is power bi desktop here you'll learn how to install and download the tool and you'll also get familiar with the UI of the tool the third module which is power charts will help you understand how to create impactful and comprehensive reports on the power bi desktop the next module which is power bi KPI indicators will help you understand the importance of KPI visualization and how they can benefit an organization in visualizing their growth the next module which is power bi dashboards will help you understand how to create interactive dashboards with the help of lot of examples and use cases followed by this we have a comparison module between power bi and tableau in this module you'll understand the importance features of both of these powerful tools followed by this we have the last module which is power bi interview questions now you'll understand the important concepts of Power BI and how you can ace with power bi interview. We'll also look at a couple of market trends of power bi so guys before we move any further make sure you go ahead and subscribe to Edureka YouTube channel in order to stay updated about the most trending technologies. The concept of business intelligence is something that is alien to very few people these days with newer tools emerging everyday to help solve the crisis of data management most organizations have already moved in or have plans to use business intelligence and solving their crisis and in this module we are going to talk all about power bi power bi is Microsoft's latest bi tool mainly aimed to help everyone analyze and visualize their data so without much ado let's get started now why should you choose power bi before I answer this question I would like to answer something a little more fundamental let's start by addressing the most essential and fundamental question what exactly is business intelligence now in an age where business intelligence has become a bigger domain than most trending technologies if you ask 20 different people what the term means you're likely to get at least 10 different answers so let me try and put it in the simplest of terms without having to lose the technicality of it business intelligence is a set of techniques and tools for the transformation of your raw data meaning your data sets into meaningful and useful information to take important business decisions to put it simply business intelligence as the technology would get the right data to the right people at the right time so that they can take more effective business decisions over the years the process of business intelligence has grown and adapted to help solve almost all the challenges while dealing with data by involving newer tools and techniques the change that business intelligence has seen over years can be divided into three waves so let us continue with our tutorial and take a look at the first wave IT to end-user or the technical wave during the first wave of business intelligence the end user had to be dependent on the IT department for data insights this is because it was not possible for end-users to create visualizations or reports on their own as tools available required some technical or coding knowledge this dependence on the IT department for insights resulted in more efforts and time consumption to get the updates done now the second wave which is analyst to end user or the self-service wave now the second wave gave analysts access to BI now people with some knowledge of analytics could use BI tools this meant more teams had the access to BI and more people could have better data insights this is the role of the IT teams and finally the third wave which meant everyone which means the power lied in the hands of the end-user the third wave made it easier to access data and create reports and visuals to get better business insights the introduction of tools like power bi tableau click view and Spotfire made this transition easy now anybody who had a basic understanding of data could create reports to build intuitive and shareable dashboards this was about the three waves of BI and in the third view came a very important aspect data visualization now data visualization is nothing but the pictorial or graphical representation of information or data it provides insights into complex datasets by communicating the key aspects in a more intuitive and meaningful way data visualization lies at the intersection of design communication and information service even though your data visualization has been termed as the key skill for research in the 21st century it goes way way back it existed in the late 18th century and can be traced back to when William Playfair invented the geometrical charts his bar charts were used to represent Scotland's imports and exports of 17 countries in 1781 these bar charts constituted a pure solution to the problem of discrete quantitative compare now obviously we have grown to learn about more and more charts as the years pass by we also have a few hybrid charts to make our jobs easier and our calculations more granular the way our human brain processes the information is easier to use images charts and graphs to understand and visualize large amounts of complex data then going through tons and tons of spreadsheets on reports take the code and image is worth a thousand words for example this is completely true because as a human mind images aren't just a mere collection of pixels they hold a lot of information this information in visual form is easy to understand than reading the same facts in text or number form so let me give you an example suppose there's a company which deals with a lot of products like Amazon and it's widespread over the world and has many many vendors that sell through this platform so obviously there's a lot of data being generated in a lot of different formats people use Excel access different databases sequel server so on and so forth some people even make all these spreadsheets and upload it to the web so obviously all that has to be bought to a single platform to analyze and then these are the reports that go to the CEOs CFOs and the CXO position all the big people in Amazon now you can't just take 1 million or 10 million rows of data to a person to look at and in for something right you need them to know what's going on what is going on inside the company how is the market outside of the company and this cannot be done with hundreds and thousands of numbers this is where data visualization comes into picture it's about giving them an idea of what's going on inside their company in different departments without having to look at tons and tons of numbers well-designed graphics have the power to put this complicated data into simple pictures and this is where modern bi wins data visualization is a quick and easy way to convey concepts or information in a universal manner it can help to one identify key areas and hidden patterns to get factors that give better customer insights 3 analyze and associate data and products properly and finally make proper predictions and obviously presentation has a big role to play in this so pardon me for my analogy but if this was a beauty contest let's go ahead and look at our winner now let's see why we need power bi now there are a few points that make power bi one of the most prominent tools for data visualization now this tutorial would be incomplete without understanding these points firstly Barbie I can spot trends in real time traditional bi tools like tableau or click View restrict you to historical analysis by using power bi you can access real-time information so you can identify trends early by doing so you can identify issues and improve performance secondly by we I can automatically search hidden insights with power bi you can auto search datasets for hidden insights in seconds with quick incites users can simply ask questions and power bi Q&A will answer their questions with an immediate effect third advanced analytics and custom visualizations with custom visuals power bi allows you to visualize data in almost every way possible as long as you can imagine it you can put it on your dashboard thus you're not limited to something that lies in a box finally bar bi is Enterprise ready with power bi and power bi desktop you can securely connect to your own on-premises data sources with the on-premises data gateway you can connect live to your SQL Server and other data sources it gives you a secure scalable and reliable enterprise grade information technology and these mention reasons make power bi important in context of data visualization so who can use power bi IT professionals developers companies begin small subject matter experts and plain analytics enthusiasts as long as you want to you can all use power bi now let's continue to understand this by knowing what is power bi the power bi as a name has been in the BI market for quite a long time the Microsoft team has worked for a long time to build a big umbrella called power bi and this umbrella is a combination of strong visualization data analysis data sharing aggregating and cloud integration to define it Barbie is a business analytics service provided by Microsoft it provides interactive visualization with self-service business intelligence capabilities where end-users can create reports and dashboards by themselves without having to depend on information technology staff or database administrators it also gives you cloud-based bi services known as power bi services along with a desktop based inference core power bi desktop it offers data warehouse capabilities using data prep data discovery and interactive dashboards in March of 2016 Microsoft released an additional service called power bi embedded on its as your cloud platform which enables the user to analyze data easily and perform various ETL operations and deliver reports with power bi the power bi gate wheels let you connect with access excel SQL Server databases analytic services and many other sources to your dashboard in power bi and reporting portals embed power bi reports and dashboards to give you a unified experience what you see on your screens right now shows power bi is general workflow you have a thousands of data sources which are being connected to power bi desktop which then can be published into the service and gives you an option of connecting your organizational data life through your power bi gateways in the end this can all be accessed through your tablets laptops and cell phones with you your colleagues and everybody involved in your business decision now that you understand what power bi is let's go ahead and look at a few of its benefits and why are we using this firstly it has pre-built dashboards and reports for popular software as a service solutions power bi helps you create powerful visualizations in the form of dashboards and reports and you can do them without any technical knowledge at all all you need to do is have a little bit of analytical sense and you can use this service to your advantage next it has real-time dashboard updates as I had mentioned before power bi works real-time and it can forecast trends in the coming few years as well third secure life connection to your data sources on-premises and in the cloud through the power bi gateway you can establish connections that are secure and your organizational data can be connected to live every time that you want to the best part about this is it is scaleable and very very secure fourth power bi also provides you intuitive data exploration using natural language query you do not have to know the query language to explore your data in power bi just using your everyday English or your natural language data exploration can be made possible fifth integration with familiar Microsoft products to utilize commitment for scale bar bi can integrate with a number of sources and a number of Microsoft products which makes it highly scalable compared to other BI tools and finally immediate deployment bar BIA is known for its quick deployment which makes your job quick as well as easy when you have to take critical business decisions and that was all about power bi in the section ahead we are going to discuss a few components of power bi now power bi has a few components you have power query power pivot power view power map data catalog data management gateway bar bi Q&A and service starting up we have power query now this is a component which can be used to search an Access and transform public and internal data sources it is the Microsoft's data connectivity and data preparation technology which enables business users to seamlessly access data stored in hundreds of data sources and reshape it to fit their needs with an easy to use engaging and no code user experience next we have power pivot now you can use this for data modeling for in-memory analytics it extends a local instance of Microsoft analyst a services tabular that is embedded directly into your workbook it enables you to import millions of rows of data from multiple data sources into a single power bi workbook it helps you create relationships between heterogeneous data create calculated columns and measures using formulas and build pivottables and pivotcharts and further analyze the data then you have Power View which is a data visualization technology that lets you create interactive charts graphs maps and other visuals that bring your data to life now Power View is available in power bi Excel and other analyst services from Microsoft then you have power map which is also another feature in Excel it is for exploring map and time-based data it lets you plot Geographic and temporal data visually analyze that data in 3d and create cinematic tours to share with others next you have power bi services so this is a collection of apps dashboards and reports built to deliver key metrics for your organization these apps are interactive with and helps customers work with their own content then we have power bi Q&A it's basically a feature which helps you ask questions and get immediate answers sometimes the fastest way to get an answer from your data is to ask the question using natural language so you can use Q&A to explore your data using intuitive natural language capabilities and receive answers in the form of charts and graphs next you have the data management gateway so basically what this does is it connects your on-premise servers with your power bi in the cloud if you want to refresh your data in the cloud with the data that is on the premise you will need to have the data management gateway configured and available to your tenant and that is how this works and finally we have our power bi data catalog now this contains the metadata for felicitated search functionality in power bi now your metadata gets stored in power bi data catalog in the cloud for a shared query it gives you a search access list for the query to determine which users and security groups can find and use this shared query now that we've seen how the components work let's continue with this tutorial and understand the architecture of power bi now broadly describing Barbier is architecture has three phases the first two phases partially use ETL to handle data and then you have the presentation of your data so let's take a look at these phases one by one first you have data integration an organization can be required to deal with data that comes in from different sources as I had early explained in my Amazon example now this data comes from different sources and can be in different file formats now the data is first extracted from these sources which can be your different servers or databases so on and so forth from wherever you can pull in data this data is then integrated in a standard format and then stored at a common area called as staging area then we go to our second step data processing now the integrated data is still not ready for visualization because the data needs processing before it can be presented now this data is pre processed or cleaned as we can call it this is also known as transformation of data for example missing values or redundant values are removed from the data set after the data set is cleaned business rules are applied to the data and it has transformed into pre sent table data now this data is then loaded into a data warehouse and now that you have extracted transformed and loaded data your ETL is complete finally you have data presentation so once all this data is loaded and transformed it can be visualized much better with use of various visualizations that power bi has to offer you use reports dashboards and help 1 represent data in a more intuitive manner these visuals reports have business and users take important business decisions based on these insights with that let's move on to the building blocks of power bi where we can talk a little more about these insights now everything you do in power bi can be broken down into the following building blocks a good understanding of these building blocks will help you understand concepts and will let you create detailed and complex reports so the basic building blocks of power bi are the following you have visualizations data sets reports dashboards and targets first up you have a visualization a visual representation in the form of graphs and charts and maps of a data is called visualization for example a chart or graph can be used to represent data visually bar bi gives you different visualization types which keep getting updated with time now some of the commonly used visualizations are map representation card visualization stacked area chart 3 map and pie chart now these visualizations can be simple or complex however visualizations aim at presenting data in such a way that it gives you more insight in the context which is otherwise difficult to discern from simple data files next we have data sets now we know that a data set is nothing but a collection of data or information in the form of spreadsheets now power bi can harness this data to create visualizations it can be a simple data set or a combination of many different sources which can be filtered and combined to provide a different dataset or together for example you can pull together data from many different sources like a different database fields an Excel table and online results of some email campaign to create your data set having said that you may want to filter your data before you bring it in to power bi filtering lets you focus on the data that actually matters with your data set ready you are now free to create visualizations and display different portions of the data that's set in different ways and with this you gain insights next you have reports now a collection of visualizations that appear together on one or more pages is a report in power bi in a collection of items these reports combined to form a workbook and are all related to each other you can create visualizations on multiple different pages if necessary and arrange them in a way that best suits your interest what you see on your screen is the image of a sample report next you have dashboards now power bi dashboard is a single page interface it is often called a canvas that uses visualizations to tell a story now a lot of you might be confused within the difference between a report and a dashboard now this is because it is limited to one page a well-designed dashboard contains only the most important elements of your story or your report the visualizations you see on your dashboard are called tiles and are pinned to the dashboard from the reports so in a way you can see that your dashboard is a compressed version of large reports that you are going to present now because this is limited to just one page a well-designed dashboard contains only the most important elements of that story the visualizations you see on the dashboard are called tiles and are pinned to the dashboard from reports in power bi a tile is a single visualization found in your report or on a dashboard it's the rectangular box that contains each individual visual now power bi gives you the freedom to move or arrange tiles so you can present the data the way you want to even while you're creating a report or dashboard you can make the tiles bigger change their height or width and snug them up to other tiles any way you want so this was all about power bi is building blocks now I'm going to take this far bi tutorial a step further with a demonstration of creating a simple report using power bi Microsoft power bi is a suite of business analytics tools that helps you create and share actionable intuitive reports for business insights and now I'm going to show you how you can put your data to work with power bi we'll go through the basics of data visualizations and dashboards and we'll go through how to create and modify data visualizations we'll also look at how to join data from multiple sources and build a dashboard report to share with our colleagues so what we're going to cover today in part one will be getting started using it part two we'll talk about joining data from multiple sources and part three we'll talk about building and sharing a dashboard and at the end of this we'll have a demo so part one getting started in this section we'll learn how to install the application will talk about importing data from Excel to power bi will create and modify simple visualization and we will save our report and publish to power bi service now let's talk about installing the laptop application first what you want to do is go to HTTP colon slash slash app dot RB I dot-com here you'll sign in with your credentials you'll run a simple wizard to install the application then you look for the download icon it is here it's an arrow pointing down with a line underneath it so run that wizard and the power bi will be ready to launch and it will automatically launch the first time that you run it when you run it you'll get this Start screen it is black and yellow and you'll have access to forums the power bi blog various tutorials as well as some videos that you can watch and learn but you don't need any of that videos because you have this one video so let's go ahead can also access the get data functionality here from this screen if you decide that you never want to see it again you'll notice a check box in the lower yellow part of the screen that says show this page on startup simply uncheck that box and you won't ever see it again when you run the application so next up you'll want to install some data after you've installed the application you're going to go to the get Data button and you'll be able to pull in data depending on what you're using you may be using data from Excel like I'll be doing further in this demo or you may be using data from an example a sequel server database or an Access database the options you have for pulling in data are a plethora so there's a lot of available datasets and we'll be sticking with the excel for my demo but keep in mind that you can pull data from a lot of different places so after you have selected what data you're going to be using you'll be given the option then the navigation window to select the exact data set what you want to pull you'll checkmark a box for example right here we have this box checked and then you'll have the option to load the data or edit the data in the query editor you'll probably want to edit the data in the query editor just to make sure that you'll pull in exactly what you want once you have pulled in the data that you want you'll see that the data up here as a fields list on the right-hand side of the application as you can see here in the sample data we have a budget-- business team delivery day but whatever you've pulled in will appear in the fields list and you'll be able to use that data in the fields list to create your visualizations so let's start here by creating a simple visualization this is a very simple visual it's simply a column chart with one number so it's one column right here it's the budget field that we pulled in drag whatever field that you want from your data there is no strict rule to it and make a visualization you'll end up with a column chart like this you can also see that there are a variety of charts available there and then in the visualization box you will be able to make modifications to it and like I said this right here is a bar chart but you'll be able to make pie charts column charts line charts and a variety of other visualizations in power bi a variety of modifications are available for visualizations too right here we've highlighted where you can click on the lower right hand corner of the visualization and drag it up to make the visualization bigger or smaller for example you might want to make it smaller because maybe you want to put more than one visualization on the canvas there or you may be wanting to make it large so that it can take up the entire canvas so you can drag around the corners of your visualization to make it the size that you want the format that power bi saves reports says dot p bi X file this is not the best way to share a report but it's definitely the best way to save your work if you are in the middle of something you can go ahead and save your report as a dot P bi X on your machine or your onedrive maybe even your onedrive for business or a sharepoint online set wherever you're saving it and so that way you'll be able to pick up later if you're in the middle of building a power bi Report now if you want to share your results of someone the best way to do that is to publish the power bi service and you'll be able to do that using the publish button that is the ribbon and that button is on the Home tab on the furthest right we have highlighted it on the slide there and then when the publishing is complete you'll be given a link that you can click on in order to go see your workbook or your report this will be there on your power bi website and you'll probably want to do that just to make sure it looks as good as you wanted it to in your tool so this is the power bi dot-com interface the power bi dot-com interface gets changed from time to time that is also the reason for this particular tutorial because it got updated in 2019 recently it is a cloud service and when new features come you may see things in a slightly different place for example the search bar here I believe is slightly different now but the gist of it is what we want and you'll be able to use that search bar once you've published a lot of reports and have a lot of datasets available on your workspace you'll be able to find them easy using the search bar or you can use the functionality of defined your recent reports as well as when you will be able to manipulate and share them using power bi dot-com interface we are now going to go into a part two which is using multiple datasets so now we are going to be using multiple datasets and joining together in this section we will talk about adding data from other sources joining the data from multiple sources creating more interactive visualizations as well as updating that publish data on the power bi dot-com web service so here we go with getting additional data now to get additional data you do the same thing that you did before you got your first data set and it's really not very different from how you did it you just are having some data you're working with you'll go to the get Data button you'll push it and our demo will be using Excel but remember you can pull in data from a variety of sources it is also good to keep in mind that they don't all have to be the same source you could be using some data from an Excel spreadsheet some data from example a sequel service query or from another database query you can pull them all together in power bi create relationships and manipulate that data and create some great visualizations right here just as you did before you'll select the data that you want to load and you'll probably want to edit your query before loading it just like you did before so whenever you're loading queries you want to make sure that you have the data that you want and your data is in the form that you want it in before you pull it in power bi will load the data that you have selected and there may be relationships in that data for example here we have some actuals and some budget information that we are loading in sample data but you know you may have two different tables that you are pulling in and they both have a month column for example in power bi the tool is actually smart enough to detect the relationship and join the data together if it did not auto detect your relationship you'll be able to manually create it but for most times in my experience it does it on its own so I want to introduce here the modeling tab it's right next to the Home tab and with the modeling tab what you can do is change some things with the format of your data you might want to for example sort in a different way that then power bi has already sorted your data and you might want to change the format you know if there's a number you want to change to a currency or maybe you've accidentally formatted some numbers as text and then it's important that you notice that you can't do any math because of course you can't do math with text and your problem then is that it's formatted as text you can easily go into the modeling tab and change the format of the data over to the number format so now you have pulled in data from multiple sources and you want to create some new visualizations while keep in mind that the canvas view here that's what we've highlighted on the slide that is the top of the three icons you see there on the top you've got the canvas view below that is the modeling tab that we just talked about and then there's also the relationship view and we'll be talking about this as well and in the demo you'll be able to create visualizations using all of the sources of your data and whatever you are using you'll be able to create visualizations with all of your data combined and that's one of the really powerful aspects of power bi as you create visualizations with your multiple data sources you'll be able to modify those like you did before you can use the handlebars on the visualizations to drag and drop the corners around make the larger smaller fit how you want to also keep in mind that you can have more than one canvas page you certainly don't need to cram all of your visualizations onto one page if you've used Excel before it's kind of like making a new sheet you'll see at the bottom of the page and there's a little plus sign and you'll be able to create a second a third a fourth canvas page and add more visualizations into the same report using multiple canvas pages and made a couple of visualizations with power bi and right here what I want is to highlight the fact that you can change the colors on your report to highlight certain things for example right here we've added some black to the columns and power bi actually gives you really strong granular control over you know what your charts look like for example in a pie chart you'd even have the granular control to change the color of one slice of the pie so you really have a strong control on how your visualizations look right here now that you're done pulling in data from multiple sources you're probably ready to publish now whether you're publishing just for the first time or publishing again it's just as easy you simply go to the publish button that's on the Home tab now if this is your first time publishing simply just publish it and then click on the link to go take a look at it if it's your second time publishing you'll be either given a choice to say overwrite a previous publication or you're going to rename it and publish it again as something else you'll get this Vizard opening up giving you the option to go to your published report you'll go into power bi dot-coms interface and view it with that we are moving into part three now this is the creation and sharing of dashboards in the section we'll talk about creating a dashboard and we'll talk about pinning visualizations to that dashboard we'll talk about modifying the dashboard and we will talk about sharing that dashboard with your colleagues or customers or business partners so you obviously might be asking a logical question what exactly is it well most of you probably kind of no water dashboard is but what is it exactly at this point it might be useful to make a distinguishing characteristic between data sources reports and dashboards now we all know what our data sources are those are our Excel spreadsheets sequel server queries or other data based queries when we've pulled in numbers and text than other types of data then we built a report on top of it using power bi now once we have created visualizations on that data a dashboard is a really important aspect it really is just a type of report and what it is is that visualizations from other power bi reports are all paned to one specific place that can be updated in real time so that your business partners and your business decision-makers your customers and your colleagues and look at the dashboard and instantly have the information they need to make those important business decisions basically if you bring all your power bi reports regarding that particular decision and put them into one canvas that is when you have a dashboard it is a compact form of your complete report so now how do you create a dashboard the easiest way to create a dashboard is to simply click on the pin icon which you'll see on your visualization and will then give you the option to create a dashboard you'll also see as we show you on the screen here there's a plus sign next to the word dashboards in the power bi interface you'll create a new dashboard there too once you've created that dashboard you can continue to pin visualizations to it here we go there's that pin icon there and you will be able to click that and attach visualizations to your dashboard and once you've done that you'll be able to move them around and make it look like how you want it to look like eventually of course you'll have to share the dashboard with whoever needs to see it the pin icon will ask you which dashboard you want to pin it to do you want to pin it to an exist - bored or a new dashboard if you need a new one go ahead and select that but you know you're building one dashboard at a time and you're probably going to be sticking with your existing dashboard for a few visualizations at least you can view your new dashboard with your visualizations of course and you want to look at it before you share it just to make sure that it looks how you wanted to go ahead and click and share and send it to some of your colleagues you can modify your dashboard any time and you'll be able to click on the corners of your visualizations to make them larger or smaller just like you would want it now you won't have quite the degree of freedom you did with the canvas though there are certain size settings for these visualizations they can be so big but you can't choose exactly how big you want them like you could in the canvas when you're in the web view the predefined sizes is what they are called when you're finally ready to share your dashboard go ahead and click the sharing icon so you'll type in the names or email addresses of the people you want to share it with if you're dealing with a circumstance where you've got an internal and you know the global address list you'd be able to simply type names from that or if you're sharing externally you may need to use email addresses and you'll type those in and go ahead and click Share you notice you have some options here and you can allow the recipients also to share your dashboard and generally you do want to send an email notification when you share something with them so then they'll know that it's shared with them and they'll be able to go access it there's an option you can check as well you can also share your dashboard once again by simply copying and pasting the URL once you've clicked that share button and added the person's email address or their name and share it with them even if they lose the link or they forget about it they will never have to go through that process again you'll actually just be able to copy that URL and paste it and they can view your dashboard apart from that there's also a QR code generated for every report for the very same purpose and the first thing we need to do is that you need to install power beer for that you need to go to power beers official website that is Bobby I got microsoft.com I need you directly download the option here let me show you how it's done so here you have the option of power bi desktop so first what we need to do is that we need to create our reports then we want to create a final dashboard which has all these inside so what we'll do is we'll be creating a report for each of the insights in the power bi desktop so here just click on download option and it will automatically initiate the dog so once you've downloaded this file okay then you need to install it it's very easy installation step so this is what your power bi would look like when you have been launched so one thing I would like recommend is that you sign it if you not created a sign it then definitely it make sure that you create a sign-in off of your desktop so once you've successfully logged and you get this notification here of your username as well so let me just give you a simple overview with respect to how power beer works and then we start with respect to our session now first you have a simple workspace that is the first work space that you're seeing is the report workspace this is the workspace where you will be creating the different visuals as well as creating the different reports as well then you have the data workspace so when you are loading a data any data that you're working with can be viewed here all the modifications that you want to perform with respect to the data can be done here and finally you have the relational workspace now the relational workspace is one of the useful workspace when you're working with multiple tables now this helps you establish as well as plan is the different relationships between the multiple tables as per today I shall be discussing all the charts you need to build effective reports on the power bi desktop this session will take you through the various power bi desktop charts and most importantly when is it more appropriate to use them we'll be going in order with which these charts are present in the desktop app so without any further ado let's get started so first of all this is my data set I've already imported the objects into my model I think I should explain where this data set comes from because this looks pretty morbid with all the bombs and weapons I assure you this was no real-life data so I hope most of you have heard of if not played the game of Counter Strike geo for those who haven't it's one of those first-person shooter games where you go around killing a bunch of your friends it's great some good clean fun right but the best part about it is that all the data you've generated on the game is available through an API so I've acquired these data sets using various gamer tags and it has an entire history of how many minutes have played what weapons I've been using what maps have been playing one how many have killed how often I've been shot all this great data and I thought it'd make for a great demo so let's start with the charts so first of all I'll be creating a basic bar graph or a column graph with this for that you can use any of these given stacked bar charts column charts any of these which wouldn't really matter because we're just using one case this is basically because I want to show you guys what you can do and how you can transform these charts into its most effective form so let's take a column shot now I'll be using this other data set which I also got from the CSG oh it's a player's data set which has a twenty players count The Kills how many times they've been shot the latitude and longitude from where they've been playing etcetera etcetera so it's pretty simple actually all you have to do is drag a column and drop it here on the field what you can also do is you can drag the same column tab and drop it right into your graph there you have it now there are a bunch of interesting things you can do with it for example I would like to change the color saturation according to the number of absolute kills so green being good and red being bad as you can see this player number 20 despite having the best KD ratio does not necessarily have the best absolute number of kills so you can do a lot of cool stuff like this with the power bi let's move on to our basic stacked and clustered shots now this entire column gives you bar and column charts now they are of two types mainly one is the stacked chart and one is the clustered chart I'll be showing you the difference between the both now first of all I'm taking the stacked chart here I'm taking a bad shot as you can see it's horizontal instead of vertical on the axis I will be taking whether the bomb is planted or not it's a common axis so it's basically in a true-and-false situation in the legend I'll be taking the weapon type so the legend is where you can specify and a lot of color to each category and the value I'll be obviously putting down a count and there you have it as you can see the rifle has been used the most immediately followed by the pistol now if I had to represent the same data using a clustered chart this is what it'll look like I'll use a clustered column chart here and I'll drag and drop the same data which I did for the previous chart so basically you use both these charts when we compare different cases depending on the same two parameters the stat shots are where you compare things as parts of a whole but a clustered chart is where you do the same thing but in separate bars so with that let's move on next we have our line and area charts so these are the charts which usually show growth but you can also use an area chart to show volume in some cases here I'll be finding out the tick rate by plotting tick against the second on the axis we'll take the second stab and in the values we'll take the count of tick so for those who have a doubt tick rate is basically the number of times your game refreshes in a second for people who do competitive gaming a good tick rate would be 128 as we can see if we calculate the slope of this graph it's one twenty eight point four eight right here we can plug the same thing using an area graph as you can see the graph looks similar but it gives us an idea of the area shaded under it it gives us an idea of the volume so with that let's move on to our next chart so here I'll be using a combination shot as the name suggests it's a combination between the bar chart and the line chart and you can use it the same way as we did the previous charts so on the share axis I'll be pulling down the players on the column values I'll be putting the KD ratio or let's just put the absolute number of kills and the line values I'll be putting the KD ratio as you can see it's pretty similar to what we had inferred in our column chart another interesting chart here is the ribbon shot which is like an area chart but it shows data with respect to the maximum measure so let's try that out as well now on this I'll be plotting let's say the weapons used in the number of rounds so let's bring the round to the common axis will categorize the colors according to the weapon type and then we'll bring the count of weapons to the values see how intuitive this is because when I just dragged the weapons value to the value field but the count it's selected on its own so there we have it we have the combination chart as well as the ribbon chart as you can see as we had inferred before the rifle has been used most and by just touching on each of these colors or any part of the graph you can find out the absolute information regarding the bar next up we have another one of our very common shots you've probably all seen this one before it's a pie chart it's a big circle cut into pieces can't really miss it a donut chart is essentially the same thing except for that it has a smaller circle cut out in the middle turning the filled pie into a hollow donut it's a visual preference mainly but there is a key difference between both of them let's start with the pie chart in the legend I will be putting the weapon and in the values also I'll be putting the count of weapon I'll be doing the same thing for a donut chart as well closer to make it look better so now go ahead and look at the pie chart notice how you look at it chances are your eyes go straight to the center at least at first you view the pie chart in its entirety because pie charts are filled to the center and here's a donut Chuck because donut charts are hollowed out there is no central point to attract your attention so when your eyes go instead if you like most people your eyes travel around the circumference of this donut chart you judge each piece according to its length as a result you can also think of a donut chart as being a stacked bar graph which has been curled around itself so essentially we use donut charts for its readability and the pie charts for percentage breakdowns so next we have the tree maps which serve the same purpose but according to the hierarchy let's plot the same thing let's just plot the weapon type there we go with that let's move on to the maps on bi now we can be using a number of maps here the fill maps here are where you can show data density on certain states but we'll be using the regular map because we don't honestly have so much data so we'll be plotting where the players come from data tier at latitude longitude longitude for that you'll have to categorize the latitude and the longitude as latitude and longitude in the data view so we can also do a bunch of other things with it like we can change the size according to the count we can change the size of the bubbles as you can see wherever there's a concentration of more players you can see the bubble is larger you can also change the color saturation let's change the color saturation according to the absolute k/d ratio then you can see green being good red being bad again you can do something really really similar with another tool here which is the ArcGIS map the latitude the launch dude sighs we'll be according to the count and the color could be according to let's say absolute kilts there we go you can also change the colors if you like if you go to this formatting tab over here the background border lock aspect a bunch of different things another thing worth noting is that here msbi this uses bings map engine so it's very precise that's also why it takes some time to plot next we have funnel charts so basically this shows stages and progress this really cool change the color saturation while I'm here let me also explain the slicer to you people basically a slicer slices the data according to how you need it according to a certain field suppose and slicing data according to the absolute number of kills here you can actually control the data visualization from both the sides you can see the absolute number of kills in each bar between the sixty-first and the hundred and fourth kill so that's basically how a slicer works you can use it on maps your pie charts you can basically use it on any other chart that you want to so now with the visualizations we've used so far these have been visualizations which are used to compare values across different fields but to create power bi reports sometimes you only want to show a single metric just so you can track as it changes over time so here are a few different visuals that do it so gauges are great if you want to show progress towards a particular target like so by default you can always see double the amount of the amount shown here but you can obviously go change it here you can go you can change the data labels you can change the gauge axis you can change the call-out value the lock aspect a bunch of different things you can also add other fillies here like minimum maximum or the target so that's one thing another thing we can use is the card this one here you can also use a multi-level card bird this is a single row card so this is the card would just shows the numeric representation as text by default we use units to trim down the number but we can also use the formatting tab to change how it shows the number so you can do a bunch of really smart things with it like you can use the measure and ask msbi to return a string moving on so all these numbers lend themselves to showing KPIs where you got a particular value and a target you're working towards the great thing about this KPI is that it shows you an indicator and a number as well as a trend over a period over time you can control your goals right here again back to the formatting tab there is the goals bar here there's the goal in distance you can control the trend axis you can change the indicator and how it displays the units and so on and so forth now along with these charts power bi also has some tabular visualizations to look at your data example I bring the table over here and I'll start adding fields to this see you can just go on adding tables that you want to you can just go on adding as many fields as you want and we'll keep giving you a total similarly you have the matricis here now I'll be creating a very simple 2 by 2 matrix say the rows could be the weapons and the columns could be the weapon types and the values could be you can't a thing to notice here is when you add another field you do not get repeated values hence you get the absolute total from both sides with that we've got just one last visual left I would only like to address this one as it deserves a session of its own now if you're into data science you might be familiar with something called the R this is a really common application used to do deep analytics and statistics it is also a great visualization platform so msbi allows us to integrate with R so it basically means you can get your power bi file over to our get visuals to run and bring it back to the desktop and use it like any other chart this session will be answering all your questions you have regarding KPIs and the power bi desktop so before we begin let's take a quick look at the outline of this tutorial so today we shall be discussing one what is KPI next when to use KPI third what do you require for KPI and finally how to use the KPI visualizations in the power bi desktop so without much ado let's get started so a lot of you might wonder what is KP a so KPI or a key performance indicator is a visual cue that communicates with the amount of progress you've made towards a certain goal it basically demonstrates how effectively a company is achieving key business objectives so organizations use this KPI at multiple levels to evaluate their success on reaching targets both internally and externally so high level KPI may be one switch focus on the overall performance of the enterprise while low level KPIs may focus on internal things like employees in departments such as sales marketing etcetera etcetera so this is a really important question when to use a KPI so KPI is mainly answer to questions II what am I ahead or behind on this specifically refers to a number which is your target and secondly how far ahead or behind am I so this represents a trend which is related to the target since a KPI is based on a specific measure it is designed to help you evaluate a current value and the status of the metric so therefore when we ask would you require for a KPI it basically requires a base measure that evaluates to a value and a target measure it also requires a threshold or a goal which the target is set against so currently a KPI data set in power bi needs to contain goal values for a KPI so if your data set does not contain one don't worry you can create goals by adding an excel sheet with goals to your data model or in a PPI X file so this is the next segment I'm sure most of you were waiting for this till now so how would you use your KPI visualization so for that we need to open our power bi desktop so we'll be creating a KPI that measures the progress we've made towards a certain goal a lot of the people will directly start with the KPI but I personally find it more comfortable to start with a column graph and then change it into a KPI so before we start let's import some data here I have an excel sheet with KPI appropriate data so this is what the preview of my data looks like we've got an actual sales column and a target sales column month wise here we've got the Jan to December month numbered accordingly and here we have the fiscal month for those who don't know fiscal month is basically months arranged according to the financial year of a country here it is April to March hence I've started with one being April and twelve being March so let's get back to our charts so as I said I'm going to start with a column chart here we are just gonna drag and drop values like I'm just going to take the month and drop it into the graph and then take the actual drop it into the graph here we have a graph now the thing is power bi desktop is actually smart enough that it knows what column to take as what parameter so now we are going to change it into a KPI now this is my KPI icon we're gonna be using this let's select the KPI icon and there we have it now to turn it into an actual KPI we must have a target so let's take the target sales and put it in the target goals field so this is what a KPI is mainly supposed to show here this is a number that I'm ahead or behind on and this is the trend now looking at it this way you might not see a problem but I assure you there is a problem with this for that I'll have to use the table using the table is as easy as using any other visualization here we just take the month drop it the actual sales and the target sales per month I'll be going to the formatting begin here I'll just increase the size by a little bit we go to the grid and we increase the text size so you can see it properly then as you can see the months are ordered alphabetically so this has to be changed so what I'm going to do is I'm going to select the month tab here go up to modeling and here you can see an option which is sort by column here you can either change this to fiscal man or month number I'm simply going to choose the month number here then see how our target has changed optionally you can also format this KPI by choosing this paint roller looking icon right here which is the formatting pains I can now here we have the indicator which controls the indicator display units and the decimal places next we have the trend axis when it is set on the trend axis as displayed in the background of the KPI next we have the goals when set on the visual displays the goal and the distance from the goal next we have color coding suppose your company follows a certain color palette this is where you can change the colors to match your color palette here you can also choose the direction of your graph suppose high is good or low is good for example if it is something like earning versus wait time typically a higher value for the earnings is better suppose it is something like a defaulters graph then essentially a lower value is better so you can change the color settings accordingly your good colors green bad colors red neutral colors yellow so let's get back to our graph now that you know how this tool basically works you can do some really smart things with it like you can create a measure in the model to return a string so I'll be calling this progress you now you can see the progress column is added here what I will do is I'll be taking a card and I'll be adding the progress to its field here so it's basically going to return the progress this year so this is a life tile and it will keep changing our data set you can also do a bunch of other interesting things for example instead of a single KPI you can use a multi KPI for that all you must do is go to the Home tab here and on the ribbon you can see something called from marketplace click on it search for something called power KPI okay you can see the icon appear here with other icons on your visualization pane you can use it like you use any other visualization here so you can just drag and drop different values on your values your axis has to be a certain date does not matter what date but it can be a period or a month but it has to have a date then in your values feel you can just drop actual and target there you go this also you can format he using this this also you can format using your formatting pane like you can go and change your layout you can change the title suppose now it says actual and target by month you can just rename it to KPI there are a bunch of other options you can play around with let's move on to something else another thing you can do is you can actually get a custom KPI from the marketplace again you can just go to the KPI option right at the bottom somewhere you will have something called the KPI indicator it will take some time to load you can also use this like any other visualization on your visualization pane like so you can use it like you use any other KPI tool here as you can see these has vibrant colors and it shows the graph in a really nice way with these dots and indicators what you can also do is you can go to this formatting pane and you can change the graph you can go here to KPI general and choose a chart type right now we have a line chart then we have a line no marker where those indicators are gone next we can use a bar chart as well you can choose a banding type where the increasing value is better decreasing value is better or the closer is better like the increasing and decreasing I had explained earlier in the KPI chart the closer is better option can be used where you are testing medicines and chemicals here right now we have increasing is better you can change the banding percentage you can change the colors like you did in the previous charts let me get a slicer here now I'll go to the formatting pane and in the selection for central I'm gonna turn off the single select so I can choose multiple things on the slicer as you can see you can use it like any of the charts on your power bi all three of them are interacting the same way with that I think we've covered the KPI now there's always the ambiguity that you get so let me tear it off a dashwood in power bi basically is a single page wherein you have all your visualizations with respect to a specific requirement preceptive now this dashboard could be with respect to different domains now let's say if it's a HR - then what would happen is you would have the details of the employees you'd have the ratio of the employees you'd have number of employees that have come in today you would see the number of requests that have come to the HR and so forth okay now here what you're seeing basically is a marketing dashboard now it basically is a company based marketing dashboard by juicing the different opportunities opportunity count the revenue and so forth so to be very short attachable is just one single page of visualization which tells you a story now this story basically is to meet the end users requirement so it's up to the user who's creating this - book to customize the story as per their choice and make a happy ending surfer now if there is always the ambiguity as to how a dashboard is different from a report so let's talk about that next now as I had mentioned earlier a dashboard basically is just one page canvas here which tells you the complete story that you want to know however a report can contain more than one single page what happens here is the report is very more detailed to be more incited and we most specific with respect to the requirements so let's say if you want to have a complete analysis then you would go for a report rather than a dashboard now here again when you talk about the data sources that a dashboard can take since a dashboard is a combination of different visuals this basically can come from different types of reports thereby you can also integrate your data from different sources as well but when we are talking about a loop over here you mainly working with one single data set that are specifically used for that report purpose so let's say you were to create a marketing then you're going to take just the marketing data but let's say when you're looking at organizational data then are you going to have data from the marketing you can have marketing data from the sales finance and so forth then you have the option of pinning now what do I mean by pinning basically it means that you're adding or copying a specific visual now in terms of a dashboard you can pin an existing term from a current dashboard to any Nashville that is if it's already present in the current dashboard then you can put it to any other - how in case of a report you have a broader opportunity where you can pin it to any dashboard as such if you want you can pin the entire page of the report to your dashboard as well are you guys clearly huh good now apart from that let's see if you want to filter or modify your data to get better insights then what happens is this cannot be done with respect to a dashboard in your dashboard the data is already fed so filtering and slicing it there would not be possible now when you're working with a report here you can basically filter your data you can highlight it you can slice it as per your requirements you can join your data you can separate them you could group them all this can be done another report level how when it comes on a dashboard level it basically cannot beat up so it achieved this slicing of filtering on the report level definitely will be applicable on - but however it cannot be directly implemented on the - then you have the option of alerts that is when a certain condition is met then you can get an alert from that this could be on your file application that is the power of your mobile application you could get a notification or let's say this could come to you as an email so this again can be set on the dashboard itself so let's say you're comparing multiple parameters let's say at one given point your sales and profit values has changed it has come down below a certain value then what happens is as an organizational person you need to take the responsible measures so this can be very useful when you're monitoring your data on a real-time basis as well and in cases when you are working with multiple dashboards to differentiate with respect to different dashboards you can set them as feature dashboard now feature dashboards basically are those - modes which are highlighted from the rest however in case of the report it cannot be set as such and then going on from there you can also use natural language of what exactly sinister language wearing will be looking at and later part of the session but to give you a simple idea or an understanding here what it basically refers to is that you can directly feed your queries in English itself you don't need to write them assess SQL statement or anything like that so let's say you want to identify which is my top-selling store then definitely you can just write that and your power bi dashboard will convert it and give you the output and form of for bhishma now don't worry we'll be seeing that as part of our demonstration as well later on so I hope you guys are quite interested now apart from that on a dashboard level you cannot change the visual representation of a certain form of pressure that is let's say if you're using a line chart to denote something then as per your requirement you cannot change it in a dash book so you need to go back to your report then you need to modify it there and then you need to pin this updated version so what happens is it gets updated in the dashboard asthma but when you directly modify it in the report it does not get reflected so you need to repin this updated which will as well now when you come down to the customization capability of both a dashboard basically uses visualizations in form of a read-only method here so any changes that you make are basically with respect to the changing in names you can either link them you can resize them rearrange them and so forth in the dashboard however in our report here you have complete right with respect to the visualization as well as the data that you're working so you can modify it change it as per your requirements it's completely left to you as to how you can work around with it so I you guys clear with respect to both how the report is different from a dashboard okay I seem to have a question here do we meet create reports for - but it's definitely you - because it's from the reports that you would be going on to create a dashboard so to put it more simply it reports give you a complete idea whereas a dashboard will give you a complete over so based on your requirements you can use which were meteor necessary so moving forward let's look at the data set that we'll be working with today so we're going to work with a superstore data set of a United States Organization which was collected basically from 2011 to 2016 okay now I'll be showing you the data serozha just a minute so this is your complete data set you have basically the date of order you have shipping date the more the customer ID customer name which segment it was from postal code city state country apart from that your region market the product ID the category subcategory and so forth okay so I hope you've got a general idea of what the data set looks like okay that's great so what we need to do is we need to mainly achieve for insights from these data first what we need to identify is the overall before that is we need to identify the sales and profit of our complete superstore okay apart from that we'll also try to identify the performance in different regions and we'll see which are profitable regions and which are non profitable regions then what we'll do is we try to identify the performance of each state as a whole okay now which state is going to show you the opportunity of growing which is basically pulling your market down and which is something that you can look forward to in investment or marketing as well okay after that you have inside three where we'll be seeing the province of different segments involved in the superstore and how do they contribute as per find what we're doing is we'll try to understand the revenue generated by each category of product now there are different categories of the product so we will see which category is hampering our growth and which is feeding our growth as well okay I guess clear and here the first thing we need to do is that we need to input the data now for that you have this option here gotta get data you can get data and you have the most common data types here you have external power bi service SQL Server analytical service so forth seeing the company it list click on more and here you have the complete list of different data sources that you can connect with respect to a power bi desktop now we're gonna work with an excel file so let me just keep an excel and it's going to ask me the location root means go down P over superstore and it's establishing a connection to the data set okay so since my data set has multiple pages it shows me the different pages as per you have orders you have people and you have written once you are on a sheet it extracts about 200 rows and gives you an overview with respect to what is present in your data set again this basically helps you verify what data set you're working with and visitor value data set or not so since we need we orders detail let me see honors and then click on load so now you can see the successfully the data has been loaded because you can find the options of fields here okay so this basically is because respect to the orders table so let's say if you were working with multiple tables each one of the tables as well as all the columns present in those tables would be highlighted here okay so this is where we'll be working on right now to get a more insight with respect to your data you can go to the data workspace and here you have the complete preview of your data so this is your complete leaders have present here and you get to see whatever data is present and if you wish to perform any operation on this so let's see if you want to modify this data then you can't hit it gray nice option here and you get the query editor so this becomes the workspace where you can perform all your different operations on your actual data set now my default obviou does perform certain amount of operations on your data while it's loading itself so these steps can be viewed here okay so any operation that you don't want probably a to can we remove you on let's say if you are performing any operation then that also can be removed from this option here so every step that you perform gets added to this applied step option so far now let me just close this and let's go back to our workspace so first what we'll be doing will be trying to create a sample Vishwas okay so this basically will give you a feel of how power works so what we'll be doing is let me call a profit leaders here so to rename any movie report you can just double click there and let's call it profit leaders okay now what I'll be doing is I'm going to create a combination of my different states as well as the profits that each of them have but so let me just bring in the state's okay so this basically is going to create a default visualization okay and since its States what it's doing is trying to put it on a map Pushpa okay so for that what I will do is I will add in the profit details as well okay now when I add the details of profit what happens is it sees the difference with respect to the size so let me just filter a little more I'll add in details with respect to the country as per okay so this basically will filter out all the data so let me just bring this up and here you can see the profit across different states that I am making in different countries as well okay so this basically is a geographical representation of all the profit that I am making across the bill okay now let me show you how to filter out the data as well okay now when you come down in your fields option you have this option called filters now in filters if you click on country okay you can see all the countries present here now if I just select one specific country so let's say let me take United States okay then what happens is my complete visualization changes with respect to United States alone okay so here basically it becomes with respect to United States okay so all the profits that you are seeing right now is with respect to the different states in United States okay so if you just go down over over them you can see the different profits and you can see some states are negative okay so what happens is I need to identify which are my positive states and which are my negative States so what will do is will try to change the colors with respect to which they are represent so for that to go to the next field that is the forward field okay now here you have the option of data Palace now the same size with respect to the profit scene can also be represented in a different map based system also that's called field map system where and what actually happens is that the color intensity of each of the states this with respect to the value of profit that they make now let's say if you want to vary 8 the value to identify the regions which make profit will format up here under data colors option you have something known as divergence when you turn on divergence what basically happens is there is a variation with respect to that color so let me just change this color set up as a whole because it seems a bit confusing right now so I make the minimum a slight creep okay I'll make the middle as the medium grid and finally the maximum pass decree okay so this basically helps me get a better idea with respect to this as well now let's see if you want to go more detail let's say the minimum or let's say the center okay I'll set it as a zero forget that means states which are not giving me negative profits are sent up okay States below that should get let's say I give them a blue color okay Center let me give a different color let me see yellow and the maximum profit making states are angry okay so here it starts from blue okay in menu look at Texas it has a complete negative value okay these are my mid-level profit making state but whereas California is giving me one of the highest profit similar to New York as well okay so you can see the variation with respect to the color and how we have not related with respect to that so I hope you guys have got a simple feel of how to create a visual with respect to power okay I have another question here it's how do you know which wish were to work with okay so by default you get multiple date kinds of wish was present in public self and there are more custom visuals as per so it is completely left in you as a representative as to work a form of wishful that you want to represent it certain data that may be represented in a pie chart would be very helpful but when I bring it back to a bar chart or line chart it will not give me a clear-cut picture so it's finally in your hands to decide which kind of wish words to use idea okay so let's go back to our presentation and start with the first insight now the first thing we need to identify is the overall trend with respect to our sales and profit as well as get an insight with respect to the different regions and identify the regions which are profitable and nonprofit so for this let me go back to our area and let's begin by creating our first inside so let me begin by creating a new page for this report so click on this option plus here to create a new page okay and let me call it overall performance so now click and then we said now what we need to do is that we need to identify the seals and profit okay so for that drive sales from here drop it on your workspace and similarly drive profits okay by default what is happening is you are creating a clustered column chart okay now you can change it as per your requirement so let's say you want to create a line chart so just click online so the sales and profit data gets converted to a line chart here but since we don't have a second parameter or the axis based on which these values have to be mapped its just giving me two points now this is the sum of sales and sum of profit that I have only on a given interval so for that what I do is that I bring in the order date that is the date on which it was quarter okay so when I said order date here indeed different parameters here you have your water month and D so let me remove here I'll remove month and D and I'll make it in terms of quarter so it is giving me in a reverse order that is with respect to the different quarters so you can see here it is giving me a descending order as to the sales and profit now this is basically because it is getting sorted with respect to the court to change that just click on three dots here okay then you can see it by default it has set sort by quarter so you can change it sort by sales or sort by profit so you can see here by default it is giving me quarter four quarter three quarter two and Cuauhtemoc okay so I mounted in an ascending order this is something that is set by default so to change that just click on the three dots present here okay you can see it is following a descending order so I want to make it in ascending order so just click it again and then you can see it changes into an ascending order well score a 1/4 2/4 3/4 four okay so the size seems a bit small so let me just increase that as well so let me make it similarly let me increase here as well on the y-axis so this is basically how you can increase the size of the font that are present accessible to the different axis so I hope this is a little more clear so you can see with respect to quarter one my sales was close to 2 million wherein I had made a profit almost about $240,000 but when I went to quarter 2 it increased my sales reached about 3 million and my profit equally increased to 325 million finally by third quarter I had a sale closed three point five made but a profit at the same time was more it was about four hundred thousand dollars finally in my fourth quarter four I had saved close to four point three million at the same time a profit of five hundred thousand dollars okay so let me just increase the legend size as well because it this is your legend so it helps you identify which is my sales line which is my profit line asthma so here again let me make it let's say twenty seven and let's see if you want to change the color of the line as well you can do that in the data color option here so see us can let's say if you want to give it a blue and for profit let's say I'll go thread okay so this is a blue and red combination that is happening here okay now one thing you need to understand this this is a combination of the different quarters data this data as I had mentioned is a collection of number of scenes from 2011 to 2015 okay so here what happens is all the dates get grouped up with respect to the different colors so this value that you see okay this basically is a summation of all the sales and profit you have made in the first quarter for the year 2011 2012 13 14 and 15 okay so this is a complete combination with respect to that see okay that's the only reason that you have four points as well but if this was not happening then you would have 20 points rather than four points but now what is happening it's it's combining the different quarters then it is helping me understand what is happening exactly now let's see if you want to filter this data a little more so let's come back to our field stuff and here I come down you can see here the different options already present here you can see with respect to the different quarters you see with respect to different profit as well as seats now let me bring in something interesting here so let me bring in my category here as well so just drag-and-drop category here and it gets added so let's say I want with respect to only furniture so if you select furniture okay the visualization completely changes so earlier when I was making two million sales now it's only six hundred and seventy thousand dollars these are actual sales are not and my profit is close to fifty thousand four hundred ten dollars so again this basically radiates because this is the details with respect to my furniture now let's say if I remove furniture and add office supplies then it slightly changes with respect to let's say so I guess here with respect to how our data visualization is created and how we achieved the first insight any doubt with respect to that now if you remember this insight is not complete because we need to identify this with respect to the different regions as well so what we'll do is come back to our filter option click into select or minimize this and let me add in the region option from here to my visual level okay so what happens here is that I can see all the different regions present here okay so here these are the different regions you have Canada Caribbean Central Africa Central America Central Asia Central US eastern Asia Eastern Europe and so forth so you have different regions as well so what we'll be doing is so let's say I want to see it with respect to eastern Asia when I click on eastern Asia the visualization takes a change again here so with respect to eastern Asia I have made a hundred fifty thousand worth sale and I had made just a profit of thirty thousand okay although it grew in my fourth quarter to almost three hundred thousand and sixty thousand profit let me check on others breaching let me come down let me see the eastern US region okay so here you can see a huge group my first quarter there was just a $65,000 say but when I come to my fourth quarter it's still three hundred thousand and my profitless forty four thousand only okay so this helps me identify which are the regions that are not doing well and which are the regions that I can put my interest to how to market my product No okay so if I again come back here if I say Central America so you can see here it is not a normal crow so I had 150 thousand in the first quarter second quarter is almost the double third quarter is close to the second quarter itself but my fourth quarter is really high so this means that there has been a growth in the fourth quarter as well as the second quarter so this basically calls in more investigation so I need to try to understand why there was not much growth in the third quarter but at the same time what led to the growth with respect to these two corners ok so I hope this helps you identify what are the insights also that you can receive from these visualizations so move forward let's look at the second inside that we need to achieve as part of today's session we need to identify the performance of different states okay so for this what we'll be doing is we go back to our API and let me create a new page I'll rename this state performance okay so here what we're going to do is first we're going to create a visualization and then we're going to give it three so let me select the visualization that is a scatter chart again you can see it has been added here to this what I do is I take my scenes from here added to my x-axis I'll take my profit added to my y-axis and then what I will do is I'll add my state details here so this basically becomes a scatterplot here okay now what I'll do is let me come down let me add a filter where in my country it's just United States so this basically will give me a state wise representation of the different prophets I may in United States okay so this is basically a scatter plot between mice profit to my sales okay so you can see here the highest profit to sales ratio is from California when I have almost four hundred and sixty thousand sales and a profit of eighty thousand at the same time this is followed by New York New York has almost close three on $11,000 worth sales and it has a profit of 74 thousand so it's almost close because these regions have a huge difference with respect to the sale amount but the profit is almost same in New York okay but when you come down to the switches the next state that is following it is Washington now what should mean what is happening is that I have just one hundred and thirty eight thousand words here and my profit is thirty three thousand but when you see this light right this line is what you need to concentrate on when we first increase the size of the font here so go back to our filter okay yeah this should be better now if you see here these are the regions that are giving me negative profit that is I'm wasting my money here even though these regions are bringing in sales but they are not bringing in any profit so if you look at the state of Texas I have almost one hundred and seventy thousand watt sales coming there but my profit is in a negative theta which is minus twenty five thousand that basically tells me that I'm investing way more to get these sales than making a profit so these become the regions which I need to concentrate more okay that is these are my highest priority states okay apart from that let's see you want to add a specific trendline here so for that you need to come to the analytics tab here so trendline basically is a reference line that you can set okay based on your overall group so this is your standard sales to profit group so anything above this is definitely good for you but anything below is not really good no let's say you don't want to try and let me remove this let's come back let's say you want to add an x-axis constant line so let me add the value of let's say which are the regions that come below the x-axis value of 100,000 okay so these are the regions that I need to improve myself anything that is on the right side of this definitely is doing good in terms of sales but same time let me hide a y-axis constant line so let me keep the value of the Y constant about 22,000 if this is basically a rough number okay so anything below this and the intersection this area right this is supposed to be my highest priority area anything below this definitely it requires an attention so if I divide them into different quadrants let me just write it down here let me call this one let me call this to make all this three and let me call this four okay we have sorry with respect to how it looks odd but I hope this gives you a general idea here okay now here what happens is quadrant one does not mean any attention because these are my high priority quadrants and anything here is also bringing me good service and good as one quadrant to can improve itself it can slowly move to moderate one so I need to concentrate more with respect to the six okay so it is bringing me profit but again I need to increase my sales in this region quadrant three is the biggest Messier even though it is bringing me good amount of sales but the profit is negative this basically means that I'm throwing away my money in these regions as such quadrant four are the regions which are performing moderately okay but in terms of numbers they are not really anything they're neither crossing my cash holding number of sales nor are they cross my threshold in number of profit so this is going to be my second region of interest my first priority is going to be the regions in quadrant three and my second priority is going to be the regions in Quadrant II so if I have time I can try pushing the states in quadrant two to contract one and quadrant one is always above my benchmark but this doesn't mean that you don't put any attention to this feature okay you can always come up with new ideas to increase them pure okay maybe some understanding as to why only these three regions are making this may help you grow with respect to all your quadrants as well okay so these are just solve the various insight you can get from these visuals so I guess clear with respect to the insights that we have achieved here any doubts with respect to what I have discussed great so the third inside what we'll be doing is we try to identify a segment wise performance they will see how each of the segments are doing in the different states so let's go back to a power bi desktop now let me create a new page here and let me bring the category CEO okay so rather than taking the categories what I take is the subcategories this will give me a broader idea so I bring in the subcategories okay and I'll add to it the number of sales so this basically is going to be represented in a table form but a table form is not really helpful for me so what I do is I put it in a clustered bar chart so when I do it in this way it becomes a clustered bar chart give me the different sales made by each of the subcategory okay now what I do is I create it a little bit ok now that this what I lose I just bring in the profits as well so I'll add the profit into the color saturation option okay so this you can see has waited now if you go back to your format option you have data colors here okay so here let's again enable divergence okay so let me make it light green let me make it mint green and let me make it darker so you can see with respect to which are the categories that are giving you profit and which are giving you seats so if you see folds are the regions with respect to the max of up sets how our copiers are the regions with respect to the maximum profit okay so this also gives me a different insight with respect to how these regions are performing okay so this again can be represented in a different way you can also try the sort with a pie chart but this is my suggestion so here's a homework for you why don't you try representing the same chart in a different way and it will be interesting for you as well so let me just rename this finally once more I'll call it segment performance okay and let's go back to our presentation and let's see the final inside that we need to get now this basically is to identify the revenue generated by each category of the product okay and identifying which category of product is hampering my growth let me create a new page so I'll call it revenue generated okay so cook it I mean what are the different data that I need for this visualization you can take a guess guys okay yes so I need the category definitely so let me put in category I need my sales I need my profit and finally let me also bring in the order date but here what I do is in my order date I'll just stay here instead of months this will help me understand how the different category of products of function throughout the year so what I do is I just variated a bit let me take a line chart graph so here what happens is you can just see two things status with respect to furniture office supplies and technologies the sales to profit ratio okay so let me just fullscreen this now here what happens is you have the option of going to the next level of hierarchy so if you just click on this what happens is it becomes sales and profit by here okay so you have sales profit per year so this was our sales per profit distribution but when you go out what happens is it's the profit made by each of the categories over okay now I'll just bring this down so here what I'll do is I just remove the categories from here and then it just becomes a sales to profit ratio okay what is the sales and profit made in different years here what you can do is that you can actually filter it so to have wish we level filter what I'll do is I'll bring in the categories so this basically will help me identify the sales and profit made in each of the year so let's say for furniture alone this is the sales to profit ratio made in each year so Dylan 12 2013 14 and 50 okay similarly with respect to office supplies you get a number let's say furniture and office supplies so this is a different number and you can work around with respect to them so this will give you an insight with respect to all the three categories at the same time you can also compare multiple categories together okay so this rather than applying it directly to the visual can be added as a filter to your visual okay so with this we've achieved the four major insights that we are trying to achieve and beginning of the session and we've created multiple reports for this now it's time that we could want to create our dashboard before that what you need to do is that you need to save this file so let me save it so let me call it my first dashboard okay so here when I save it it's gonna be saved in form of a dot P bi X file which is supported file from Poppaea now that I have saved it it's time that I publish it to my second public interface that is probably a search that is going to be the interface where we will be creating our dashboard so for that what you need to do is that you need to publish so this is one of the major reasons that I had mentioned that you need to sign up for power bi okay because this is going to go to power bi service which is an online browser-based interface and that's where we want to create our dashboard once you click on publish so you can see it's publishing to my power bi service okay so here itself used Dipti notification that has successfully publish it to my power beer and click on open power bi - but so you can see here the browser is opening this report so you can see here it has been opened asked different pages for a report now let's say if you want to edit this report you can do that on power bi service that's fun so just click on edit reports and you have almost saved features present here as you had power in power bi desktop but again what I need you to understand is you cannot manipulate the data that is associated to this report to do that you need to work it on your barware desktop not on power bi service ok so here let's say me remove this so this is profit region with respect to all this was the falafel representation that we had created first so this also has a next level of hierarchy if you see ok so what is happening here is that it is considering the whole profit of united states rather than individual state so if you go to an upper view it basically becomes this so that internet would be helpful when you're working with different countries ok so we had set a filter here that the country is just United States ok now if I remove this ok then it sort of becomes a mess so if I do that it happens to be a global level representation ok so that time it becomes slightly hard for you to identify of which so that time what happens is you need to see the bigger picture here so you can drill down to your issue report and then what happens is it becomes a representation in terms of a global scale so earlier what was being represented as individual states now becomes with respect to different countries so now it's time that we begin by creating our first dashboard ok so you have the option here of report - so you have the option of work so you can work with multiple workspaces ok so by default you'll be working in my workspace and under that you have first of all that I had created earlier and the present report that you were working with that is my first dashboard so you can also load an Excel workbook here in case if you want to use a lower book for different visualizations and apart from that you have the datasets associated to the different reports also Presidium ok now let me show you how to create a dashboard now creating a dashboard is quite easy all you need to do is just click on the pin visual option ok and it will ask you whether you need to pin an existing dashboard or create a new dashboard but since there are no existing dashboards I'm gonna create a new dashboard I'll call it my first dashboard and help into it okay similarly I take my state performance okay I'll pin it to my dashboard segment performance also goes to my first dashboard so you can see here by default it is showing me it there's a dashboard present so when you work with multiple dashboards you can select it accordingly could have been let me take revenue generated also I'll pin it there okay so with this we've pinned all the visuals that we have created to our dashboard so let me show you how how the dashboard would look like so here you can see now there is an entry for my first dashboard if you click on that you have all the full visuals that you had created as part of your dashboard so this is what a dashboard would look like in a web view but let's say if you want to see it in terms of a phone that is if in app how it would look like so just do the same you just pick it change it here so this is how it would look on the phone application so this would I can radiate with respect to how it is set on your phone the screen size and everything would magnify the image but don't why okay let's go back to the web view now here let's say if you want to set it as feature so then you have the option offset as feature so if you say such as feature this becomes your feature dashboard as such okay then you have the option of favoriting the dashboard so when you're working with multiple dashboards then it becomes easy for you to manage with it then you have the option of sharing your dashboard now to do that you need to upgrade your account to power bi pro with whiskey would cost you only $10 a month okay they do have a 30 days trial period as well but since I've completed mine i presently is being disabled so once you do that you can always share this report to anyone that you have okay now let's see if you want to review all of this as well it's quite simple it's just to drag and drop operation so let me show you one of the most unique feature that power bi dashboard allows you to this basically is your power reactivity so let's say if you want to have any idea with respect to your data let me ask which state has highest sales so this - may bridge state is bringing me the highest sales so it's coming from England and accordingly it has given me a wishful with respect to all the states that is it's giving me the maximum first and correspondingly it is following it by sending bottom ok so let's say which state has the highest profit let's see with respect to profit so let's say profit ok now again you can see it is changing here so it's England again here but the values have changed ok so if I say is profit and sales so now it has completely changed it's giving me a scatterplot ok but this is slightly different from this category have created this is with on a global scale and this basically is an insight that is being achieved through Barbie eyes Q&A feature so for any dashboard that you create you can use power vs Q and a feature learning you can simply just put a browser queries on power bi will create a data which will and give you a complete insight today we are going to discuss two of the most talked about tools in the business intelligence and data visualization market yes I'm referring to power bi and tableau but before we get into the details of these two tools let's quickly take a look at today's agenda like we always do in all of my sessions when I would be discussing these two tools based on these parameters that is visualization cost of ownership integration data management and finally functional comparison now for functional comparison I've again jotted down few parameters and we would be discussing these two tools based on those parameters as well so let's not waste any time and get started with this discussion lap visualization well it completely boils down to your preference so let us take a look at these two tools one-by-one first we have power bi now if you're looking for something called as custom visuals then power bi is a clear winner why because it has opened up its SDK for visualization and that has given you more custom visuals plus it has great drag and drop features it has good data import capabilities that is why if you're looking for custom visuals any days fabia is a winner here as far as tablets concerned it gives you pure visualization if you have somebody who likes the curated approach or more cleanly kind of an approach you can always go for tableau why is that because it would give you create drill down features it would give you amazing visualizations so as far as visualization is concerned yes if you ask me for my opinion I would say tableau is a clear winner next we have cost so when you take a look at cost we have to consider something called as the initial cost now if you ask about initial costs power bi wins why because it is way cheaper compared to tableau if you compare its next of user cost if you compare its web user cost server node power bi is a winner here but then this is not the only cost you should take into situation when you talk about business intelligence you have to consider other costs costs that can be considered on the longer run so are there any other parameters which we can consider for the longer and cost yes definitely and if you compare those parameters like labor costs total cost of delivery and all those things this is where I feel tableau is or has little more heads compared to power bi why because on the longer and many compare it's labor costs its total usage cost and all those things even though your initial cost is more tableau gives you more affordable kind of a software when you look at from the longer-run perspective are the total cost of ownership perspective so if you ask me from my opinion again I would be using the longer and thing and for now tableau is a winner here as well third on this list we have integration now integration again it kind of boils down to perspective like dividual as in factored it this is because these two serve completely different functionalities if you take a look at power bi it gives you great integration capabilities how it acts more like a Swiss Army knife that is it readily integrates with various other tools now it's a Microsoft product and there are various other Microsoft products in the market that various businesses use and since power bi lets you integrate with these two tools it kind of has an edge over tableau because it can integrate with various tools like you have reporting services Excel SharePoint and all those things so all in all when you talk about integration capabilities yes power bi gives you a lot more options tabla on the other hand it takes in more of a scalpel kind of an approach or a surgeon like approach where if you are dealing with a particular defined kind of a problem or you need more curated kind of an approach then you should go for tableau which gives you sleek and clean visualizations but if your main aim is integration yes tableau is a great tool but power bi has to win here so if you ask me for my word yes it would go for power bi definitely next we have something called as data management now when you talk about data management you have to talk about data shaping data modeling data analytics and all those things let's take a look at those one by data shaping power bi great tableau it's good but power bi it's great why it has something called as query editor that uses M language and basically it lets you do so many things with ease and you do not have to worry about switching into Excel every now and then because your power bi kind of takes care of it there and there so yes it does help you and if you ask about tableau even the people who use download complain saying that this too much to and fro between excel and tableau if there was a solution for it it would have been much better so when you talk about data shaping power bi wins data modeling again power bi as to win your y DAX power pivot and that SQL framework basically which it has definitely gives it an edge when you talk about data modeling analytics again power bi why because probably is very fast yes it does not have a screen and curated approach as tableau but overall data management if he asked for my vote any day power bi next on this list we have something called as functional parameters so what are those parameters when these are few of the parameters which I have gone ahead and jotted down we have the year of establishment now tableau had a great head start here because it started 10 years prior to power bi but power bi is kind of catching up but if you talk about overall organizational approach for a data visualization tool tableau has more experience compared to power bi but fabia is definitely catching up applications now as I've already mentioned custom visualizations or more open source approach is what you are looking for power bi is your thing more curated and clean approach tableau so if you need something like custom visual stay dashboards you can go for power bi ad-hoc analysis and longer and operations related to data visualization tableau users well as far as my personal experience is concerned tableau is little difficult to learn when you compare it with power bi power bi is much easier to learn and it is for the wider applications given the integration capabilities it has but that is a personal opinion I don't want you to jump into that because people who have used more table might find tableau easier to use so that kind of boils down to your preference really if you ask me I like power bi more when you talk about ease of use I have had varying opinions for people as well so er the best days and you are the one to decide on those things support tab Levin said clearly it has better support compared to power P AK scalability good power bi is good but tableau is great if you talk about applications on the longer and better scaling tableau has to win your infrastructure again both take a completely different approach your power bi gives you SaaS kind of an approach which is software as a service whereas tableau gives you more flexible kind of an approach where you're free to like or not free but more flexible kind of an approach basically so these are the parameters I felt that we should have discussed there quite a few other parameters where these two tools can be compared and as I've already mentioned that these two tools are very neck-and-neck or very close to each other these are two of the most talked about tools or the hottest tools when you talk about business intelligence and it would be unfair to say this is a clear winner or that is a clear winner it actually boils down to your preference the best way for you to decide is to go ahead and use both of these two tools those are readily available to you and very easy to learn so you can pick those and they sell it on your own which tool is good for you a great for you and that would also depend on the problem statements which you have or which you need to solve as far as this video is concerned I just wanted to give you a picture as in how do these two tools fare based on these parameters I welcome you all to today's webinar on power bi interview questions so the idea is to over the next 1 hour or so the idea is to walk you through some of the most commonly asked interview questions in power bi so we're gonna be focusing on a lot of conceptual topics a lot of theoretical questions and a lot of practical questions and the idea is to not only walk you to the questions but also to actually take you through a very very good demonstration and just to give you a very good approach an idea on how to answer some other questions ok so I'll try to connect a lot of these questions to real-life scenarios so that when you're asked a question next time in your interview you're not only giving out a theoretical answer but you're actually able to connect with practical examples and use cases and stuff like that so we're gonna try to make it as broad as possible and obviously the idea here is we have picked up a very very limited set of questions but it's a pretty good and some of the most important questions that we have picked up for this specific webinar so first of all have you broken it down into a few general subtopics so first of all I'll be focusing on a few general power equations and obviously what is self service business diligence so needless to say power bi the very fact is that power bi is one of the most popular SS BI tools in the marketplace today and any of you who have worked on other AI tools coming from the world of tableau or clicks ends or Spotfire you can also relate to this so if you are asked a question what is self-service bi I think the first approach to take is to explain what is SSB I okay now any of you as I said if you've come from the world of traditional business televisions right if you've worked on tools like SSRS Cognos if you come from msbi world or if you use the Business Objects you typically have used tools where it's not exactly built for end-users right so essentially building reports or building any kind of project used to take a lot of time obviously for developers it's a very easy tool to use but if you think of end users at the end of the day if you think of the business users or the end users or you know non-technical people would find it very very difficult to use something like say a visual studio to develop them SPSS honest reports okay so that's the world of traditional BI where development cycles were long and there's a big hindrance right so if business users want answers to certain questions let's say if a mutual fund manager wants to know how has my fund performed over the last 10 years enough of a very simple line chart that mutual fund manager has to go back and depend on his internal IT team to go back and build that chart for him it could be a very simple line chart but as I say the mission fund manager may not be that well-versed in a traditional bi tool like a necessary okay so they will be depending on the internal in-house IT team and that way the overall process will become pretty slow because the IT team will have their own requirement getting process they'll have their own software development framework which they'll follow and probably a very simple activity which which ideally have taken less than 10 seconds to build right on paper line chart is so simple but then it could easily take 2-3 weeks depending on a lot of factors so this is exactly where self-service bi comes in and self-service bi is an approach to data analytics that enables business users to filter segments and analyze their data and this is the key where the key is really to focus on business users and you can either say business users or you can say end-users customers so essentially non-technical people who are the real consumers of this particular tool ok and what is the benefit of using this and if you just compared to this scenario just I discuss this a while back now here the mutual fund manager can directly use a tool called power bi and he can directly connect with the data you can directly build the line chart all by himself ok so obviously the development cycle would be much faster and the business user would be happier because you know he has control of his data he has control of exactly what he's seeing and he's not dependent on any external team ok so it's a win-win approach it's a win-win approach the cost the end user is happy the IT team is also happy because sometimes what happens in traditional bi projects especially if there is a requirement involved typically requirement gathering phases as we have all encountered right there are so many iterations that take place right you´d requirement gathering you do one phase of requirement gathering and then something misses something gets missed and you come back you build a product and if the customer is not happy with the products you go back and build it again you hit it over it right so those kind of problems can crop up especially in traditional approaches but that is something that's completely out of the picture in a SSB I approach because the business user himself or herself is building the report and there is no question on dependency and whatever they want they are building so essentially it's a win-win situation and as I said it's a very easy process and anybody who has basic understanding of data can create reports to build intuitive and shareable - puts so when you answer this question is very important to get the understanding give the basic understanding of what are the challenges of traditional bi what are the challenges of some of the other top e aí tools and where does SS VI fit in it should be a very bland dancer just that no what is SS behind at Bobby as an SPI tune but try to be as broad as possible try to give context on where s SBI comes from why is this VI important and what are some of the other tools remember power bi is a very small part of the whole landscape the BI landscape is dominated by our tools like tableau like Spotify and like lik sense in the data visualization s SBI landscape and if you talk about s SBI in analytics is also tools like all tricks which have really revolutionary and you can you can take these examples and that will really get to show your depth and breadth of knowledge in the entire bi space ok so moving on next question what are them are parts of Microsoft sales of is VI solution so as I mentioned the SSDI question in in the beginning was be only a very general question now we are being more focused on Microsoft stack so we have focused on the entire Microsoft stack and obviously when you talk about the Microsoft stack we are talking about primarily two toolkits here so one is obviously the XLVI toolkit and the other is the power bi toolkit okay now some of you might be wondering why am I using XLVI toolkit so if this question is asked to you in the interview what do you actually say do you actually mention Excel the idea mentioned power bi because obviously we are discussing power bi so it's natural we'll be talking about power bi but it is also very very important that you mentioned XLVI because remember how o bi is nothing but an extension of HD Excel add-in components right so if any of you have worked in Excel and I'm assuming all of you have worked in Excel and I don't mean to say working in Excel in a spreadsheet application so obviously Excel is a spreadsheet application B you have all of us a user even if you have don't even if we say if we don't work to make cell all over the scene Excel spreadsheets right but what I really mean to say is Excel from the standpoint of some of its add-in component some of it add-ins components like the power bi components of XA like power query power pivot Power View and when you think about these components what are they they are actually helping you build bi solutions right within Excel I know it comes as a surprise for many people initially to believe that probably I actually came from Excel but that is the truth when you think about the origins of power bi then you think about how power bi came into existence it came into existence from Excel so whatever you have in power bi is nothing new right so all these components have already been there in Excel for a long time okay so when you think of power paper power paper was actually launched doesn't hadn't given you think of power query again power query was launched way back Power View is again a very very integral component of excel so all these components were already existing in Excel and all that power bi desktop did was it repackaged all these components which baa-baa text oh okay but it is very important to understand that they were all already there so the obvious question that I sometimes face from candidates is you know why did power bi build a tool like that no why why did I build a tool like that if xn I had already had these components and why not use Excel and the challenge was that and this is I'm sure most of you who have actually worked in these tools would have faced similar challenges is in Excel you of so many different versions you know if you especially feature 2013 2010 version 2016 version and within each version there are so many different editions and then the other thing is that these add-in tools are not exactly part of Excel there are different releases you have to separately add them you have to install updates so all those challenges are theirs if you're migrating if you're moving from one edition to another edition the compatibility issues crop up so all those problems used to happen with this particular stack in fact as I told you and as I will show you in in some of the upcoming discussions that all these components the behavior of this components are exactly the same when you compare the XLVI components and when you compare the power bi components that is the power bi directional components they are exactly the same there is no difference at all okay barring of you very very minor differences they borrow most of the features right however as I said the idea of using the power bi decks toppers to package it in one single solution tool and present it to end-users which is just much neater it's just much easier to use it and overall it's a just a good solution it's just a clean solution that Microsoft has built ok so again coming to the question it's very important that you mention about XLVI because most people will actually go back and say okay Microsoft self-service bi to solution will only be for VI which is obviously this power bi interview so we will actually say only for be able actually it's no so you should mention that it actually came from Excel and Excel by the way is also a very very powerful self-service bi solution because remember even without the power bi comprehensive the Vixen even without these excel components like power query power view of power pivot think of a very very simple use case where you have a simple spreadsheet application a business manager has a very very simple spreadsheet application and he has some data on an Excel spreadsheet and all he wants to do is create a very very simple chart out of it can he not use the charts fings function in excel answer is yes right all he will do is he will connect with excel data he can simply create either a pivot table or he can create a pivot chart and he can analyze that particular data right within Excel and I'm not even talking about our query power paper in power view I'm talking about simply creating two tables and pivot charts in Excel which you can do without using any of these components right and and I mentioned about that particular use case what the business manager is actually doing is that business manager is actually performing self-service bi right he's performing self-service bi right with an X in okay they don't what is for bi desktop obviously power bi desktop is the free desktop application and typically this question can sound a very very generic question and they just want to test out your overall understanding of the tool that we are obviously guru do much of our development in which is the power bi desktop tool so it is very important to a few keywords are very very important one is obviously it is a text opera application one other key word that I would really like all of you tu use is the client tool it is a crying tool okay remember power bi the entire architecture of power bi is a client-server architecture where you have the power bi desktop which is sitting in the middle as a client tool so power bi desktop is where you actually do all your development stuff okay that is where you actually build your reports you actually build all the cool visuals you actually build your model right inside power bi desktop and then you publish it onto the cloud which we call the power bi service so remember it's a client-server architecture so mention those words mention those keywords mention those terms and the other thing that you should also mention is and again as I say when asked the question on what is power bi desktop just focus a bit on the entire architecture generally okay so they may want to know what is the next stop but if you talk about the service that just goes to show that your overall having a pretty good breadth of knowledge and just just give them a picture this give them picture don't mention about all the components but just mention about what is the desktop and the surface at least mention about the next up and the service generally ok and I think this is also a very very key part the second point maybe a saying that probably a text off works quickly with the power bi service and as I mentioned here just just mention the part that this is the client and it's the server essentially okay what are the power bi components and this is again a very very important question and it's more of a follow-up to this initial question so sometimes they might want to ask a more specific question like what is power bi desktop and remember when I've been asked this question you should focus on desktop and the service but now this is a much more broad-based question where they want to know your detail knowledge on power bi now what do you understand about the entire ecosystem of power bi and this is what the complete octet resolved all right you can see that it consists of power query power pivot power view these are obviously they add in components okay these are the the power components then you have power map data management gate fee power bi Q&A is another level a feature natural language query which I will just talk about in a while and finally power bi service okay so mainly theoretical what I would suggest is just give them a picture just give them the names of these eight components and just give them an idea of what each of these components mean okay so if asked a question like this just focus on power bi again been asked a question like this just focus on power bi give a very very brief description about power bi probably just concise probably mention power here is a self-service bi tool and it has obviously the these are the main components your the power query power pivot power view and all these three are part of power bi desktop so all these three can you can just Club it as part of power bi take stock and then your power map which also is a part of power bi desktop and then finally update a catalog management gateway power bi q-and-a and power bi service which one obviously is the service component okay obviously there are a few things which keep changing in the overall ecosystem so remember one thing you have to understand power bi is that the product is very very frequently updated so for instance the power bi desktop gets updated every month so every month power bi desktop it shoots house releases every month and the power bi service which obviously lives on the cloud that shoots out updates like every week or probably multiple times a week okay the new features getting added every single time and the reason why I mentioned this is because of the Q&A feature remember Q&A feature for a long time has been on the service and I just mentioned a while back the Q&A feature isn't a service it is still in the service but then in the desktop it has very recently been included as a preview feature okay so when you talk about these things if you have an understanding of what are the recent updates you know what are the recent releases that are coming from the power bi stack if you keep yourself updated but is the january updates what is the december updates and if you understand this the length and breadth of everything that's happening in the power bi landscape i think that will just add more value to your responses and the answers to the questions that you give so just as an example for this particular question if you are talking about power bi Q&A one thing I will recommend is just mention that it's a service component but also mention that off late power bi has included that as part of the power bi desktop okay so now you can actually go to power bi desktop and you can shoot Q&A features directly from there and I know I haven't talked a lot about this yet but I'll just quickly connect to my data here in power bi and as you can see I am just connect this is my power bi desktop interface that I've opened okay and I can very easily connect to my excel file see how easy it is to connect to an excel file here from power bi desktop directly and I will very easily connect to my excel file from here this data and now you get a very simple interface from where I can choose the sheet names so this is an excel workbook where I have three worksheets orders people and returns I'll select the order sheet okay the edit is way it's basically for opening up the query editor if you open up if you click on edit it will open up the query editor where you can edit your queries okay and this is basically what you're gonna get you're gonna get data in your power pivot model and now you can go back and visualize your data okay so if I want to remember the example of that fund manager whether the fund manager wants to see how much profit he has got it's based on every day you can see how easily just fit three clicks he can generate a very very nice line chart okay and you can see how easily we can build dashboards in power bi okay and how powerful it is a self-service bi - okay and just to come back to the question on Q&A feature and by the way the preview features are turned on in power bi desktop in this particular thing you can go to file options and settings go to options and you can see all the preview features will be listed out here go to preview features and you can see all the preview features listed out here you can see I have turned on all the features like Spanish because I don't know Spanish so I can turn this on right all right so these are all the preview features and if you just double click on I see something called Q&A is listed out here and you can just double click on this particular take and you should be able to see that very very familiar Q&A feature which comes up in the service I think that same thing is now built into the neck stop okay and I will talk more about the service later on guys but just to bring this up as an answer to this question I just wanted to quickly cover this with all of you so you can just double click on this and you can ask questions you can ask some question like you know sales by region pretty cool I'm just typing it and if I want to see it as a pie chart I can just say as a pie chart ok so just by typing I can see exactly what I want to look at and it's Excel physicians is one of gives you some suggestions here as you as and when you type their values and you can see we're going to get the matrix wanna see this as a different kind of visual let's say as a as a table it gives me all these options okay so that's the Q&A feature that is built right inside the power bi desktop as a preview feature and when you answer this question the other important thing that I do want to mention here is just giving a brief description about all these components remember guys power query is the ETL component of power bi desktop so that's an ETL component so the idea in power query is you're taking your data you're connecting to various sources and you're performing a very very basic ETL operation there inside power query right so the idea is to just perform basic data cleansing operations because remember data from the source is never in the right shape it's never in the right format okay it's what we call dirty data so data for a source is always dirty so you want to always clean it okay you want always clean it you want to perform some basic transformations before you use it for your actual analysis okay so that is the very very first step in power query okay so if you remember what I have done is the very first step in power bi is I connected to my excel file right and typically the way to open up query editor is you can click on edit queries here or you could have clicked on edit in that particular dialog box I got just a while back you could have directly click on edit there okay so click on edit queries here and once you do that it'll open up the query editor where you can go ahead and edit your query okay you can go ahead and perform basic ETL operations obviously there's a lot that is here we're not going to cover everything but at a basic level this is just to just for you to understand just to give you some context as to what this interface is all about okay and just a simple example you can probably give some simple examples here where let's say you want to you're looking at the orders table and you don't want to look at all this data so you were removing some columns and let's say here you can record and remove all these columns you don't need all these columns you want to remove the ship date so you don't care about ship date you don't hear about customer ID and see whenever I perform any kind of operation here whether it's a remove column or if I do an ADD column now just here I can also go back and do an add column so I can actually just come back here and I can add a column I can add a custom column is just like adding a calculated column okay so I can add a column called a cost and cost is going to be equal to sales minus profit okay and that's gets added as a calculated column and that again gets added as a step so everything in a query is basically a step okay so mention these key words that you build queries query editor and behind the scenes you're creating steps so mention these pieces when you're talking about this particular answer and also mentioned data types very very important part setting data types properly okay you can see what's happening here is when I build that particular column called cost so this becomes Lucy type it's not exactly in there correctly typed format okay maybe see one two three basically means that it's not properly typed so you have to go back and specifically set the type to decimal number okay these are all the data types that are available in power bi desktop and so that's it that's all I have and what I will do now is once I'm happy with whatever changes I've done let's say I perform all my data cleansing and data transformation activities I can go back and close on the fly and once I do that what will happen is data for will get loaded into my power paper data model you can see what happens here it is Katie could if you get loaded into my power pivot data model okay so the first step is power query variable perform the ETL and the next step is from power query the data goes to power pivot and power pivot is nothing but an in-memory columnar database where your data gets loaded ok that's your data model in other words we call it our data model so the idea is that you perform your ETL you clean your data and then you load your data into the power pivot model okay and from there you're gonna visualize your data using Power View okay that's the very very basic process that we have in the power bi big storm the same as we have in Excel okay if even if you use the Excel bi components it'll pretty much be the same thing and just to give you a brief glimpse of that I'll not talk a lot about it I've opened up my excel remember I have opened up a excel but also have the add in components loaded so I'm using 2016 version of excel by the way so it's very very easy to do the same thing in Excel as well so you can go to get data I can go to file you see the interface is very similar to what you have in power bi techstop and which is why I actually drawn those parallels in the beginning of our discussion where I said that both these components draw so much from each other okay and connect with that same excel file here click on import it's connecting I'll get the same interface where I can connect to that particular worksheet from that workbook it is still establishing connection either the same interface a very very similar kind of interface opens up just a while back what we saw in power bi desktop it is taking a bit of time to load and the idea is that the idea we aren't showing you this thing is just so that you are mentioning these things when you're asked this question so that you get an idea that okay the same thing you can do an X X in X well what you can do in power bi desktop I'll just quickly go in and expand it and select my orders table from here and you can see the very very similar options are coming upload edit it'll open up the query later very similarly how it opened up in my power bi desktop okay so you can processing my queries the concept of queries is very similar to what we saw entertain dekstop and you can see it's opening up the query editor and again here you can see that it ain't a face is very very similar to what we saw in power BX don't remember it's very easy to confuse it I'm actually opening up an excel but this is actually it's not pave tech stop okay so I can do the same stuff I performed just a while back in power bi desktop I can go back and remove my columns I can do my order date a row ID see the steps are getting added I can add the custom column go to add column custom column I can do pretty much the same stuff here okay I can have that cost column it's gonna be serious - profit click on ok custom column added and now we can go back to home and say close and load - okay and ik menu closing and loading essentially what happens is you're loading that whole stuff into your model okay and which is nothing but this power pivot alright so it's very very important that you have this understanding that power bi desktop is not to be learnt in isolation it's not to be learnt in isolation but if you have inner you should have an appreciation of the fact that behind the scenes is nothing but excel in action okay so when you mention whenever you ask any kind of questions on the entire architecture of power bi or the components of power bi it is very very important that you link it to excel and you give them a very very concise and holistic answer of what you can do in power bi desktop and what you can do in excel and how similar they are overall okay so that's about power query overview power pivot and power map power map is another add-in component in Excel and essentially power max whenever is pretty much the same way within power bi desktop as well using the very very powerful mapping feature here that you have okay you can do spill some very very cool maps directly from the power bi desktop okay and essentially you can do some pretty cool stuff isn't the power maps feature as well that you have within the Excel add-ins okay we have data catalog etiquette look is basically as your data catalog so it's pretty helpful for connecting to different sources management of different sources your date I grant management gateway which is used for connecting to on-premises data another very very essential component be something that you set up in the service level so if you go to the power bi service basically so whatever you see I have right now is what icon the power bi service so remember guys I talked about this in the initial session beginning of the session where I said this is my client into development tool okay this is probably a text up it's a client development tool this is where I build my stuff and after I build my stuff I publish it on to what I call the service and just to give you a quick demonstration of that just to give you a very very quick demonstration of that I'll build a very very simple line chart here and I just go ahead and name this as I just give it a name okay probably I'll go ahead and name it as demo save it and call it demo that's my demo Bobby I do remember with power bi reports get saved with the file name extension of PB IX it's not a very important interview question they tend to ask you what are the different extension types and it's not only with ba ba files they actually something called template file source which I called power bi template 5 is ppit remember these two types of extensions b bi x stands for the power bi file that is the power bi analysis file and p bi t which stands for the power bi template file okay so I'll save it for the name demo click on save remember - for publishing it is very important that you're signed in and once you're signed in once everything else is setup you can just go back and save it in your workspace and once you have done that you can just go to the service and check that your stuff has been published okay like something very similar with beef is simple on your service and you can look at the report section where you can see that has been published this retracted reported and published and remember this is the cloud environment okay so this is my development tool there's my development environment that's my client and this is the cloud environment which is my server okay I published my stuff here and now all my end users can view my reports and dashboards from here okay so from here I can further create what I call dashboards and then I can go to my dashboard and say share my - but I can go to share and then I've mentioned with whom all I want to share my dashboard and give the email ids of all the folks I want to share my dash boots with one very important thing to remember that is power bi you can only share stuff with everyone in your organization so for instance if you look at this link this is where you can simply go ahead and specifically give access to who all you want to share it with the stripe start typing the email addresses do you want to send them and automatically they'll receive email notifications or you can or they will basically get notifications on their power bi service interface which is basically the tab here some notifications tab that you basically have here okay so very easy very simple very intuitive process overall and just mention that mention in these P he says when you when you're talking about it and again it just goes to show the overall depth of knowledge that you have in the tool if you mention all these different components taken together while you answer these questions okay what are the sources that power bi can connect to actually there is infinite okay so ideally there are lot of sources probably I can connect to if we go to gate data this is only from the desktop okay from the tech stop it connect to a wide range of sources so you can go to more you can just take a look at the available sources that probably you can connect to we saw an example with Excel but you can connect two files XML JSON databases ton of databases that you have okay and if you don't see anything here you can obviously set up an ODBC connection very easy to set an ODBC connection also and online services you know just least this list just keeps Chris growing as your services and it's pretty cool list of services and sources in connected okay now the very interesting option is web data just to give an example on this you can very easily connect to web data and I'll take an example of money control okay probably we can go online and search for the website money control okay and this is again to highlight to you how easy it is to connect to web design power bi okay so typically it might involve writing a lot of scripts we just want a quickly connect to our website and take some tabular data from there it skip all the scripting all that stuff and just connect to the data generally okay I've got an copy that link and paste it click on OK and once you do that it will try to establish a connection with money control it does take a bit of time initially to set this up and once it does what it will do is it will go to the website and it will try to look at the website and remember is everything's underlying paste on HTML it'll try to search for tables anything which is structured as a table you'll try to search for that kind of data and once it finds it will present to you a list of all those available tables ok so I will go ahead and once again so right is it does take some time depending on the connection that you have so I think the site is not working that well right now so what is doing is a service you can live connection with that site at this moment and it will ideally typically present to you a list of all the tables table of structures that you have and you can see it takes a bit of time to connect to the site and as you can see finally my list has come up the initial case there was some issue with the connection so we were not getting all the tables and as you can see very nice little present to me all the tables it basically looks at an entire website and it's clear that entire website for any HTML the structures that it finds and you can see very nicely I get this bonds here and what is this bond if I have to quickly go back and show you in money control and actually close that website here if I were to show you quickly what that bonds is so there's a section in that website where I have something very similar to this and you can see how easily I am able to get a very nice tabular format of all you can go to web view where you get that website kind of view or you can directly select your data from here you just click on bonds and you can just go ahead and load this table directly into your website into your power bi desktop file okay it is loading it further takes some time because remember it is taking that it is straight from your website so it does take a bit of time just my mana control site has opened up here and if you just go down you can just take a look at this whole piece here that's the bond section that got loaded okay so very is nicely you can see these are double of structures you know the index is global markets okay you have a section on born you have a section on currencies and power BIA when it looks at that web data it is able to very easily describe to the data is very easily able to select that data from here because at the underlying layer these are all HTML tables okay so mention some of those examples when you talk about this particular question of what it can connect to it can connect to a wide variety of sources that is only in the desktop if you talk about the service you can go to get it under service okay and you have a further lots of services you can connect to from here so just as an example you can choose content packs you can obviously connect into content packs which are nothing but pre-built and prepackaged - boats we like to call it okay in other words another name for content packs is what we call apps so nowadays or apps are a replacement for content packs you can think of it and they are nothing but prepackaged it's like packaged - points in like packages okay you know where you have already built a dashboard we have already built in the reports the data says the dashboards the model everything and the end users consuming it as an example but I really like to show you here is something with Bitcoin okay so something with Bing Maps is an example so let's let's look at Bing Maps let's see if I can find Bing Maps here yeah I have Bing Maps here so what I can do is I can just get it now it is just like installing an app from Google Play Store or just installing an app ok and you can see what I am trying to do is I'm trying to consume that app ok it's like a prepackaged stuff all the data sets everything is built in and I have to enter a parameter so Microsoft is asking me for a parameter so I will just type in Bitcoin just to see what is the search activity in Bitcoin and just for those of you don't know Bing is a search engine by Microsoft okay it's a search engine just like Google click on add and you can see automatically that dashboard got added I didn't create it but that is something that got automatically created okay and that's the reason why I say these are content packs which are nothing but prepackaged - sports with our already built in and now we can actually see that how people are searching from where people are searching the most and obviously us people are searching for Bitcoin the most okay see some news mentioned here okay and top languages interestingly German and French are also pretty high okay see the change week over week change how many more people are viewing and he also get a pretty good line shot here where this actually shows you the activity ok the activity in the last week and a pretty interesting I mean on this particular day there was a big spike you can see that twenty four thousand thirty seven people actually search for it and slowly gradually people have lost interest at this it seems like okay not many people are actually viewing it now so anyways the idea is to just highlight that there is something called content packs and remember there are two places where you can connect to data one is similar from the next op and the other is from the service okay from the service also you get a very robust set of options to connect to data where you not only can't connect to files databases in connect to files from here onedrive local files you connect to databases I should add a businesses and you can also connect on to services and further you can also connect on to your organization and content packs if you're working for an organization and you can also connect onto any organization content makes the people in your organization have developed okay so services are basically content packs from online services typical online services like Bing Salesforce and all that but then organization is only specific to your organization so mention this piece as well when you talk about the sources you can connect to one of the building blocks of power bi I believe is something I've already talked quite a lot about but when you ask this question again as I say it mention give a very very broad-based view of the building blocks and I think I'll really appreciate if you're answering this question as an interviewer definitely they'll appreciate it if you're giving them a holistic view of the entire architecture right so talk about power query talk a little bit about excel talk about those main components which we call power query power pivot power and Power View talk about those components and then come to the building blocks the building blocks office the other data sets the visualizations the dashboards the tiles which are some of the most fundamental things that you have in a power B and just to just to give you a brief context about work for this office so when you build your stuff in power bi desktop obviously you're working with three main tabs primarily that is the obviously the query editor is where you did you ETL you know that your data into the model and then you come to the theater tab data tab is where you can actually view your model tables ok so right now I have only two tables one is the orders table and one is the bounce table so you can actually view your tables over at the data tab the relationships tab is again where you can quickly take a look at your underlying table relationships and finally the reports tab is where you can take a look at your report whatever reports you're building okay and once you publish this stuff then you come to the service and the service you basically have it's obviously the underlying model and data along with that which you've exported out or published a service that's your data sets the reports folder and from the reports you build dashboards those transfers folder and also a component called workbooks which you can mention and in dashboards you further have what we call tiles ok these are called tiles in the dashboard ok as you can see these are all tie this okay so for instance here I have created only a single line chart right if you look at this particular thing that I published I created only a single line chart now I can opt to add one more visual to my report so what I can do is I can look at my report here and another beautiful thing about the service and again something that all if you can mention here is the editing capabilities in the service that power bi gives you so you can edit a report straight within the service so the amazing feature where you get a very very desktop like interface right within the service remember however when you mentioned this part do mention that you can't build models and you can't do query rating within the service you can only edit reports ok so you can only edit reports and you can only work on the visualization layer within the service right and here I can walk to build a bar chart where I can look at sales in category wise let's say I want to stack this by segments and I can do that and you can see how automatically power bi is able to fit these things into the respective components and again the it just goes to show the SS bi capability the self-service bi capabilities of power bi so if a novice user if a business user is working on it and they have absolutely no clue you know what to put which where to put which component and if they could simply click on the fields and power will automatically put them in the respective sections ok this again goes to show the self-service capabilities of power bi and I can create multiple pages of course ok I can create another page but I can build a simple tree map here when I'm showing the categories category and I'm showing the profits now and it can build a very simple tree map here and if I want to saturate it with colors I can further add some color saturations ok I can for the resin color saturation disable to saturate just by sales I can also set to be mystical colors ok and now I can go back end and still remember this the report that I've edited so I need to go back and save it so I need to cover can save this report and once I do that it will be directly saved within the service itself ok it will be saved in the service itself now one of the questions that people might ask you at times is they might ask you that can I so now that it is the report in the service can I go ahead and download the report and answer is absolutely yes again remember it's a preview feature previously this was not there it is feature got included after a lot of requests by users it was on major roadblock generally in the development process but it's a very very useful option that you have here and now that you have saved the report you can actually go ahead and download a copy of the report in this in your local machine ok very useful feature you can download it here you can also export this to PowerPoint preview another very useful feature that you have in the service you can actually export it to a PowerPoint okay so remember it won't be as interactive it won't be an interactive presentation obviously but you'll still get a pretty cool view of you know an introductory page will be created and the divisions will be there so it's a pretty decent kind of interface that you will get so that's another feature that you actually get in the service and once you build that report you can go back and obviously pin that your existing dashboard so my - but it's going to be demo dashboard which I built and now in my existing dashboard app demo if you remember I now have two visuals okay it says power bi is ready to download and now I can see the desert type else that I have basically okay so hope you all followed this particular discussion where I talked about what are the building blocks in power bi obviously you'll mention a bit about the components and then you will talk a little bit about one of the primary building blocks is obviously tier assets visuals reports dashboards and tiles okay data sets visuals using visuals you create reports using reports you create - ports and basically using dashboards essentially the tiles are part of the - what's using reports you basically create I use and using tiles you create - coach okay what are the different types of filters and power bi reports visual filters page double filters reportable filters and drill through filters not a very very important question so typically the way to answer this question is to go back and take kids scenarios now what are the scenarios where you will implement each type of filter so visual level filters obviously is present only with a visual level so it is only you can think of it like a report level filter it's present only in a particular type of report particular type of chart okay so when you come back here and come back to the desktop and if you take a look at it you will see that line chart that I built here go to the filters panel and you can see all the visual level filters are the applied by default okay and these are going to be by default all the fields that are already a part of the report okay so these are so if you please now the chart here if you place a bar chart here for instance so whatever you do in that line chart will not affect that bar chart okay so if I take a bar chart here when I say region Y sales and now if I go ahead and filter that region so remember if I click on the region filter here I will have a region filter here for the bar chart and if i cover and change that region filter to central it will not affect the line shot because as I said it's a visual level filter so visual level filters are in short they are specific only to the visual then I have page table filters I can go back and drag and drop that region now in the page table filters and now if I go back and change central if I filter into central both will be affected okay make it east both will be affected okay so that's why we call it a page table filter all the reports within the same page will be filtered okay then I can have something but remember if I have a report in a different page it will not be filtered so in a different page if I go back and place region and sales see how it will not be filtered okay so although I am filtering by Central and East here in the page two I am not filtering by Central T's because that's a page double filter okay finally if you want to implement that you can implement something called a report level filter okay I can remove the page table filter and then I can take the region in the report table filter which means all the pages in that report will basically have that filter and here I can go back and include Central and mist and see in the first page always it will be filtered but because the reportable filter if you go to the second page also you can see that in this particular section it will be highlighted okay it will also be filtered here and reportable filter will appear in this page also get a third page it will be here fourth ways it'll be here so that is what we call a reportable filter okay and finally we call it a drill through feature which you can use a drill through filter to basically work on drill through reports so what you can do is let's say you have a scenario where you have categories so instead of region if I let's say if I have categories here and I can build another use case in page two let's say I have a scenario where I have subcategories okay so here I am having subcategories and categories of course let's say I've categories subcategories and I'm looking at category and subcategory by sales okay very simple report type I'm looking at it's a detailed view I'm looking at so I can configure a drill true filter so the idea is that if I click on my a particular category I should be able to see subcategories of only that particular category so in that particular case I can go back and configure drill through filter okay and again you can mention where let's say the use case could be that if I want to click on a particular report I want to navigate to another page and I want to see subcategories in that particular page those are scenarios where you will basically quit and configure filter reproach well one other thing that I must mention to all of you here is they'll be certain use case there are certain scenarios where people will I mean I did mention the clause drill to reports remember okay just to quickly mention how again puts us to clarify how you configure it remember one you must must remember is the filter is something that you don't place here okay the filter should always be placed in the page where you are configuring it so if you're configuring this page the category filter should be placed here okay remember I am filtering by category right what am I trying to do and I'll click on a category here and based on that I'll select a subcategory here so the idea is that you're gonna filter on the category in this particular page so you should always place category in the drill troop section in this particular page and now when you go to page one and if you right-click on the technology you will see an option for drill through okay you will see an option for drill through to page two and when you do that you will see only technology getting filtered and once that happens you will automatically see that in the drill through filter section category technology is automatically filtered remember guys it's a very recent inclusion it's not exactly a again real true filters typically was not part of the filters panel even a few months back the second came very recently and again a very new feature but it's always a very very useful feature because drill through reports is something that if you have worked across other tools Enterprise reporting tools it's a very common feature where you click on a particular item and you want to go to another page where your filtered with that particular item okay and this is the very very useful case right it's a very very useful scenario where it enables you to do that right so you can not only configure stuff in in one page but you can actually configure stuff in another page another very common scenario another very common use case for this could be that it could be initial introduction page right the initial introduction page could be like you know you're showing the category citation on category related information so showing technology and you're basically showing all that all the categories right the categories are furniture office supplies technology so everything about the categories you're showing and then you have page two and the page two will actually consist of everything about the subcategories right it is the subcategory page it is a subcategory page for the category that you have selected and ph3 could be the product page for the product that you've selected from the socratic essentially from the subcategory that you've selected okay so I can further build another page I can further build another page and guys remember this need not be a table okay this could be anything else this could also and by the way you need not you know need to have category also this could be anything else this could be this could even be a bar chart okay I'm just giving a table as an example but this could be any kind of vision it could be one visual it could be multiple visuals you can also have a pie chart here okay I can work ctrl-c ctrl-v I can build a pie chart here and you can fill any kind of issue but remember the concept is that whatever subcategories you see so I'll call this a subcategory and that is basically my initial category page and I can further build one more page called the product page and the product pages where I'll probably have some detailed information I probably I can build a bar chart here also when I'll get my product IDs or many product names in the values axis the sales nobody go back and saw this data sort by sales some Shehadeh highest sales up on top okay and here I'm going to go ahead and sort this information by subcategory so I can go back and sort this by subcategory so what I will do is I will take the subcategory and put it in the drill two filters here okay so now remember category this page I have no filters so because that's my main page there's nothing should be applied everything about my categories in the subcategory page where everything about my subcategory here obviously I've selected a category so that is filtered out but then further I can go back and select my product from here okay so the idea is that you will navigate from here so you want to go to drill through you want to go to subcategory okay so I'm looking at a particular category I want to know more about that category to go to subcategory show me more about furniture now whatever you're seeing right now is about furniture right you're seeing all the in all the top subcategories in furniture you're seeing all the tough subcategories in furniture now okay interesting do you see furnishings not done well at will okay so furnishings is kind of a lager do you want to see what's happening in furnishings so you want to click on that and now we want to go to drill through in product you want to see all the products of that particular subcategory and that's how you get to see all the products of that particular subcategory okay so that's the idea of guerrilla to filters again is a very very important concept very very powerful feature in power bi and it's very important that you mention that okay so those are the main types of filters that you have when power bi desktop content packs an app setting I covered that already so just skip to that right now that's very very important part guys and obviously Dax is a functional language very important piece in the overall power bi desktop stack remember in power bi whenever you talk about power bi and something that you guys will anyways mention when you talk about the components he obviously you guys talked about power query there was an instance where I actually wrote a custom column I actually added a custom column a while back okay remember that cost column I added where I tried a very simple expression called sales minus profit so the language behind the scenes that's being written is called M code okay so it's called M code so power query is equivalent to M whereas power pivot is equivalent to tax okay the underlying language the TVs in PowerPivot is tax when it's in underlying language that you write in power query is essentially called M and just to give you a very very brief flavor of M what it is and if you go to edit queries here remember all these queries something but a combination of steps and if you want to look at just a brief idea of what is M go to V you go to advanced editor and you can take a look at the M query syntax here okay that's CM query syntax Atticus highlighted for you here so remember is existing in query editor whereas PowerPivot is something Dax is something that exists in PowerPivot so where do you see Dax you can go to the modeling tab or you can just send to the modeling from here and go to new column and you can just start typing in your Dax queries here okay we look at more about some of these examples in a bit but this is where you actually start typing in Dax look at a very simple level I can say sum of sales I'm creating a very very simple calculated column here and that is how you basically build Dax queries okay and cope I can just delete it so very important guys when you asked about Dax it could be a very generative question but is very important just to not be theoretical it's very important that you mention a couple of things like it's a functional language and give some examples of how Dax is related to stuff that you can do in the PowerPivot that is in the data model and essentially it helps you add more meaning to your data because our underlying data could be in a certain format but there are certain kinds of calculations or something kind of complex measures that you want to add which could only be done in the powerpivot layer and nowhere else okay and we look at some examples of this just in a bit of certain things that you can do only in the powerpivot layer and nowhere else you can undo that stuff in the ETL layer okay might sound surprising but I'll just show you some examples of why that is the case okay these are some of the common Dax functions and again this kind of goes hand-in-hand with the ninth question so when you asked about Dax just mention some of the functions again is goes to show the length and breadth of your knowledge of tax because any kind of job interviews on power bi Dax is an integral component because people expect you to be good in Dax right because it's not all about the visualization layer is not all about point and click click on a visual you get a visual knowing the underlying features which is fine but people do expect you to understand D the tax queries very very well and not only Dax even at some level the M query language also you need to be good at at least at least have a basic understanding so that if there the error similar to some debugging at least you're able to pinpoint on those issues okay filter function it goes hand in hand with calculate basically and this is again a very very important component in power bi desktop and something that he will use a lot and you know whether you know any other function or not this is one function that you have to know and have to understand okay and calculate function basically operates in the filter context I'll just briefly explain to you what this is and what are some of the use cases behind using it so what I will do here is I'll create a very simple table so what I have here is a is example where let's say I have here okay so I have your information I take order date and I'll take sales information okay so I'm seeing a bar chart I will convert this to a table and right now what you are seeing is a very nice tabular information right now what I want to know is a percentage okay so let's say the scenario is that I need to know okay right now what I'm saying is the total sales across the total absolute value of sales right but what if I want to quickly figure out the percentage of the total so for instance right now you're looking at quarter wise or maybe another good example will be taking subcategory instead of taking order let's take subcategory okay so right now what you are seeing is sales across different subcategories but what if you want to know what is the total percentage of pay per sales compared to the total so right now missing absolute value is how do you convert it a percentage and the way to do that is using a calculator function okay so you can just divide this by total so it I mean conceptually is very easy right so you'll take this number you would divide by this so you will take one zero seven five three two you will divide by this okay just to expand it could the focus mode now I hope you can see other numbers correctly now so two zero three four one two point seven three divided by two two nine seven so you're dividing each of the row values by the total at every step of the process right to calculate the percentage okay and that is something you can do using the calculate function here to build a Dax calculated column here okay there are some lot of cool ways of doing it and again there are some pretty cool ways of doing it manually without creating tax so you can go to show value as and you can actually say percent of grand total so this is the easy way of building it but remember even when you are doing this behind the scenes power bi is implementing the tax for you okay there is also another revolutionary feature called measures so again there are some lot of new things that you have in power bi is something called quick measures that you can also implement but again remember whenever you're doing any of this stuff behind the scenes you are implementing Dax okay you have some new quick measures if I go to new quick measures I will see a ton of stuff that I can build in power bi okay you can just take a look at this now and you can see a ton of subjectivity written to you here okay and again there are a lot of these things tend to be added from time to time new things tend to be released you can see time intelligence calculations totals running totals star rating really cool thing you know star rating it's an amazing feature so you can actually give a star rating highest value let's say my highest value is going to be something like 250,000 I'll probably say okay that's why values Ray Lewis value I can give the number of stars and I can enter a new quick measure called star rating okay and now you can see behind the scenes I've actually got Dax so whatever you're seeing here right now is actually a pretty complicated version of Dax okay so whenever you create quick measures or any of these default point and click options behind the scenes power bi is actually building that Dax for you okay this is a pretty complicated bit of Dax that's written and the final output is actually pretty neat okay you can see that that's my that's my star rating column I've created and you can see that as a top value five stars here four stars here one star here Spiti relative depending on the type of Decatur and and the best part is that it is actually going to be dynamic and what do I mean by dynamic what I mean by dynamic is that this value will change depending on the underlying data so essentially if you take subcategory and if you further try to break it by region that data will actually change so now now your stars will actually adjust based on the underlying granularity of your data okay now you can see nothing is our five star everything's like you know two stars three stars because the maximum that you mentioned is no one's reaching that other than the total obviously okay so what I wanted to highlight again is that behind the scenes although you can do this particular you can solve this particular problem in a very very simple manner but behind the scenes for me I will always write a tax for you so coming back to the underlying question once again so I am looking at my individual sales values and I want to calculate the the percentage of it so if I want to write a Dax formula for this the way to write it would be to go back and implement it manually using the calculated column option and I'll quickly go ahead and open up my wizard and fire in a quick bit of tax here so how do I do that and go to the modeling tab and I'll go to a new column click on new column because that is actually gonna be a sorry a new measure that's going to be a new measure and I'll just in one of the upcoming questions I'll talk a little bit about what is a measure and what is a column this is a very very important difference by the way between the two so here is gonna be a manager I'll treat and the measure is going to be called let's say sales percentage I'm going to call it sales percentage and let me quickly type in the measure I'm going to take the sum of sales and I'm going to divide it by what I'm gonna divide by the calculate of the sum of sales I'm gonna divided by the calculate of the sum of sales and here I'm going to apply what what I call the filter context okay and this is this is the important part is the key part and okay so whenever you use the calculate calculate basically has two arguments and as you would have observed whenever you start typing any kind of function in tax it has a pretty nice kind of auto completion or a help suggestion visa that comes up it tells you that hey what are the arguments that I need to enter in calculate and it very clearly tells me that the arguments I need to enter is the expression the expression is the stuff I want to calculate which is obviously going to be sum of sales that is what I want to calculate sum of sales is the underlying expression and then I have to get the filter context okay you could get the filter okay evaluates an expression in a context modified by the filter and this is the important part so remember when I am performing this operation I should ideally not have any filters right conceptually think about it when I am performing this operation ideally I should not have any filters and that's exactly what you're going to set here in this particular example so you're going to set this up in such a way that you don't have any filters let me go ahead and quickly set this up now so what I am going to do is I haven't go ahead and give all so when I say all I'm specifically telling power bi that hey no matter what filter context is applied on that particular row you're ignoring everything okay and I'm going to take all of orders okay I'm going to consider all of orders and I'm just gonna go ahead and close my appearances here okay a missed closing the brackets here let me go ahead and quickly edit the syntax and whenever you make up in correct syntax what happens is it will give you this red squigglies which will highlight that okay so the syntax is not right as you can see this my tax formula right now and just to clarify once again calculate the way we are using calculator is with calculating sum of sales it's the first argument and they're calculating sum of sales based on the filter context of all orders okay that is where you you mention the filter context so you're you're saying that I will calculate the total sales irrespective of the filters applied so I'm taking all of orders so you're dividing individual rows by the total that is the underlying meaning of this okay and I can actually go in and multiply that with hundred just to add clarity on this whole phenomena so because it's a percentage I'll just multiply that 500 overall and now I can go ahead and add that piece of code right into my table and see the results okay as you can see I was very easily able to achieve that using a very very simple piece of tax code involving calculator okay so this is just one use case of how you use calculate very very important tax formula calculate and filters and something that gets asked all the time in interviews okay and the best part about this is is dynamics so see how I've used subcategory here if you want to use something else let's say what to use a region you can do that as well so I'll take a region and remove the subcategory and see how that entire formula are just automatically depending on the kind of calculation using okay the kind of grouping that you're doing and you can see the region percentages are highlighted everything adds up to hundred percent it comes up pretty nicely overall if you don't like region if you want to further spread it by segments you can go ahead and spit it further by segments and see how again the entire calculation gets dynamically adjusted okay and this is again the beauty of measures okay this is again the beauty of measures which the very fact that meshes in the dynamic in nature it is dynamic in nature and it will basically adjust every time you change anything individuals change anything filter anything your measures will always recalculate okay and measures are always weighted with this small calculator I can okay what are the benefits of using variables and X as a variable syntax are no different from variables in any other programming language obviously Dax is another kind of functional language so variables one of the most important use cases is that it can be reused multiple times so as to avoid any kind of duplication or redundancy in your overall code right there are some other examples that we have on you can specify what are some other time intelligence functions again calculated something that you can use for this so we talked about calculate all in filter and you can obviously use them in in multiple ways to go ahead and create n number of different use cases okay this is one use case I mentioned but you can obviously use calculate to create trading X month matrix fired acts like it's a non-standard calendar for instance okay and you can see what we are doing here is a second step is we're using all again to remove existing filters okay remember the concept of all is you're still applying the filter context but the only difference is in all what you're saying is you're removing all the filters okay what is the calculated column in power bi and why would you use them and this is the part where I talk a little bit about calculated columns and an example that I will show you guys here is related to lay say profit percentage so what I will do here is I have a measure that I created here come and delete it so I have a cost column what I will do is I'll built a calculated column so you build calculated columns using the new column option you build measures in the new measure option so I can build calculated columns using this option here and column name will be profit percentage what is the formula for profit percentage it is going to be cost by sorry profit by sorry I think I've selected the wrong table okay so yeah so one problem that happens is and this do I think good that we call this error so one thing that happens is you know when you create the credit column remember that you're clicking on the right table so basically and trying to create a column on this table and I'm not able to find the profit column because obviously it doesn't exist into my bonds table so obviously I'm trying to create a table a column in the wrong table okay so I'll go back and delete that from here so ideally the profit percentage should be created in my orders table right so I'll go to my orders table just click on it once and that should pretty much be enough to select that table and click on new column now click on new column and that means that now that particular column is now see can see that that's not part of my orders table okay it by default comes with the column name of column but I can absolutely go in and change it to some like profit percentage and profit percentage is gonna be profit and now as I start typing profit you can see automatically profit will come up orders of profit and so basic syntax that is applied so orders is my table name and profit is the column so that is profit by cost that's my presentation profit percent right so profit by CP in 200 so that's my calculated column everything looks go okay up to this point and what I will also do is additionally I'll also create a measure okay additionally I'll also create a measure conceptually there is a lot of difference but syntax wise there is very little difference so what I will do is I'll create something exactly similar only difference is this time I call it profit percentage measure see all I have done is I've copied pasted an entire formula so that sister measure and the only other thing that have to do for measures is that we aggregated so that is the only other additional syntax difference in measures and I have to pre aggregate my fields in measures okay because without aggregations measures have no meaning inherently so that is what I am going to do sorry one small thing have to change so 100 is basically would come outside the sum that's my measure so what I have is a profit percentage calculated column and the profit percentage measure you can see that the difference in icons now conceptually what is the difference that is actually or not and as an example if I show you region and if I basically want to know what is my profit percentage across different regions if I just try to see the profit person is using calculated columns look at my number is that even a valid number I mean a 300,000 percentage is that a total profit percent answer is wrong that is absolutely incorrect data okay so even if I want to look at this data across in in a granular level across different segments you can see it's the same problem I get very very big numbers which is apt which is definitely something wrong with my data but the moment I take something you make the measure if I take that measure here everything looks perfect okay things look perfect now okay the measure is giving me the right result whereas the this is giving me the wrong result and why is that so and that is basically the key difference the key conceptual difference between a calculated column and a measure okay I'll come to the performance aspect when to use what and obviously when you answer this question on measures and calculated columns performs aspect is also very important but you should always start with the conceptual difference first what is the conceptual difference what is actually happening in the model and what is actually happening in the model is when you look at the calculated column all that it is doing is it is basically summing this up it is actually aggregating all the data that is already there in my model okay in other words when you go back to the model I will look at the orders table if you scroll over to the right you will see that profit percentage is actually calculated and stored every's for every single row in my model itself okay so whatever column you are looking at the end here profit percentage is basically a calculated column that has been calculated for every single instance of the row okay and that is stored in my model so all the time doing in the table is I am aggregating all these values together which is obviously incorrect right so essentially what it means is if I look at row number let's say if I look at row number one and two and if I look at all the 20 rows which are all Western okay essentially what I am trying to do is I'm trying to add up all these profit percentage values for West ok it's just like doing a group by region and some a profit percentage which is obviously incorrect ok it is obviously incorrect and if you look at my table that's exactly what I am doing I'm actually doing a sum of profit percentage across all those she's actually incorrect okay so that is the problem that is the key problem and that is exactly what you need to fix here okay and even if you try to do up however as a profit person you might be wondering that okay okay so obviously if I try to do a summer profit person that I'll get a problem so why don't I just go back here and say this to average even by doing this you will get incorrect output okay you will get incorrect output even if you do this this is not the right way of building it okay the right way of building this is to actually do it using measures okay and that is exactly what I want to highlight here and and the other thing that you would have observed is in measures they are actually not stored in the model measures are always calculated ad hoc dynamically on the fly measures are never stored in your model okay your model calculates the value of the calculated column and it stores them on a row by row basis in the model but it never stores the value of your measures measures are always calculated dynamically on the fly okay so if you just look back at this particular example here you can see these measures are always recalculate so when I go back and remove the segments those measures are actually getting recalculated on the fly okay so I'm actually recalculating them on the fly if I just go back and take something else let's say instead of region if I just go back and take a category it's actually getting recalculated okay so that is an important thinking about measures that you should keep in mind okay and that is the other reason why when you build a measure you can just see that in measures because they are not calculated on the fly because they're actually not stored in my model you have to define the aggregations here okay they are calculated dynamically based on whatever filters you apply whatever stuff you do they always calculate dynamically okay and they are responsive to filters that is the other important thing they are responsive to filters okay so for instance if I go back and put some filters you let's say I'm looking at category and I can actually further split by subcategories okay and now if I go back and put some filters let's say I go back to region and I'll put some page-level filters here for region and if I if I look at for central data you will see that now they're responding to filters okay they're actually going to be responsive to fill dozen sphere generally okay and they're recalculating every time you're changing anything in editor they're recalculating based on the aggregations that you've defined okay so couple of key things that you have to keep in mind when you talk about measures a is they are not stored in your model B is they are dynamic they are always calculated on-the-fly dynamically as and when you're building your visuals and C is obviously they are particularly useful for calculating any kind of data which is pertaining to percentages so any can anything that represents any any kind of denominator okay so divisions particularly are a very very common use case where you will use measures because divisions are very calculated columns will fail all the time okay imagine building something like this in your query level okay you cannot do it you cannot build a visualization like that in query layer okay so for instance if you write a perform this profit percentage calculation row by row it will not work okay not work and in this kind of scenarios where you have some kind of percentage is some kind of denominator some kind of division involved you should always use measures okay and again measures you should be used for complex calculations and the other really really powerful aspect of measures is that and as we have mentioned in the slides here as you can see that here calculated columns are not compressed and they consume more memory and the reason why they consume more memory is because as I say they stored in the model and this is where again measures win out okay measures win because measures are actually they're not stored anywhere okay it's more dynamic they're not stored anywhere they take less storage and but but the obvious disadvantage of a measure is that because they are not stored anywhere because the dynamic because they calculate ad hoc on-the-fly they probably put more strain on the resources right so I'll probably take more time to evaluate so as of today the differences are hardly you know he'll hardly feel it it's almost negligible because some of the performance improvement sort of taken place but generally they'll give us roughly the same performance but still measures do take a little lesser time comparatively because as I said just put more resources and as you can see the last point that you mentioned is they can also reduce processing and refresh performance if applied on large fact tables and can make a model more difficult to maintain our support okay given that the calculated column is not present in the source system that is another important point I should keep in mind power pivot I think you've spent a lot of time already on powerpivot discussing the components at a very high level already but when you ask the question on what is powerful but is very very important to link it with Dax so again it is a it is a discussion on one of the components of power bi so I think it would be really appreciated if you all if you could just spend like you know a minute to go over what is power bi talk about the self-service aspect okay bit about some of the components of power bi and then come to talk about power pivot but the fact that it's the is the modeling layer the fact that you have that X velocity in memory corner is that you're maintaining and in essentially that's the layer that you actually built tax calculator columns and tax measures in power bi okay so mention those pieces along with that another thing that I wanted to mention is the data model quickly data model obviously is the model that you that you're building after after you're performing the query editing and all that stuff the place where you're loading all your stuff into is what we call data model and data model consists of tables and obviously tables are consisting of columns and rows and essentially you have relationships okay these are the key aspects of a data model and just to briefly explain this whole piece to you and what I'll do here is I will be connecting to my excel once again and I'll just be bringing in one more table so what I'll be doing here is I'll be quickly going ahead and connecting to my excel and bringing in one more table called returns just to demonstrate to you guys what is the relationship and how to quickly build a relationship and you can see it's the same process and only difference is that I'm going to bring in these two tables people and returns together okay I'm just gonna directly load them is he processing queries and I've loaded the data into my model right now it is loading my data so this is a step where I'm loading my data creating connection loading director model you can see these steps and I have to make a small change actually I could awaited queries and my data is actually not in the right shape so I need to go back to returns and it's a quick thing I'll change here let's go back and make this first throw as headers you can see headers on all in the first row very very simple ETL operation I'm going to perform go back to people and convert that to first first four letters I'm not really using bonds so I'll just go back and you know go back and remove bonds delete their pawns and close on the Politis okay so what I have is the end result is I have three tables remember and query teacher they're called queries but the moment you load that into the model that is power pay but we call them tables okay so everything is a table now and if you go back to the tables tab you see the three tables that are present here obviously orders people and returns and if we go to the relationships tab you can actually see the relationships okay so power P I will do a pretty decent job in automatically creating relationships based on column names so if there are two similar column names probably will automatically run with them but then it will not do a very good job always okay so for instance here you can see region and region automobile-related just double click on the relationships tab and you can see the relationship region region related you can see the types of relationships cardinality many to one one to one one to many in typical standard relationship types and it's an active relationship that's something you can turn on and off ok and you can also exit birth returns it was not able to relate then the server can manually do it order I resist it's as simple as a drag and drop and now we have related orders and returns so it is very very important to put relationships guys and you know it's always a consideration that you have to always it will decide whether you want to keep everything in one table or you want to go back and keep things in separate tables it's always a very very key question that you might get so at what level do you decide should you normalize further at what level do you decide that so that that's a very tricky question and I think there's no easy answer to that but it really depends it's just like so many other things it really depends on a lot of different scenarios because you can decide to cap everything in one table so for instance if you look at this particular use case you can decide to have orders people and regions returns all in one single table okay you can just go to queries eater to emerge queries you can just join all these three together and have everything in one denormalize table if you know look things are good but the obvious disadvantage is that it's gonna be a big table it's gonna take a lot of space and that's not a good thing okay I don't think on the contrary you can keep them in separate tables but the obvious problem will be that you know because the relationships are there whenever you're trying to query data across you know when they try to visualize something across three different tables this is going to take a little bit more time compared to when you're trying to do it in one table okay so there are obvious frozen constant both approaches but it's important to understand relationship very very important and mostly in power bi as I said you would mostly have these normalized scenarios where you will typically have a lot of these split out tables and you will typically have to combine them with relationships okay and why is relationships important because without relationships you cannot visualize data across multiple tables so for instance you have orders here people here read returns here and if you go to my visualization layer now I can up to see sales by the and you can see sales have selected from the orders table I can go to the people table I can specialize that by person okay I can actually visualize that by person remember I have selected fields from two completely different tables and I can actually filter that whole stuff by the return status okay pretty cool isn't it and this is something I could not have done this if I have not related the tables okay so you can see the visualization that I have here I have selected fields across for three different tables and this was possible only because I've related the tables if I've not related the tables for acidify it not related my tables if I just go by I can remove the relationship here if I make them independent tables without any relationship you can see that it will give the same values at times power we will give an error like this and at times it will actually go back and say give the same value okay so that's an important thing to remember and understand exactly why relationships are required and these are some scenarios that you should actually point out when you talk about the data model and what is power pivot and when you talk more about relationships okay so the X velocity in memory analytics engine is something I talked about that says basically the underlying engine behind power but that's something that drives forward it can handle a huge amount of data because at the underlying layer it is nothing but a column database okay it stores data in column and interfaces and Colin area base as some of you may know is a very special kind of database which is optimized for storing huge amounts of data and overall data access is very very fast in the column it is it doesn't maintain data in a typical relational database format where you know data is stored in the of rows and columns typically the way we understand it but column interface is a very very special way of storing data and I'll encourage all of you to go and look at it more and if you can stress a little bit more on column database when you talk about this particular concept it's just spending a bit of time talking what is about what is the column interface because as I said everything about power pivot and the in-memory engine of power pivot is based on columnar database so to talk about the column interface mention a bit about what it is and if you can get into those aspects of why data is fast y axis is fast nothing like it but it's not recognized and that's getting too much the advanced aspects but that's not required but it's an optional thing that you can take a call on our relationships obviously there's an option that you can set even either have one active relationship you can have multiple active relationships obviously if you go to the relationship types here one very very common use case of this would be order dates the date to date think ok now order date ship date due date and a very very common use case of this is especially if you are dealing with her own plane dimensions and this is a very very common synergies that you should mention role-playing dimensions is this term that I should mention where you can only have one active relationship okay very important that you mention that guys which role playing dimension you can only have one active relationships okay and how do you make a relationship inactive so we talked about that already let's say region and we generated here it's a solid line can actually double click on that and you can make that a relationship inactive click on ok and that is an inactive relationship our query I think I spent a lot of time on this already so needless to say when you talk about talk where he mentioned a couple of key terms ETL tool shaping cleansing transforming data very very important pieces and also mentioned the M query bit that every queries is a combination of steps and you can build multiple queries and ultimately the underlying layer you're writing M code ok query folding is actually another very very important feature in power bi and obviously it's more of a performance enhancement question that that can get us very very important question actually that that sometimes tends to get are steered from a performance optimization standpoint and query folding basically leads to the fact that the kind of operations that you can perform at the source get transferred to the source ok so at a very basic level you can see that there's something called a few native query there will be something called view native queries that you will see so if I just quickly go ahead and and and get some data from sequel server here real quick I'll try to pull it some data from sequel server I think this is also the first time that you are seeing how we connect to a sequel server like if it dot as my server name and I'll try to connect down to my local instance I have a database called adventureworks as a demo so I'll connect to my adventure works database and here I'll go and connect to let's say dim customer or let me go back encourage dim product that you dim product click on ok and there goes my sequel server table and if I try to add in some columns here let's say what I'll do is I'll try to remove all this stuff ok try to remove all the stuff that I have here remove columns obviously these are the things that is going to work as expected just like as we understand steps are going to be created I can go back and remove all this stuff from here ok I don't need this so I kept only three columns and what I can do here is further thing I can actually add Kaunas whatever and the idea here is to show you something called view native query and what happens here is that I've actually got data into power bi take stuff not ok you remember I connected to my sequel server I caught it into power bi desktop and after that whatever transformation I perform bi performed is within power bi next okay so whatever transformation I did I did it with a power bi desktop but using query folding power bi will actually transfer that operation to my source so what is my source my source is my sequel server so instead of performing this operation within power bi at its instead of performing this in memory inside power bi I will be performing this in database okay so you're transferring in memory operation to an india-based operation now that stuff will be performed in your database that is in your sequel server and there is our tent will be returned so if you just go to right-click on that and go to view native query you can see that this is the resultant stuff that will be returned from my underlying table from my database okay it's a very powerful feature and what it means is that when you're actually getting the data into power bi you're not going to get all those 50 columns right if you need only three columns then power bi will bring in only those three columns and actually as I said it's a very very powerful feature and it not only works with removing columns you can actually go back and remove columns and you can see that at this layer also if you go to view native query it actually gets renamed as eeriest gets applied okay so let me give some different name here like color name let's say English products I'm going to call it let's just gonna call it products name okay and here I'm gonna call it product ID so actually named all my columns here and as we understand in as part of the sequel language when you rename a column you all you are doing is actually applying an alias and again as you can see although this step I'm performing with in power bi desktop you know probably a tech stuff will just go back and offload that operation or transfer that operation back to the surface the sequel server and if we go to view native query you can actually check that okay again it's a very very important piece that you should keep in mind if you try to do a split here let's say I try to do a split here split by a delimiter okay and let's say I want to split this by a space and click on OK and see how I split that stuff and if I right-click on this now you will see that punitive query is disabled because in the native sequel language in native sequel server this operation is not supported so up to a certain point you can do query folding but beyond a certain point you can't do query folding that is another important thing to keep in mind so if you perform any transformation in Pro query editor you know for which query folding is not possible obviously punitive query would be grayed out in that case I mean obviously that means that at the native database layer that particular operation is not supported so that's not the important thing to keep in mind ok what are some of the common transformations are the very very important question that might get asked at times because just to test you your basic level of knowledge changing data types very fundamental thing will do it always all the time you know another thing that I will add is adding header rows you want to basically modify your header rows filtering grows not a very useful thing typically if you're connecting to excel data or some kind of CSV sources you know initial few rows might just be you know given a she might just have it's a some kind of email some kind of introduction headers although stuff and you'll always want to filter out rows initially columns such an important thing you don't want to see all those columns right if you're 50 columns you don't really care about just take out a three or four columns out of it very important option grouping aggregations again very important splitting another very common kind of transformation that you might want to use okay if something limiter or some characters based on which you want to split the columns out subsets something out it's a very very important option over adding new columns needless to say it's something that you do all the time right can sequel and talk where you be used together I think I just answered this question in this example here answer is absolutely yes you can and the best part is you don't have to you know obviously can do it is in the graphical user interface or indirectly query type out your query you can straightaway type out your sequel query here more customized sequel query across multiple tables what our query parameters query parameters are run again a very very important topic in power bi and query parameters the whole idea is that they are very similar to filters but there are more dynamic sort of filters and the way you set them is using the parameters wizard here I will go back to my orders query right now you go to manage parameters and set up a new parameter called region so I can actually define a parameter like this it's a region parameter type is going to be text type okay so this set values is going to be let's say I'm going to give a list of values and when you say any basically you get a you get a text box and if you give a list of values you get a drop-down that's the only difference I'll show you both the examples okay current value is a default that you can give actually and go back and put in the central alright so as my region parameter have set up here and now what I can do is I can go to my orders table and see how I can filter by region here in the query I can go here I can filter by region I can't exactly type out a value here instead of doing this what I can do is I go to text filters I can say equals and here I can go back and select the specific parameter type okay and this is really the amazing thing about parameters so now instead of saying equals to a particular region instead of typing out a value here I can go back and say it is equal to the parameter region okay so now that filter is actually equal to that parameter region okay and what is the parameter region equal to remember the parameter region equal to was data central so now that central parameter is set to central and that is why it is filtered on central look if we go to the region area C has filter on central right now and it's very easy to change it by the way go to edit parameters go to edit here and see that text box comes because the list type was any is actually set as a text box and actually I can set it to something else it's a East and I guess he's automatically going to change it to East okay that's the real beauty of using parameters generally you just to go back and click on that interface once to for it to reflect just to refresh it once for it to reflect and now I guess he's ready for it against easy if you go back and set this as West the same cover cans it is as waste and if I go back to orders no you should be able to see that now the entire thing is automatically filter to a so you can see how dynamic the whole thing is and remember this is not only at the query editor level even the visualization layer in a very using parameters when you actually loaded this query in you can take it straight away go to edit edit parameters so something very similar to what we are seeing here you will see in the visualization layer also you will see in the modeling tab so here you will see the edit parameter section coming up here where you can go back and directly modify your parameter straight away from the visuals itself and one other important thing is the other use case where this is actually required and the question that we're asking here here is probably a templates the other other thing that you should all way what is the use case of this as I mentioned the use case of this is one very common use case of this is you don't want to load all your data right so if you have data for all the four different regions as central east west and south you don't wanna know all your data right you can have hundreds and thousands of rows of data but you don't really care about on it it right if you want to look at data only for West then you want to select West you want to see it only for East it is not only East right but what you don't want to do is you don't want to select all your data you want to get all your return to power BX turnoff and then you want to filter okay there are two options right one option is to include all your data load all your data and then put a filter the other option is first you give the user a prompt and based on whatever option they select in the prompt you go back and load editor and and and the second option is obviously the parameter approach which is preferred any day because you're you're loading in only the requisite amount of data that it require you're not loading in any additional data and the best part about the second approach is again as I said you're loading in only the amount of deterrence requires obviously the amount of litter that you're loading in is very very less and obviously the performance will be much better so another common use has is redic right so if you have data what's up for the last ten years and you don't care about that ten years later I'd say you care about only data for the last one one so you always give people a filter option or in this case when I say filters I mean parameters right so you configure filters with parameters and I'll get people get a prompt with even the open of the - for they get a prompt and when they want to view the dashboard they can actually have to select the rate you have to go to the prompt select the date and now depending on what they enter data will be filtered and from the source only that data for the last three months will be picked up okay so that's the use case of parameters and how parameters are different from the normals standard on filters Flitz language in the power query we talked about it already M code and it's very important to also mention a bit of background whitney asked this question just talk a little bit about the background what is power query why do we need power query MN powerful but can import data from mostly sources and I think this also has been answered before I have talked about this as well previously but it's also important when you get this question to give some context on what is power query and what is power pivot and essentially mention the part that although yes you can get data in PowerPivot but it's also very very important that you address the part that it is powered query that it's an ETL tool okay power pivot is not an ETL tool power pivot is only used for loading data into the model that's it I mean essentially power P but can only be used for performing calculations and analysis ok the whole point of power pivot is analytics ok it is not for determining ok if you want to perform data cleansing that is not power people should also to sort of to add in components the two tools have two very different rules and that is the thing that you to bring out in this particular question power map so obviously is the mapping interface of power bi so I just got to briefly focus a bit on this particular piece obviously maps are extremely powerful in power bi desktop and if you just go to the map section one very very important thing guys you have to keep in mind is when you're configuring especially working it maps is to set the correct you graphical type so if you go to modeling you have data category something called data category it is very very important that you when you have inherently have data or like city it is very important that you go to the data category and say this is the city ok extremely important that especially when you're working with map data you have to set this a geographical data type and when you do that you will see a small globe sign will come up ok so now it signifies that city is actually over data category City which is a geographical type and a small globe so I will come out do the same for country as well do the same thing for country and I will do the same thing for postal code and see how postal code is actually summarized so this is this is another thing that will happen from time to time in power bi which you have to fix in modeling so sometimes some things will be summarized so obviously postal code is something I don't want to summarize so and we actually go to don't summarize okay because if I give you two postal codes 5 6 0 1 0 2 and 5 6 0 1 0 3 you can't I mean this makes no sense to say the average postal code is 5 6 0 1 0 2 point 5 makes no sense right so never summarise that kind of data go to postal code and set it's a postal code as geographical type see the globe sign will come up and finally the only thing that's remaining here is state I go to state and set a type here a state and once that is done I will go ahead and okay you can see that it says apply changes and the problem here is that I've actually made some changes to Madeline query if it is something it's okay I don't know if I can load it right now so a little italy straight away go to the maps and there are two kinds of maps mainly in the desktop interface obviously there are a lot of custom be shoes that you guys have so just to give you a sneak peek into custom visuals real quick and one who spend a lot of time on that just to give you a sneak peek on this whole piece I have a lot of custom visual maps where you can do a lot of cool things but at a basic level obviously you have only the kind of maps that you are seeing here right now I'm going to quickly go ahead and close that query because I predicted my sequel server it is creating a bit of issue okay so next step is I'm gonna go ahead and quickly replicate and just kind of repeat what I just now did someone quickly go around said this to city set by state to the state type by postal code and finally recorders at my country okay so the idea again is that I wanted to highlight here is the concept of setting geographical types correctly because this again when you mean you try to visualize this data finally in maps I just ensures that you're that you're more accurately able to represent it up now remember power bi will do a pretty good job in understanding exactly what a particular sting is so if it's a city name if it's Delhi Bangalore Mumbai power bi knows it is a city so you know I don't have to specify detail it's the city but remember if it's something that probably is not able to perfectly recognize in certain cases it could be very very useful because remember the column names are not always going to be exactly what you're seeing right now although it's recommended that you use proper column names but there I mean over the case and again the underlying data may not be exactly what probably is able to recognize so those kind of scenarios is very helpful if you set data category security remember even if you don't set it properly power B will still display it interpret properly okay but then there are certain use cases where it will not come accurately okay and I can go ahead and represent this in a map and all have to do is just take my country and take my state I look at my city and see how Bobby is not able to do it the best way all the time so sometimes it will fail and I represent the state of based on sales so sales is basically going to be my size let's say I get further to work on a saturation based on sales okay I can look at this data across and all you're seeing right and you might be wondering what's exactly happening why am i only seeing you know western United States and the reason is because if you remember I have filters applied so the underlying model that I've taken I have filters applied based on parameters okay and again that is something I can further configure within power bi okay just to quickly clarify this once again where do you set the very parameters remember parameters is something I apply just a while back and you can further look at the visualization layer you can further go to edit query is go to edit parameters you know how to open edit query let's go back here and say this as east or west or central whatever you want to say this is east now click on OK and you can see now this whole thing will automatically go ahead and convert to East okay you have to apply the changes because remember they're actually doing is a query level change so idea is that when you when you set this as East it will go back to the query it'll take all your data it'll take away Eastern data and now it will show you only the Eastern data okay so that's the quickest idea now now Italy she's only showing you Eastern data in further go to edit queries now you go to see the central data I just just type in central here and are you see how they central data okay so changes for the filter to actually go back to the source and get only the relevant central data and this is the part where I mentioned that at every point in time your prompting the user and again it's a very very good performance optimization technique that you can use especially when you are dealing with very very large data sets okay if you have these things I've already talked about these are a few additional questions that we have Power View power bi designer can be refreshed at the pool reports power bi reports this is an important question again this is related to shitted refresh videos are you will want to refresh your data right you want to typically have a schedule and when you're when you're publishing your rate on the cloud or a dataset section basically I have an option for Shirov refresh and this is exactly where you can go ahead and set up your refreshes specifically for the kind of data that you have and you need something called a gateway right now my gateway is not configured but you need something called a gateway to set up a CD refresh remember if you are connecting to an on from my source especially a sequence of a database or something like that on from my source then you need to have a gateway and you can actually download your gateway from here to configure it further right and you can also go to the manage gateways tab to basically go and manage your gateway so if you already have a gateway installed and you want to go back and set your gateway specifically so this is one other place where you can go back in and check your gateway everything is working fine there okay so it's important that you mentioned a couple of these things that obviously yes you can refresh there is something call to get you refresh and also mention about gateways gateways are that they're the interface between the cloud and the on-premise world okay so gateways are what allows you to transfer your data from on through my sources to your Azure cloud where your cloud server does the power your services hosted what are the different types of refreshing data package refresh model refresh tiles refresh and visual container refresh so this is another important question so typically when we talk about refreshes how many ways going to refresh the package refresh is basically the concept wherein let's say you have a per gate extra file or an excel file which you have in the service and I mean obviously the same file is there in in onedrive or SharePoint Online and you want to ensure that they're both synced essentially whatever data you have here you have there and vice versa and that is something you do using what we call package refresh ok that ensures that whatever files you have in your power bi service is exactly in sync with what you have in the onedrive or SharePoint Online remember it is not to pull data from your so this is not like the typical kind of refresh that you're thinking so that is basically going to be the model or data refresh so the refresh that you are thinking or the refresh that I showed you here is basically the model or data refresh okay this is basically what we call the model or data refresh okay that is what you do using refresh now or schedule refresh okay then we have something called IU refresh which is something that we we can go to the different - ports and as you are understanding dashboard so whatever stuff that you see remember these are just views of the underlying reports okay and if you just click on the tile sending it will take you to the underlying report so - ports are nothing but cash sheet fuse or the other line reports which typically get refreshed every 15 minutes or so okay but if you want to specificity refresh a tile you can go back and say refresh - 4 tiles okay that is how you refresh the dashboard views to reflect according to the data changes which anyways get refreshed every 15 minutes okay those are the types of views refreshes that you have is available on premises answer is absolutely yes then when you answer this question is also very very important for you to specifically mention about the gateways and also to talk a little bit about the kinds of sources that can connect to okay remember for power bi to connect to cloud sources it doesn't need a gateway so if you have something living on as your if your something living on an online service you do not need a gate if your something new in SharePoint Online or onedrive you do not need a gateway okay so even if you want to perform a shit you refresh you don't need a gateway so gateways are only required if you want to connect on to on-premise source either to perform a scheduled refresh or to get it and repeat from a direct query okay which is nothing but the live connection essentially what is probably I Q&A and power bi Q&A as I briefly showed you some time initially in the discussion was it's a natural language query tool so you can just type in queries and essentially it just gives yourselves on the fly it's not that intelligent right now probably it's remember it's still a very very preview feature it's a feature under constant development but it's fairly accurate it's pretty good overall and there's a lot of artificial intelligence built in overall there in how the tool performs especially in the app and I don't know if you guys have used the-- just to take some time it may not be an interview question obviously but take some time out to explore this on the power bi app and probably I cap what they call rolled out a feature called conversational Q&A okay so here is Q&A but which we use the app they have something called conversational Q&A where it is just like as if you're having in conversation with Google assist Sealy something like that you know just like you're having a conversation asking the hey what is the sales and they'll go back and respond something and then you can just go back and carry on the conversation from there you don't have type in a new question every time okay you can just scary on the contrary just like having a conversation with a friend this pretty cool feature overall and remember this power this feature is there as a in in the service but it is there in the desktop as a preview feature which is something you should also mention okay so uh what are the ways exhale experience can be leveraged with power bi and there are a couple of things that we can do in Excel by the way which I wanted to briefly talk about and just to discuss about this piece what I want to do is work with the open up Excel so there are two things particularly that you have in Excel now one thing we call the power bi publisher for excel obviously the personal gateway and the entre - gateway the gateways that we have but there's one additional component that is worth mentioning called analyze in Excel of ditch as you can see there's something called analyze in Excel update so these are both add-ins that you've trained stall in Excel so analyze in Excel allows you to go back and raise a dataset in power bi from it in Excel okay so if you have a dataset in probably a service just go ahead click on it okay you can click on it and it can basically just say analyze in Excel and when you do that basically you download a file you download an OD c file OD c file basically stands for office data connection file just going to show you what that office say declination file is and how it looks like so this is what an OD c file looks like okay if you guys see my screen right now you will see that it's an office data connection file so they it's an OD c file right click go to properties you will see some OD c file okay and now what i can do is i can directly see this double click on it and it'll open up with an excel and it'll straightaway open up in the powerpivot interface okay what I did was I took data from the datasets in power bi service and I now I am analyzing that straightaway the next sale and see how it opens up okay that's my orders table and see how it comes up now remember in my original service also I had only three orders table okay so now I'm looking at the same orders table and I'm analyzing that table within my family a pivot table interface okay fine so this is a great feature that we have in the power bi the overall interaction and overall the interaction that you have between Excel and power bi sister phenomenal generally and a lot of these things tend to be included a lot of these things are happening where there is more interactivity that's been built in but the overall concept is that the integration has to be seamless between both the environments right so you can very clearly take data from service from the power bi to excel and Excel to power bi and vice versa so that's that's the seamless integration that that exists right now between these two tools so so right now we have learned how you can get something from the service onto the excel for two other way around from excel to the service essentially from excel to power bi how do you do that and for that you need something called power bi publisher for Excel which I have already ate at it as an added and remember again for this you need to go ahead and sign into your service and I can just go ahead and hit a publish now and all that reduces in just pin it just usual way I can print to my dashboard just a very simple example aaaa like go ahead and pin to my existing dashboard and he's published its pinned to my dashboard now and if I go back to the service if I take a look at my demo I can see that that a is now pin then you can see how how seamless integration is and how these two environments power bi and exhale are talking to each other okay so it's very very important that you mentioned some of these use cases the examples and if you can do that if you can highlight some of these things will be really appreciated then just giving a theoretical answer to give some of these examples and use case and what okay these are the XLVI Haddon's I spent a lot of time discussing this so I do mention some some some some very very brief about all these different tools that you have how is data security implement power bi this is actually a very very important question especially which pertains to low level security or lists as we call it okay and the way you do that in power bi is using the concept for roles okay so you first do this in the desktop so for instance right now here you can see I have data what's the first things I'll do probably here is remove that parameter okay and code and remove that parameter reference by the query so I can't remove obviously cell to go back in first go ahead and quickly remove that filter and now go back and remove that parameter okay and close on the play so what I'm looking at right now is or the entire united states data because i have no filters applied so everything's perfect i can go to manage rules and the way I do this is using something called manage rules okay so if you go to the modeling tab you see something called manage roles in security go to manage roles and here you will have an option to set roles okay so what I will do is I will want to create separate roles for separate people right so let's say it's a big company that I have as I operate and you know I have different people from different regions I have central people like eastern region people have western region people so central region people should not be able to look at data for eastern region people right if you belong to a central region if your manager for central region you don't care about eastern region of western region right so it's probably confidential data or whatever the use case but you only want to focus on data which is central so what I will do is in that case I will basically create rules I will create what I call a central role and I will create what I call a eastern role and as a next step what I need you to do is enter Cooper can write some basic tax and what I will do is require and type in my quick expression I'm going to say region is equal to central and remember this is a role I'm setting for a central role the tax filtered expression is always going to be a filter expression and the result always has to be a true or false right so this is going to be region equal to set and for the remaining I'm super hit save and for Eastern I'm going to put it as region is equal to Eastern okay so see how I set up two different roles here one is for the central rule so if anyone is viewing it from the central region now this particular filter will be applied by default and if anyone is viewing it from the eastern region this particular filter be applied by default okay click on save and now the DAX phone has been configured okay one way you can preview this feature is by clicking on view role as okay and the good way to do this is just to go back and put some additional information let's say I'll have a region here and have region wise sales and profits mentioned here just to give some clarity on the exaggerator that you see and now if I go back and click on view roles as or view as roles this is the way you can preview your stuff okay that how the central people will view this they're looking at only central data and how the eastern people will view this now they're looking at only Eastern data ok so this is how you can go and see that stop viewing as option will come up you're actually viewing it as the role of Easter so as a developer you can preview exactly what people will see when they see your dashboard ok so it's a very very important feature now all this is good so all that you have done right now is you have implemented this in the big stuff how do we publish this out so obviously we already understand publishing so all that you have to do is to obviously boku-kun save it first and I'm going to go into a finish this out save it in my demo file so remember to obviously to implement this in the service you have to publish it first so right now I've just implemented my desktop so the final step is I have to publish it so I will publish to my workspace so replay speakers exist another dataset with the same name is all details it will by default replace because they both are the same names right and once I do that I'm at my service no and you can go to datasets now you can go to manage security there's a security section here and here you should be able to add you should see the rules present iam ok this is very can basically no Cobra can add the email addresses of all the people and remember this further will be linked to your ad groups right because remember your configure it depends on the admin settings how you set it and all that but basically you can type email addresses of all those people who are belonging to the East role and email addresses of all the people who are bound to the central route now if anyone who is in the eastern role and if they try to view your dashboard now they will basically be looking at only Eastern data because I've said that filter remember region is equal to East and if anyone who is belonging to the central role is trying to give a dashboard they will look at only central data ok and that depends on what email addresses you enter here ok so as an administrator you can basically go ahead and enter all this stuff and that is how you add a very very simple layer you can implement low level security remember I have done it in a very simplistic level but this could be a far more complex tax expression so he can come back here he could probably go back and write a far more complex expression ID I said it has to be a boolean by as the end-product power bi does support mobile so there is a power bi mobile app which is supported across multiple platforms and many many relationships are something that's another very very important point that it tends to Gus gets asked a lot so does it support many to many relationships answer is actually no it only supports one to one one to many and many to one but many to many if you have to implement you have to implement using what we call bridge table okay so that is something you have to specifically CREP legate or create either in the model or in the power query so first create that bridge table a one to many to many to one a very good example could be authors and books one author can write many books one book can be written by many authors or doctors and patients also a very good example you know one doctor can has treated multiple patients and one patient has been treated by multiple doctors in both these cases you have to create a bridge table ok and then you will set up the one-to-many relationships in power bi desktop that is how we will typically implement this follow the I publish for excel we have talked about this already the differences you have briefly talked about edit interactions typically work at the the visual layer of power bi another very very important component especially in the visualization stack is edit interactions so here this is my is a very simple bar chart I have I can parallely build line shot here and now a line chart is going to be showing my sales based on order date let's say very simple line chart I have here so the idea of edit interaction says you want to specifically choose how you want these to interact with each other okay so I can further have that map okay so I'm going to take that map I'm going to copy/paste it across here okay so now I have three different visuals on the same page and we will try to set up an interactivity between each one of them okay it's gonna try to align them a bit and the way to do that is using edit interactions okay go to format go to edit interactions and as how you turn on the interactions panel and as you can see when I click on the map how are the others interacting okay so you can see the very clearly the line is being filtered whereas the bar is being highlighted what does it mean it means that when I click on a particular value here so if I want to know more about California for San Francisco click on San Francisco and everything else is filtered on San Francisco ok if I want to know more about let's say call it a city of little town and Colorado click on Littleton and now you see everything about Littleton in Colorado want to know more about New Jersey Lakewood click on Lakewood and you see more about Lakewood and see how this particular line chart is filtered whereas this is actually highlighted if you want to turn on the filter for this go to edit and make it a filter now it is actually filter and not highlighted ok if you want to turn this as none you can turn this as none so with respective whatever you select here so you can see the line is not being impacted ok and that is how you basically turn on interactions and specify interaction between different visuals in poetic stop and finally how does SSRS integrated power bi and this is again something that you guys should mention especially when you're asked a question on components so you will anyways discuss about the Microsoft bi stack and generally talk about talk a little bit creation bi and separately fair ask the question you know does it integrate answer is absolutely yes just the same way I showed you how Excel and power bi integrates so seamlessly the same thing applies with SSRS as well in SSRS and power bi they seem interact very very seamlessly ok you can build SSRS dashboards and you can very easily pin them into power bi the same way I pinned an excel stuff into power bi okay so same thing happens in SRS as well ok so indeed there is a very very rich interaction that happens between both so before we in decision let's just take a simple look at how popiah is performing in today's market because it's always important to understand the market for a technology before you completely dive into it now in terms of job vacancies power bi is something that is going rapidly now the rate of growth is somewhere about 3% every month and the current scenario but again is something that is going to increase because how effective power has become in today's market how easy it is for anyone to use power there is going to bring in a lot more opportunities so it's always helpful if you move into this field the beginning itself and that we have a lot more experience when it becomes a popular technology so apart from that when you look at the salary average it's somewhere about fifty thousand pounds for a three month okay the three month tenure is what this graph is representing so this basically is a huge amount but then again I would not tell that this would be less because power BIA is something that is used by people from different of it this could be a business head everyone today is comfortable using power because it is a self-service business intelligence tool it can be used by people who have some technical knowledge and also can be used by people who have no technical knowledge as well so it caters to need of materials with respect to their needs okay so with this we come to the conclusion of today's session hope you had a great session thank you and goodbye. I hope you have enjoyed listening to this video please be kind enough to like it and you can comment any of your doubts and queries and we will reply them at the earliest do look out for more videos in our playlist and subscribe to Edureka channel to learn more, happy learning.