Power Tricks, Tips and Tools from the owners of PowerBI.Tips

chrome windows have you got them open good morning good afternoon good evening depending on where you're calling from my name is Chuck typically ho sees and I've got some of my favorite MVPs as if I have unfavorite Mbps I love all my kids Mike and Seth Mike Carlo actually I've been working with from Johnson Controls back at the MDI MDI the Microsoft data insight Summit from two years ago I think it's actually when I met you breathe Seth was actually one of the first MVPs from the what is it what you're calling yourself at the Wisconsin user group mafia I think it's actually brew city maybe City bug that's right I had a ton of fun getting in a doctrine into this community and since then these guys have teamed up together and created an incredible website I don't know if the communities thanked you enough I don't think so so thank you again for creating that incredible website with all of the cool tools on it and they're gonna walk through a bunch of their tools so Trevor's actually already asked a couple great questions and in the last two minutes Mike is already adjusted is actually demos to incorporate your questions Trevor so Michael I'd like you before I hand the microphone over to go ahead start sharing again because I think you're gonna go ahead and do this with the driving and once we actually can see your screen I'm gonna let you introduce us if you can see me looking at you guys that's cool go ahead and I'm gonna lock the aspect to you and I'm gonna hand the microphone over to you and I'm gonna try and be quiet so Mike tell me a little bit about yourself and then give the microphone to Seth if you could sure no problem so let me get the presentation fired up here and we're in edit mode good all right so my name is Mike Carlo as Chuck done a very nice introduction for me earlier I was working at Johnson Controls I did a lot of heavy an analysis an analyst by nature my background is mechanical engineering I have an MBA and now I'm currently working through my data science master's so I love data and I would be considered what you would call the traditional business user I am The IT nightmare I would build Excel sheets I would build macros I would hide data all over the place load things here move them around and I really saw a really neat tool where it was power query back in the day with Excel very early on and I saw that that tool was being moved into power bi and just fell in love with it I thought this is incredible and will automate my job and just do a really good job of making me work more efficiently so since then I went to a couple data insight summits and been just really participating and learning the tool as much as I can and trying to contribute back to the community with my passion for the tool and my name is Seth Bauer my background is completely opposite mics and for some reason we gel and work well together the going way back because it's story time my uh my background was a Bachelor of Arts and art found my way into business after the obligatory restaurant theme that every artist has to go through in terms of making money fell into the Microsoft tool stack in sequel learned database administration and development from a great group of guys at a previous company and wound up my way to the current company that I'm at which is concurrency as a and currently a business intelligence architect in power B and I basically I was looking for the the latest and greatest in terms of what Microsoft had to offer and I recall the first day the first version of power bi still in SharePoint Online and all of a sudden one day there was the power bi version two and I kind of fell in love from from that standpoint on so a huge contributor in the community love the forum's started at the power bi user group part of the power bi user group board etc so basically spend all my days and nights in power bi which is a lot of fun I guess I should mention now I've actually moved on from Johnson Controls and I also work at concurrency I guess I should convince you to come over here yeah so now we get to work together and have a lot of fun in power bi everyday which is awesome all right let's zoom on here next slide so that's us in a nutshell as far as our personal BIOS but in terms of the partnership that kind of precipitated power bi chips and in what we're doing currently right now kind of kind of grew out of the pug so you know if there's one thing we love to mention in in all the presentations and interactions that we have with the audience it's by all means you know reach out and try to find a local group that you can interact with because this is really how everything in terms of the things that we're generating together started and it's a really exciting time so essentially we started off with the pug and I met Mike and he had a fantastic idea of you know all the things he was working with in terms of the Excel and power bi you know wanted to write that down or put that down somewhere and and came up with the idea for what is the power bi at a tips website and since then we started hanging out more and more after the Pug meetings and coming up with great ideas and started to you know think of ways in which we could help out the community together and so I worked on him to come work with me and he worked on me to join him on power bi at tips and that's kind of where it originated from yeah I definitely make a clear comment here we we had a lot of ideas after the Pug group and there was a lot of drinking involved so it was definitely great ideas whether they were good or bad but it was definitely a lot of fun and now we thoroughly enjoyed working on stuff together so we if you haven't joined us I'll throw a shameless plug in there we are the Milwaukee Bruce itty pug group if you are in local Milwaukee area and we do travel around every so often to other pug groups as travel allows you know feel free to join our group we have forums on there we try and share content and presentations that we do with the group as well we had a great session just recently in Seattle which was a good good crowd all right so kind of talking through we've you know how we got together and how the site's being formed so I started this site in 2016 and we started with just a kind of a bunch of tips and tricks and as Microsoft kept developing the tool there was a lot of neat areas that could be enhanced and just kind of added some coolidge to make the experience easier so this is the site it's power bi dot tips we have a number of different tools that we have out there now it's the advanced theme generator which is we'll walk through that a little bit later it helps you pick JSON themes and helps you sigh as your reports we've introduced a new tool in January it's called layouts takes a lot of the guesswork out of building a report or a building report page that looks really well looks really good and then we also have a tool box and so the add tool box is just a collection of links that I have found useful other people's blogs that I like to watch or follow people who are putting out good articles try and make that as it rich as possible so if you are bored and can't go to sleep at night you should go out to the toolbox and try and find a bunch of power bi articles you could read so a lot of great content there I'm even linking a lot of Microsoft content you know how to do embedded tutorials on embedded stuff anything else I can find I'd love to put there if you have a link you like and you think that would be helpful to other people send it to me might get power bi edit tips and I'll add it to the toolbox it's there for the community all right so because we are tips and tricks I will start off with a quick tip so in my more recent blog here we were talking about adding data types within the query editor so we'll just start off real quick and show you how this is working so let me exit out of present mode here cancel this and let's go over here let's start a new page I mean hi this look at the bottom here alright so I have a data table inside this data table I've got some dates and resource and some information I'll click on it let's go edit this table so really what I found here was is often you'll go through a data set and you'll you'll have steps so I'm in the query editor right now you can see the source and if you haven't been in the query editor much before on the right-hand side these are applied steps to your data set so as you do a data transformation each of these steps will be added to you the data as you as you move through it for example if I add a column here and just say new custom column I'll just type in something generic like you know hello and we'll put some quotes around it so it knows that it's text okay and you'll notice here I have a new added custom column but you'll also note here at on the column that's highlighted that there's a custom one I just added there's an abc123 mean there's there's no data type really associated with this column so as good practice should be you should try to address this and make sure that you have the right data type for the column so if I click that and then I drop down to where it says text you'll note here in the applied steps I have added another step so you can see kind of how this would happen if you do a lot of transformations on data and you're constantly getting data that has no data type supplied and you need to apply something so if I change the type here if I do something else if I filter something let's not filter that column let's filter by date and we'll pick a couple dates here okay I get another applied step and I could change another column type if I wanted to and this is something else if I want to change this from a decimal number to a whole number so you'll note here I now have two steps where I'm changing the data type twice it seems a little bit useless and it'd be nice to consolidate this when you do get a lot of queries this will get really long and you'll have a lot of steps here and from someone else's perspective coming back into your query they may get confused about how many steps you're applying so let's get rid of these things let's go backwards to here by removing these steps so I'm going to remove this change type step here and so on the added custom step here you'll note there's a formula bar and you can show the formula bar by going to the view and then there'd be a little toggle here for formula bar so you actually have to have that on to see this bar so if you haven't spent a lot of time in the query editor I'd highly recommend going to view and then formula bar and then clicking the little check marks to turn on the formula bar alright so in here I'm actually gonna define a data type as I create the column so for this function in M at table dot add column I can put a comment at the end of my statement here and then do type it's a text ethyl T dot type that's the right way and when I hit enter I've defined that this column will be created and will be created with a type of text so this gets kinda slick now because now I can do a number of add columns or additional column pieces where I'm not having to do two steps every single time it just makes the code a little more efficient so kind of a cool little trick there makes it a little bit easier for people to read your code later on knowing that you're adding columns and you're defining the column column text type in line with your code all right let's mmm yeah we can do that let's let's apply some changes that should be fine it should be not pretty late wait I'll also note one other really quick tip and trick here while we're in here I'm gonna quickly drop a little visual on the page here this is one that I've found I'd kind of stumbled across this recently and thought this was a good idea so the idea here is what happens when you have multiple visuals on a page and this kind of speaks to like doing layouts and aligning things one of the visual principle here you'll want to do is to have multiple items on a page but you want to align them in a way that you want to have these three items you know the tops of them all aligned now you can do this by going to the format ribbon and in the format ribbon you can click on the items by hitting ctrl and then you can align them by using this align button now you'll note up here in the right-hand corner I have all the align functions kind of already pinned up here so you can actually add your own items to this quick access toolbar by right-clicking on something and then adding to the quick access toolbar what you'll also note here is there's actually a trick if I click the alternate key so I'm clicking alt on my keyboard up here in the right-hand corner which I know it's kind of small you know what do I have a zoom it let's see if I can find that real quick that's a no just keep going all right we'll keep going so if I hit alt up here you'll know here in the left-hand corner I have one two three four five six and so now I can click my number of items here so I'll click these three items I'll hit alt and then I'll do one and so what that did is that aligned all the left items of these objects so let's undo that let's do alt 3 so now I've just aligned the top edges of them and so you'll note here if I hover over these I'm doing a left align and then I'll distribute evenly vertically and then I do a top align and then distribute evenly horizontally so now this allows me to build shortcut keys where I can do alt 1 or alt 2 and align things accordingly so I can hide grab these three items hit alt three four and now it aligned the top edges and then distribute them evenly in that arrangement if I want to add more items I can add more items on my page by just ctrl-c ctrl-v three items and then alt one all - oops - now gives me three items aligned so now I've been able to make a grid of items on the page super fast by just hitting some shortcut keys and the alternates alright done with tricks on to the cool stuff so let's go back over here to some of the tools so what we'll be talking about next will be the advance team generator and some of our layouts Mike it could over here - staff give us some background what's going on here yeah so let's talk about the advanced theme generator like if you want to click into the back into the desktop essentially I'm sure most are familiar but up in the top ribbon we have the switch theme and what this functionality allows us to do is essentially take a JSON file and import it into the power bi desktop and carry with it a whole series of color themes what we found is the vast majority of business users get scared away pretty quickly when you talk about coding of any kind and those files even in representation of laying out first in the original format we're pretty easy to look at and we have an example what that looks like in terms of just a basic basic JSON file that has a background foreground the table accents etc where you plug in your hex code for the color that you want to see and this in and of itself isn't very intimidating and I think some people have used it but even in the show of hands that we've done in previous presentations of this most if not all have never even looked at or created one of these files on their own so Mike came up with this idea to try to simplify that process and it's a good thing he did in version 1 but it's also been version 2 and 3 to the now where the json file and the options we have from the power bi team are much more advanced and if the first file didn't scare people away this one certainly would because now we have a granular level controls all the way down into the different properties within visual visuals so we can set background colors we can set all of the theming the font sizes etc so even if you don't want to use the themes as a color changing tool you can use them to set all your fonts so one of the things that we use all the time is 8 point font isn't something that we typically want in our visualizations so even setting the preliminary font size for everything within your power bi file and creating a template from that is is definitely a valid and useful way to use the tool so that's really the the use case in in how and why the the reason behind generating and building the tool that mike did to help out the business user so Mike why don't you take off and show show that demonstration of how your tool works yeah so we've looked at some really advanced code here I mean this stuff is complicated and you know it takes a little bit more of a trained ID and someone who feels comfortable working in JSON so you know in this property here you know reading through this you'll know that we have a font size equal 14 and there is some syntax that goes along with this to make this work properly inside the tool so I'll do a quick demo of what what we can do with this so I'm gonna switch the theme on this page and so the idea here is we're actually just gonna modify color so I'm gonna say import theme and I have a couple themes here on my desktop already and I'm just gonna go to let's go to this um this theme here this one is made by Amanda at Microsoft this is her plum theme from the themes gallery on in power bi community so what this does now is I've now adjusted all the colors of this page and been able to change them so they're an appropriate color base now if I click on particularly one of these visuals and I'm gonna just adjust the colors here and show you what's actually happening if I click the data colors go into the default color here you'll now know there's a really nice a wide array and these are all customs so this is not the stock stuff that comes with the tool so if I go back over here let's adjust us again and we'll go back to the default theme so I've applied the theme now I want to get rid of it I would click on switch theme go down to default theme and when I do that all the colors will return to normal and these are the standard out-of-the-box colors you are normally used to so I'll switch it again here import theme this time we'll pick do it again use a mantis theme close zoom and there we go there's all of our custom colors so cool it works now let's go figure out how to build it so let me transition over here to the website and we'll go over here so there's a couple ways of accessing it I could be here on the home page let's go big on this boom alright so you can go through and you can find that themes in rehear version 3 is the latest greatest has all the neat features and all the other JSON properties so we'll go to that one there's also icons on the home page there's a quick little instruction sheet here at the beginning so you can kind of pick stuff there awesome then you can go in here you can set some global data colors so this is where we're setting the colors for all the reports so if I open this little box up here it's got a little hand drag or here on the bottom corner and so we can type in hex codes here if we know them so I have a little color wheel here on the right hand side to help you pick a particular color so I've gotten some really good feedback saying hey we would like to pick an array of colors that would be a good idea so it's on the backlog so now what I can do is I can add this below color by hitting add or I can just select a color from the wheel and just hit add select the color I can then it rearrange the colors so that way I can reorder them so that way they look appropriate so the colors at the top here will appear on the far left side in power bi and work their way across I can clear all colors and then there's also a lot of helper tools that give you a good color theme so let's go over here to something else let's go to what will take the google looks if I can type it right click any old color a picture Google feign image here images well there's images here and if I grab this other palette creator which is an Chrome add-in I can then right click on any image so once you install the Chrome ad in here called palette creator it'll add a right-click menu item that I'll let you build color palettes on any image on any website so if you have a company website you need to get to you can just click it so I like this image I'll click on that one and from the palette creator I can generate an 8 color 16 year 24 palette for let's click an 8 and what this will do is it'll take the image it'll grab it and then it will give you a color scheme for that image then I can copy it with this button I'll hit copy it'll let me know I copied it to my clipboard you can jump back over here to the theme generator and then I can control V paste in the code and it's as simple as that add custom colors and boom now I have them in my list here so I can you know reorder a couple things let's put some color at the top here so it looks a little better when we use it in the tool with that guy down don't need him this is a really dark one let's go up there and so I'm kind of just grabbing the colors in again these are the order that which they appear inside the report so that's trying to stylize it a little bit mm-hmm doesn't really fit well there's thumb at the end for now okay so we've got this then all you need to do to get these colors out of the tool you can just hit download and so what this will do is it'll pre generate all the JSON theme colors and theme for you and write all the syntax make sure that it works properly so I'll open that up it's not formatted so you can go I think it's alt shift f4 format and this is in studio Visual Studio code and now you can see I've that the name of the theme comes from here so the theme name is this if you want to name it you can and then you have data colors and these are all the colors that we chose so I'm going one step further let's go back over to power bi we'll go to default theme we'll get back to our basic colors I'm now gonna switch the theme again and import and I should have downloaded a power bi tips color theme I think this is the one we just did and now if I upload it you'll note that all the color palettes have changed so if I click over here the colors you'll see that here's my colors all nice and laid out so pretty cool you can extend this extensibility you know across all the JSON properties so as the tool has been growing and as Microsoft has been adding more features we now have other global settings so you can do table colors here as well these are global settings for all the visuals so if I want to adjust the text size and cross the entire report I can do that here and now you can customize individual items you'll note there's a lot of betas here there is so many data points that belong to custom JSON I think currently I've logged over 1,400 different data points that you can have across all the different types of visuals so there's just a ton of properties you can adjust here so you know the general properties alleged properties and the various access properties as well you can adjust the properties in here they're already pre-populated of what acceptable values would be and if I just add a couple more properties in here so let's just do font family oh yeah I love this one everyone last one I think this is a good one Comic Sans s everyone loves that guy so he'll throw him in there and then you can pick another a chart and then you'd say let's just let's show this axis and make the text size something else so you'll note here I'm just changing properties all across these visuals and then once I'm done I can go back up to the top here and hit download again it'll generate my JSON for me I'll click it and we'll present the data here and now if I reformat this again alt shift F to get it that worked formatted and now you'll note we have all the structures that we just added already correctly labeled and syntax some of the tricky part is when you actually look in the in the tool you know show title does not show up inside the tool so it's kind of hard mapping between the spec and then what's actually used by power bi desktop all right see we've got I'm just checking my notes here make sure we got everything covered yeah good I think we're good let's move on to the fun stuff the new stuff that's not not to say that the the themes isn't a huge time-saver but along that vein moving into the the second tool that you'll find on power bi tips and that's layouts and we're really excited about this endeavor from the standpoint that one of the biggest pain points that we've seen and I think everyone is aware of at this point especially with all of the emphasis that's been given around visualization and different ways in which you can create stunning looking reports in power bi and Chuck even has a great video on on how to do that and we'll post that in the comments as well but this all kind of precipitated this idea of how can we create something that would allow business users to be able to create really good-looking reports in in a fraction of the time and this is really where layouts as an idea came from and what we're trying to do with this is essentially present not only really engaging infographic type dashboards that are the big WOW factors but also provide the ability to create the stock reports or the things that are gonna allow you to have all the slicers that you need on a page but still have the flexibility to not be locked into specific visuals if you want to change them so really what we we've done with layouts and what Michael walk through in a minute is try to simplify the visualization layer for you and present to you that template that really all you need to do is plug in your data and things will just light up so Mike why don't you show how that works and I got to be honest selfishly I build a lot of these things because I like them and it makes my life easier so instead of taking a lot of time modeling data and then working on building out all the visuals we can use these to speed ahead alright let's go over here I've gone to the website I'll go over here to the website again if you note here you go to the on the toolbar here we'll have a thing called layouts if you go to that it gives an instruction video it tells you how to use them so there's a YouTube video kind of get you started these are the seven that we've put together so far and these are just the files so there's actually individual configurations in each one of these files so for example the slicers layout right here that we just released today there's actually nine different age configurations in there so you're getting a lot of different options on how these visuals would look and what the breakdown of each page looks like so if I click on this one here you can see we've got product images on all of them and you can see kind of how you know this is what this is the content we're giving you kind of the keys to the kingdom here and say here's here's a standard template let's get going so when you download it you'll get a zip file you'll unzip the file in there we've included some instructions just to kind of get you started if you need some help and then there's two files there's a PB IX file which is the actual file is the template file that you can add your data to or get your data model connected into it so for this one I'm gonna start with the PPI axe and show you how the report works so we'll click that I'm going to open it up and also you'll note here sometimes when you get into the some of these layouts there's some advanced features and the advanced functionality that we're building into these for you tear them apart the idea of giving you the PPI x-files just see how we built it you know figure out what was going on here and then you can replicate this for yourself as well so here's any one layout and so we have on this page that default colors and then as you click across the pages on the bottom here you can then pick a different style so this original one was a square this one has a vertical line down the middle this is has a vertical line that is offset in the middle this one is horizontal so all these different layouts can be modified and the idea here is you don't like this ribbon chart sure no problem it's power bi you can just switch it so we'll get rid of the ribbon chart we'll just stick an area chart boom maybe it doesn't look so hot let's try something else so the ideas this is your report you can change it however you want if you don't want to use this visual the way it is you can shrink it and you can add another one on top if you'd like so again it to be real dynamic just give you a starting point to think about where things are going now I know you're saying okay Mike you already got a bunch of data in here but I don't want your data I want my data so we'll get into that in just a second and I'll just I'm gonna point out one more thing here that's really key is we've already given you seven different themes in the right slicers layout with color themes from both the community and stuff that we've built so if I import a theme here and I'm gonna go to the right slicers folder here and now I can pick out another color here so let's go over I really like this plum one that a minute did just looks it shows really well so now the theme was successfully imported and now with a couple changes to that color theme now I have a great report that has colors across the entire report theme and then everything matches and you also note here we've tried to customize some of these visuals for you for example on this page we have years quarters and then like a product and in this one we've actually removed all the legends for you and give you the to drill into them and we've changed the functionality of all the different charts in in this grouping so that they filter properly so they're filtering down to the right side hence conveying a good message hey Mike Trevor asked before if you could do a background color well it's not part of this theme can you show how that that's awesome great question so all the themes are dynamic and you can modify things as well here so just because it's on a white background you may not want that you may want something with a little bit of color here you can actually adjust this by going to the paint roller go to the page background and then you can pick any a color here or a custom color if you want so let's say we're going to go after a little bit brighter here in the background and maybe that's too bright I don't want to wash it out a little bit you can change the transparency so now you can change the entire back another page and again now you've just changed the whole field report very quickly and you've even made those bars and the areas sections here pop even better great question all right so this is my data let's get into your data how does this work for your stuff so I'm gonna open up the template file here and what we've done here in the template file we've actually stripped away all the data sets so what it looks horrible it up because everything's broken but we'll remedy that will fix that information in a bit here and while that's loading I'm actually gonna go grab a data source here but quick let's go to edit queries I'm gonna just grab my data source that I use for this and hopefully the internet will hold up for me while I do a data feed here always a little risky getting doing real demos alright so here's a page it's totally broken there's nothing in here so if I need to go get some data and bring things into this one I can go either from a different report and copy and paste my advanced M language queries right into the query editor where I can just start shaping the data here so I can go get data I'm gonna find OData there's my data feed up in my url I'm gonna go pick up some data here alright so let's just pick something here let's grab the shippers one let's maybe grab a suppliers table and we'll just leave it at that load the information so what we find a lot of times is especially when I'm doing this I find I spent so much time working very hard on the model getting the model just right and making it perfect and you know your interactions I want getting the DAX the way I need it summing the columns I want but then I run out of time at the end of a project thinking oh gosh how kind of now build all the visuals I gotta make the guy I got I get all the way through this process so this is a good way of being able to set up a template and have something that's ready to go for you that ok I've got my data I can work on modeling it and then I don't spend as much time dropping everything in here so let's go actually I'm gonna interrupt just for a second um so Trevor actually continued his question and said rather than actually I'm you going out and adjusting that one tab in the layout itself can I go out and actually apply that to the theme and the answer pretty certain is yes Allen is asking a question is the answer is not yeah is then if we go out and adjust it to this one theme could I put it back into the theme generator and actually go ahead and continue amending it in other ways gotcha great question so that has been one of the most highly requested features and it's the number one on layout on the backlog at this time you can't get your theme down and download it and then re-upload it so great point and we were definitely working hard to get that done for you it's that way it makes it easier for you to keep your layouts or keep your themes and then be able to adjust them after the fact cuz you're always making tweaks of them later on I've actually thought it was number two after the inclusion of the pellet own wheel that then the Pelton wheel though they're both number one I love all my children I got it Trevor and Allen I hope that answers your questions great question so guys and Allen not yet so over here you'll note that these were the slicers on the original report and they're all broken but you when I click on them you'll see that I have a field here that it's misaligned so if I now start grabbing items from my dataset and say let's bring a company name over here the filter will automatically populate contact name let's go with country on this one and again you know your data set best and let's go with regen on this one and maybe I don't need the last one so we just get rid of it gone great all right so let's drop some information over here I'll also note here if you're using let's go over here to view we try to name things intelligently on these pages and try to align them so they make sense right so I have a chart here I don't really know what's going on so if I turn on the selection panel or pane here you can then see what kind of what what the order of things are so I have cards here at the top and these were slicers so we're trying to name these in a way that make help you get back to what I was trying to put on the report so let's go over here to this card and this a card would most likely be a number so I'll grab a shipper ID over here and then we can adjust this to instead of counting we'll just do maybe the average and then over on this other one here we have a bar chart and all this data set doesn't have a ton of numbers here we can drop a couple things in here we can drop a company name on the axis on the legend well maybe get rid of that for now and for the value we'll just drop in the shipper ID and your note here I'm removing the the error data sets right so I'm getting rid of those items and then we can just say some for now so that's it looks like it's got different data boom so now I've ever built and basically half the page with just kind of adding some of my data points to the visuals that are there one thing I will note though if you go into these visuals and you remove these fields without adding a new field the visual just disappear so for example if I remove the size here and remove location this is essentially fixing it by removing the visual so be cautious if you're using layouts you kind of want to leave one item in those visuals at a minimum that way you can add you know drink bring your new field over and fix the visual from that perspective so here on this location I'll drop in city will get rid of the broken thing and now I've got cities popping up on my chart and then again since I don't have a ton of data in here I'll just grab the seismic the supplier ID and we'll just do an average of that and so now I've got data on a map and I don't think these tables are linked together let's see if they are they are not so I'm guessing supplier IDs and shipper IDs do not link so we won't make any relationship at this time but the idea here is you know your data you know how to model it this is a great way of just getting you started fairly quickly and you can customise this like crazy you can rearrange these tabs however you want you know whatever order makes sense to you and then renaming them so they make sense to you then we've got a readme file at the front so if you're having problems or you need to get help you know walking through how to add items into your visuals there's a YouTube video on how to do it we have a links for more layouts which we're trying to put out ones many of them per month so working as fast as we can to get stuff done so I'm gonna transition here from this what we'll call like an analyst level layout and we'll go into that's much more custom and a lot more fun from my perspective yeah there are a lot more fun too from the standpoint that with looking at the different layouts that are on the site you can always expect that what we're doing with those is starting to not starting to to show some of the really unique capabilities that you can do with power bi in that utilizing bookmarks and I'm not going to give it away but some of the newest features you'll always be able to see that will be utilizing those in in these highly customized ways but you can still plug your datasets and they're still designed for you to plug in your data much in the same way that the the stock templates are so we're going to transition over to another layout this one's called cool blue I released this just last month and it uses all the latest features bookmarks and then tooltips so really awesome this is gonna freakin below your mind I was just loving this when working on this report alright so the idea here is let me go in here to View mode so let me go to bookmarks panel here and go to view so this is as if we were viewing it from the service we shrink these black bars over here so it will smaller and you'll note here now we have items on the left hand side you can adjust those with using just selecting the item and then retyping the text but the cool part here is if I'm again I'm in the desktops I'm gonna hold ctrl key here if I hold ctrl and I click on these other items you'll note that I'm navigating to a new page so if you think in terms of how does this enhance my functionality I've basically built more like a web application now and I'm able to go through and dynamically click on these items on the left hand side it's then highlighting the item and then revealing to me a whole new page where I can modify things I think this is a really good way of handling the drill-down items are being able to navigate your user to different areas of your data set another thing we'll note here is we're starting to add icons again with the advent of bookmarks we can do some really enhanced features such as okay this page has some data on it and I'd really like to to start highlighting like let's look at filtering down the data so if I control click here and click on this filter icon I'm now able to reveal a transparent background with all my slicers on the page so it's I've hidden them all away somewhere else you don't have to worry about I mean if I adjust these now let's go over to Germany and we'll grab Berlin so now I'm filtering the data and you actually can see it here and again it might be a little hard to see in your screens I'm actually changed the data and it's real uptime updating in the background there so Berlin and then let's just grab a couple of these items over here I'll grab a couple of those now I'm gonna click the we have to interrupt you a lot of people are don't understand how you're actually doing those overlays can you show them at this like yeah yeah they're not getting the fact that what you're showing is here you're hiding in and making things yeah I'm just hiding and showing this so if you look at the selection point you'll see that some of them have eyeballs some of them don't make it away okay great stop good thing for sasro show the guts man and and you know so go download these things right so go go see how I built them I giving you all the keys to the castle here on the site so technically what I'm doing so you'll note here I'm doing everything with bookmarks and let me let's see if it'd be a good wave I'll just show you on this page so over here I've been trying to label all of the items in a clear way so I have this fade out and the fade out for this is actually just an image it's a shape that's on the page and if I click the little I button all it is is this shape and if I click on the shape will go over here to the visualizations you'll note that it is just an item so it's it's a hunk over here titled general and it would be equivalent to doing the same thing as going to say I have a shape I can drop a shape on the page I can even adjust this so this is just an adjustable shape and you'll note here here's the shape that I have I just added a new one and all I'm doing is going to toggle that on and off and so I'm using the bookmarks that just basically turn that shape on or off and so that's the principle I'm using here with being able to adjust these things so if I go back over here to my transparent background here I took a shape I dropped it on the pane we adjusted some properties of it so we'll go over here and I turn the background on to just a white colored background just extend this a little bit and then I just changed the transparency to just 10% so that way you can see things that and you if you don't want 10% you want 30 fine you can see that so it's basically just frosting the page for you so so just to interject here because I see some things in the comments everything that we're demoing right now is available to you for you to go download and dig into these files on the power bi dot tips website under layouts so if you missed that portion where you can just go out and grab these files you can go do this right now so everything we're demoing is is stuff that you have access to and you'll also note here I have a couple other items so to make that filter window so to turn on that bookmark and I'll show you here on this page one I had this thing called filter selection so all this bookmark is doing is I'm turning on the bookmark and the bookmark is just revealing items on the page that you can see that's that's purely all that it's doing and if I go back to the base page click this here's my other item that's hanging out there get rid of him I'll go back to the base page and now it just basically toggles those items off and so there's a lot of lot more details going on in bookmarks if I go to exit here you can actually change how the bookmark is interacting with the page and you'll note here I've turned this bookmark to only modify the display the current page and then all the visuals on the page so this is basically allowing me to turn things on and off you can actually save filter contexts of the different visuals if you want to select a data point and have the bookmarks hold that data point in the bookmark you can do that as well but for this example I'm only turning things on and off so if I juggle between removing filters and filter selection so here's my filters again now the remove filters I just talked about if I click this bookmark this bookmark is modifying the data because I now I have it checked and all I'm doing here is removing any filter to this page so if I turn on the filter section I just let's show some slicers I can adjust some things oops and now if I click the little X here I'll hide that information and go back to the base page and then if I want to remove the filters I'll click the I can click this remove filters bookmark or I've tied this icon to remove filters and then it just removes all the filters so now I can have a button that just defaults to whatever view that I want in addition to this I'll show you how this works on the actual toggling of a bookmark so this is purely a shape I just imported a shape to the program and it's just a little white icon that I have on here and when you import an image is sorry I said shape I meant image their shapes and images this is purely an image so once I import this image I can then go over here to the link button so this is a property for this particular image and I can tell it what I want to do I can have an action like a back button or I can turn it into a bookmark button when I click on bookmark it'll allow me to pick which bookmark I want it to do or what action I want to take to execute that bookmark so for this filtered icon I'm saying okay I want to invoke the filter selection action so this bookmark is on I'm going to say just do the filter selection and then when I click it control click in the desktop boom there it is it shows up and the same thing I'm doing here for the acts right so the axes a similar thing it's an image when I control click on that it goes away um one other feature that we've been muling around is you know inside a report it's hard to convey what each chart means and how you can interpret the data in a way that makes sense to the end-user the consumer of the data so we've added this thing called an info button if you click on the info button we again we gray out the page kind of put a little frosting on it and now what you can do is you can do information boxes so now we have an information box over every single visual area and you can add details you know this is my sales for the last year it only encompasses everything in North America these are key indicators of our performance and so you can actually enrich your data with a story more that have items in the background here that you can just here's an info button things just pop up and show up on the page let me cancel this all right and then for my last trick I'll show you the last thing here that's in this report and it's called report themes tooltips so if I hover over any one of these months I can hover over the month and then it'll give me a total sales for that month and then any sales trend during the month pretty doggone sweet and it's so slick how it updates its animating a little update of the bar chart and then I hover across these items it's doing this for me so it is passing the filter context of every bar that he's hovering over so just to point that out does this isn't just a pretty picture every one of these is filtering down to the data set that's behind the scenes super cool so how do we do this so we go over the tool tips section here there's actually a new page and I built a separate page this is by itself a tool tip you would do this by going down here clicking the plus icon you can go on the page and then go to the settings for this so I'm clicking the paint roller and that'd be very update so this is March updates yeah so very recent but it's ok grab the latest desktop be sure to go do that and this new functionality be in there it's actually a great point if you don't have the latest desktop version can you download some of our templates sometimes they won't work so make sure you're always up to date because if you have a newer feature like this tooltips and you're trying to open it in an older version of your desktop you will have problems so stay on the latest greatest or you can download it from the Microsoft Store and have the latest version always pushed to you so you'll note here on the on this particular page I went to page size and I changed it to tooltip and the size of the page changed and let's go here to go to view and then we'll go to actual size so it's actually a really small visual and then oops sorry and then here if I go to fields I can then grab some data let's let's grab some order details let's grab a quantity let's stick it on this page and just make it a card okay I have some data done and now what you also have to do before you leave because you you will get tripped on this at some point make sure you go back to the page and you change it from a normal page to a tooltip page so I'm turning this on I can also hide this page so I don't want people navigating to the page naturally inside the report so you can right click on page 6 hide the page so now that I've made this a page 6 let's change the name here to a tool tip to tip 2 okay looks good now I can go to visual inside the report and if I want to have a tooltip on this one so I this is defaulting to this tooltip I can click on this visual I can click on the paint roller and under tooltips here it's setting to auto I can also manually detect which one I want to use so for this visual I can just default it to tooltip too and then now my new tooltip will appear super cool functionality and it filters things it's changing their on the numbers it's filtering down based on the filter context of whatever my cursor is over really like this feature nice job Microsoft make sure you do really contrasting otherwise grey because that lose those tooltips back in the initial report you can do that by going to your tooltip clicking the paint roller and then you'll change your page background to some darker color so we'll just change this to a dark grey oh now I got to change the text again yeah check love I love those highly saturated boom dark highly sachit it looks that's that's boom that's my wheelhouse so there we go and then if you go back over to this page hopefully I'm picking the right page and there it is so now we've got a nice contrast that's a nice grayed out item that we can then pre custom and get that all ready to go for you so we'll be producing obviously as many of these types of layouts as possible but if anything we hope that one of your big takeaways is there's an immense amount of capability within this tool to create an exact look and feel that not only for a particular report page but now down to the level where we're almost building web page like designs where it's a single single tab for the report end user they don't know that you're jumping around between different tabs etc because one of the things if you notice on the bottom there are a few grayed out and that we now have the ability to hide those different tabs for whether we're using bookmarks or drill through or tooltips so the the we have a an absolute ton of fun building these and you're more than welcome to use ours or you know start developing your own your own things as well and I'll and I'll even shamelessly throw that out there if you have one that you developed that you just love and you want us to on the site throw it our way if we can review it and enhance it a little bit and we'll we'll put it up there again it's for the community so if you have great ideas too you know we'll be more than happy to promote it on the site it's here for everyone to use and consume and I think that about wraps it up what are we sitting on time got about ten minutes for questions yeah and let's see if we could actually answer some of these questions Seth did you see any that was in there now Martha's asking question I didn't quite understand he he actually owned a read it that way Mike can you show how to script the remove filters bookmark exactly what he meant so I missed the context I was i yes I think I nobody's talking about so we're gonna go I'll do the full remove filters option here okay so what you'll do for removing filters is you're gonna make two bookmarks essentially you'll make one with no filters applied and that was what you'll trigger yourself back to so let's let's let's do something new here um that's the best way to do this oh I see I see what are you talking about now I got it so let me I'm gonna use I'm gonna change the functionality here so let's go over this we'll use this little report icon you know what just kidding let's make something new here let's go grab a shape I think you can do this on shapes I'll grab a shape I'm gonna grab a triangle and my triangle will be my remove filters so we'll start from scratch here let's get this guy the way he's kind of in the way right now boom alright there we go there's my triangle alright so on this page if I click on something you'll note that it's getting filtered so I'm gonna do this as an illustration purpose now if I go to the marks and if I add a bookmark now with something that's been filtered I hit add this will now make bookmark 21 and so this will be you know clicked filled clicked item right so I say clicked item this now shows me that I'm adding this items that clicking so now if I go to let's go to click the sky so now this is the data totally unfiltered I if I have any filter contexts let me and I shall show this too I have these slicers that I've hidden in the background here you'd want to uncheck everything that's here right so you don't want to leave anything with any filter anywhere this or in the report page filters so I'll turn this also I'm good there's no filters being applied and now I'll create another bookmark with no filters all right so this is no filters so now what I do is if I click this you'll see that I'm actually able to manipulate the data if this is ticked off and not checked if you click that bookmark it won't modify your data and I'll show you that in just a second so for example if I click this guy over here and then click on no filters this actually goes back to that bookmark and removes all the filter context now if I want to show someone a filter context for example if you want to pre-select something then I would select the items I want make a bookmark after that and then for example that would be this clicked item so if I check this now boom so the item we selected earlier has now been ticked and now is filtering the data down to only that data set and then I'll show you this as well if I uncheck the data so that was for no filters if I now click on no filters you'll notice it does nothing it doesn't check anything so in order for no filters to work I have to have the data piece checked so I'll fake check it again now I'll go back to no filters you'll notice now it's unfiltered so this bookmark is really driving the remove all filter context so now let's apply this no filters item to my new triangle-shape that I have so I'll click on the triangle shape I'll turn on this link option on and then I will go over here and change it to type bookmark and then I will add my new no filters item so I'll say no filters ok so now if I click on items on the page we'll just click on a couple things here ok it's filtered if I now click on the triangle it now goes to the bookmark called no filters and then removes everything this gets a little more complex when you're talking about multiple pages and that's why you'll see that I have a gazillion bookmarks here on the right-hand side which I would hope it's some day there would be folders for bookmarks I'll just let that in there so but you'll see here in page one I have the base page so it'll take me to the base page with no filters context I have the info bookmark and then I have this filter context and then remove my filters so basically removing filters is a data enabled bookmark where I say unfilter all the data pieces hope that answered your question a folder don't we want to tree view and you'd have each node in the tree it may be a tab name I think that some sort of organization would be a-okay I'll take anything like that over what I have right now which is you know the short little text and girl that owns those features so otherwise as you as I told I think was Allen or earlier on ideas power bi comm over 50% of all of our features come directly from you guys and that's where we get them from so that's a great site yes I use that a lot yeah the bots on the list I go out remove votes from your ideas Devo Devo so so one of the things to point out here you know obviously this can be a bit overwhelming when you're looking at the the complete picture but I know that even in Chuck's visualization video there there's a great recommendation in terms of how do you how do you build these things and using just a pencil and paper and kind of sketching out what you would want to do per page is a great way to get started so really item number one is this first page and after you design it and you want to create your second page you can hide all the visualizations from page one and then start with your new new layer of selections and then just kind of build on top of that and then as you've built everything you can work on building interactions and you know adding buttons to clear slicers and things but that's a great approach in terms of how you start to layer a lot of this stuff over the top of each other another question came in is is people like your icons are using yeah where and how did you choose those I before you answer I'm going to say god I hope that the synthesis of power-ups and power bi comes sooner than later because power-ups actually has a icon library or a media library that you it uses an app that's kasam so for all my icons I actually designed them all in Illustrator and to figure out which ones mean what I did a Google search so I would just say filter button and then I would just go look to see what looks like a filter item or I would go to info button and I would just type in info button on Google so basically I was crowdsourcing what people think those buttons should mean and then I just went and replicated the the icons inside illustrator and then I've added them per the report pages specifically for what function I need so I have a now a repertoire of 44 icons that we can then pick and choose from and put on any number of pages so I would highly recommend going to find little tiny images or shapes off of Google and a trick around that is these are all PNG items so we don't use JPEGs these I think JPEGs do have transparency I'm not quite sure that but I know PNG is for a fact it do and so what you're seeing here is just the shape of the item and it's actually a square that we're rendering here but there's actually transparency around the edge so that way I can put this thing over top of things and it looks like it pops really nice here so again a little trick use pngs and put a transparent background on them great question next question was by Trevor again I guess he was actually on my last week's webinar where he asked about Python support so nothing is specific to this webinar yes it's actually in the team's backlog and I keep meaning to actually change it from under review which is where Kim's got it to backlog no dates but they're actually have started working on our we'll start working on it I'll do a plus one for that one I think it's got 1473 votes so I don't know that he needs any more plus ones and the team's working on it so but anyways Trevor I will actually try and mark that as in backlog actually I think get it right now okay that's woods are more questions because I actually have a pointer on this cool-blue that I wanted to point out but if you layer away I answering in line so I'm sure I missed some but watch for the people who because they won't be able to see those questions in the recording so once you actually ask my core answer I'm live I've got it harmony had a question around whether or not updates are gonna break any of the features that we use and the answer to that is no from the standpoint that as long as you're on the latest for when the layout comes out nothing will break in future updates because it that would just be backward or older compatibility things that power bi desktop already supports so there shouldn't be any issues there nope and we're also so you know so sometimes you'll have some issues with some incompatibility with custom visuals everything that we have built has been all custom visuals and if there are any I'm sorry stock way around all the way around everything we've built is all stock visuals so none of these visuals are custom so the idea is if we only use stock visuals they'll always be supported in the tool and if it breaks we have a great company to go talk to and say hey something broke over to these visuals we should check this out and then if we do make any um layouts with a custom visual we will make sure that we specifically call that out and so that way you're aware of a potential you know future item that you may have be aware that visual may or may not work in the future depending on if the customer supports or not or the guy who made the visual supports or not yeah any questions so I ran into an issue another issue I ran into an opportunity to do better I was looking at the cool-blue and I decided that you know I really didn't need tab 3 mm-hmm you don't want to try and delete tab 3 that would be a bad one to try and delete tab I would be the one that you do and the reason being is yes those those images that Mike Scott aren't native controls so you can't actually remove and slide I to more and five because I had renamed off all of those at that point yep so what I wanted to do is shift them up so again a kind of a pro tip as I put in tweet and Twitter is it if you actually do you want to have fewer tabs delete them from the end not try and get it yes so I will do that to show you what's happening here so if I delete Tab 3 here delete the page yep totally legit okay so so you'll note now because we're using a background image on the report pages there are other items here now I can delete the stuff here for page 3 so I can delete the items here but you have to now shift 4 and 5 up and adjust all the other layouts so it's just easier to delete from the bottom that in a blue bar is stuck to those two you know yeah that blue bar on the left hand side yes that's actually part of the background image yes yes this little this little item one item here is threes god you're screwed 1 2 4 all the way down to the bottom yes so it makes more sense if you're going to sequentially from the top down and so to Chuck's point make sure you delete from the back part of the report and move forward and then it will make more sense and then move items you don't like swapna watch though can gifts be used I have never tried you don't want to use gifts the reason you don't want to use gifts because it's jiff yeah no sorry or the peanut butter yes you actually want to use something that is vector like a PNG so it actually will scale and look nice yes so I believe I have tried a gif or Jif whatever you want to call it and I think it just sits there static I don't think it enemies yeah it would be the first frame and it wouldn't look nice so don't if you can't don't Trevor wants to know if you support pandas I have no idea oh sorry pandas is a Python package ok that's what he's referring to since we haven't added Python support in there Trevor I guess we probably need to crawl before you can walk I will note this though so if you do go to the Microsoft distribution of R M R and Microsoft comm you can go there and you can see exactly what packages are let MS are though those are all the packages at ours for our yeah yeah and we actually have a process where you can request new ones as well yep yeah so okay I was hoping you're gonna use some free icon site I owe you some it--your icons that you drew you know I actually did the same thing if you look at the new dashboard a day I actually did all the logos to actually avoid all of that that said I think the the the clip art in our office suite is actually Creative Commons license and you can use those and they do have these exact same icons Mike have you looked at those I think they're there I haven't done I hope that you'll have to do we have a link that we can grab and put also apply that right now that would be awesome because I may use those then everyone has access to them and I noticed that the infographics custom control actually has an entire suite of SVG icons which is really really cool I excellent don't know how to crack them out so I don't know whose they are so often look at that as well but I will get that link icons are helpful especially now with bookmarks you can do so much more customized and really good job and this is this is beyond just reporting now this is like enhancing the user experience I really really can't emphasize enough how important it is to get those slicers off the main page it's really nice to have slicers because you really want to drill down to your data but I feel like it's such a waste of landscape you're not able to put great visuals in front of people so the more you can then hide the slicers away or with a button click really enhances I think that user experience and I'll also take the note here too on the visualizations panel definitely utilize when you have a lot of things you want to slice and dice put some other filters here on the page level filters or even on the report level filters it's helpful to bring the items that you're not going to touch a lot on the side of the report page they can still live there sometimes you'll have a little bit of a you know weird exercise with the user I can't find the filters where'd they go you have to do a little bit of training that there's a flyout window inside the service that lets you can control the filter so a little bit of a training point for people but I think it really enhances the UI and the user experience in general god and what I actually just wanted to the office and what they're doing is they're doing a Bing search that's actually only filters for things that have been tagged as Creative Commons suite you know they it doesn't look like it'd be as useful as what I would want because you need to have then URLs to the specific one and it looks like it's actually a live not a library but a an image gallery of them in a single image so let me see I mean I'll take that as some feedback maybe we can if we use icons we'll try and use that in the future and then maybe what we can do is we can just share links on stuff that we're using that way people can get out the icons that we can leverage from the Creative Commons stuff that's good idea actually we're a minute over the hour but people often ask me where do I put images I can use them in the service because service has to have URLs I will share my tip if you guys will share me yours I actually put mining get up github seems to be the easiest place for me to reference URLs you guys guys I've been using blobstore App Store but there's actually that's a good good back pocket thing there's several blogs out that show you how you don't need need that anymore yeah yeah I don't know that I want you mime encoded but yes that that there is that too okay guys we're actually a minute over the hour I hope I haven't lost editing capability in YouTube because they do take that away from me after 60 minutes but we didn't have any gaffes we there wasn't any hook up so I don't know that I need to edit this otherwise next week I've got Phil see mark for practical Dax actually going to go out and talking about his brand-new book on how to write Dax for the layman or for the business user Chuck thanks guys thank you so much take care

Channel: Microsoft Power BI

Views: 51,089

Keywords: Power BI, PowerBI, Business Intelligence, Business Analytics, Analytics, data, business data, Microsoft, #hangoutsonair, Hangouts On Air, #hoa

Id: fnj1_e3HXow

Length: 67min 40sec (4060 seconds)

Published: Thu Mar 29 2018