UX Revolution with Visual Level PAGINATION in Power BI

sometimes you have visualizations that show a breakdown by a long list of items and what happens if there's not enough space you get a scroll bar now for the end user it might feel as if they have to scroll and scroll endlessly to get to the insights that they need but now as an alternative to this you could also consider imagination which means that you divide the number of items over multiple pages and you could even let the user pick how many items they want to see per page now this gives the business user more control over how they consume the content in the best way now let me show you how we can set it up [Applause] welcome to how to power bi my name is Buzz and if this is the very first time for you visiting this channel make sure to hit that subscribe button to stay up to date on all of my videos in which action everything I know about power bi all right so we want to build a visualization with imagination now just to make sure that you know what pagination is let's take over here Bing search now you see that if I search for something then I can scroll all the way to the bottom and there well we have the option to go to the next page so it divides the search results over multiple Pages now that's exactly what I want to have over here for this bar chart because also here I have a long list of now these items are not search results but over here we are looking at car dealers and you see I can just honestly scroll down and down and down and down we have about 84 I believe different car dealers in this data set all right so let's just dive in straight away as a very first step let's give the user the option to choose the number of items that they want to show so for that we need a numeric parameter so we can go here all the way to the top to modeling and then over here new parameter numeric range and this parameter we're going to call number of items that have the user wants to see per page all right data type all numbers okay minimum well at least one we want to see maximum let's say 50. then the increments are one that's fine and by default I want to have the default to done add this slice to the page and click on create now this creates first of all the slicer that we need where the user can pick how many items they want to see but at the moment it's not connected to anything just yet now I'm just going to put it here on the right hand side of the visual and numeric range parameter is just a disconnected table in the model as you can see when you go into the modeling view number of items is that disconnected table here at the top you see the formula generate series 1 to 50 increments of one and the sales table well that is the main table that I use for this visual okay and there's no relationship between there now let's go back to the Viewpoint of view because now we need to add another numeric range parameter because we also want to have the option where the user can click on the page number that they want to go to all right and for that we need another one so modeling new parameter numeric range and this one we can set up in exactly the same way so let's call this one number pages and then here whole number minimum let's put this to one maximum let's also put it to 50 and then increment so one default over here let's just put in one okay add slices to the page click on Create and exactly the same thing happens again we have a disconnected table but now this one is called number of pages now the slides I'm going to put here on the right hand side right next to it all right so now we have the controls which let the end here to pick how many items they want to see per page and to which page they want to go all right now the next thing that we need to do is to rank the items now over here that's the card dealers based on the series that you're visualizing here that is total sales so we can do this with a measure so let's add a measure but let's call it item Rank and this is going to be equal to now here we can use the function rank X now the rank X function has two mandatory arguments table expression now the table that we want to integrate over is going to be all of the items so we can use the all function which removes any filter on here the car repair shop name column all right and then for each item we go to calculate the total sales and on that basis it then assigns direct okay and then we can close the rank X function and that's it so now I want to test if that works I'm going to go here to the visualization and then I'm going to go here to the build panel and here I'm just going to add it to Tool tabs so tool tabs and here we can select item Rank and now when I hover over the first one it should return item rank one then two then three okay so the ranking is working however it would probably be nice to also show it in the visualization maybe here right after the item names and to be able to do that we have to make use of a little trick now let me first show you what the problem is if I now would go here to formatting and then here we go to data labels now the data labels that we have here are the ones that you are currently looking at and I need to because I also want to have another data label that then shows here on the left hand side with the ranking so what is the trick what we can do is we can make use of total labels dollar labels doesn't show so how are we going to do that well total labels only shows if you have two series or more so we just have to make sure that we have two series how without actually changing what how it looks like now well what we can do is make use of a dummy measure so if we go back and we add a new measure and this is going to be a dummy so let's call it like that dummy equals zero then we go back to the builds panel and over here we're going to add it this time right next to total sales so add Dana sales dummy okay now the colors change however there's not really a new series that pops up over here visibly however you do see it in the legend now what we need to do is just play around with the formatting so that it looks like we added before so going into format bars and then here total sales I want to have in blue and then the dummy well dummy I want to have just in white okay now here the legend we don't want anymore so I'm going to turn the light enough and now it looks almost the same as before the only difference is that the labels now show over here and that we have the option to add total labels okay so I'm going to add total labels and see the total name must pop up all the way here on the right hand side all right and what about the data labels here well there we're going to use the custom labels because we can customize how those labels look like so let's turn it on and write a measure that then Returns the rank so I'm gonna go and add another measure this is going to be my item rank label because I want to have it show a little bit differently I want to show it with a pound symbol and combine that with well the previous measure item Rank and this one we can then use as a label so I'm going to go back here to formatting data labels watch out that you apply it to the correct series which is in this case total sales then over here on the options we can decide on the position inside base done here for values this is where it gets interesting because here on the custom labels we can choose the field and the field is going to be item rank labels all right so we have the ranking we have the labels now also showing correctly in the visualization Now The crucial part because this ranking we need to use for a filter measure that checks how many items the user wants to see and on which page we are now let's write a measure that we use as a filter for a bar chart okay so I'm going to add a new measure all right now let's call this one item filter and this is going to be equal to now let's make user variables first of all we need to figure out the page that is selected okay so for that it's actually already measure that automatically gets generated when you set up the numeric range parameter so that's this one over here number of pages value so let's refer to that one square bracket open then you see all of the measures and I'm want to have this one here number of pages value all right and then the next one there we need to figure out how many items the user wants to see number of items which is going to be equal to now also here we already have the items value measure which just checks what the user picked in that slicer for the number of items and then the third component is going to be the item rank now here we already created that Niger so we can also here just refer back to item bank and then the crucial part which is the filtered table with all of the items that need to show in the visualization on the basis of the number of items that the user wants to see and the page that they clicked on now let's call this one items filtered all right and maybe table just to make sure that this is a table we can make use of the filter function and we want to filter and here we can say all select that and here we want to have all of the repair shops all of the items and then the filter expression where we need to check the ranking so the item Rank and the ones that need to show the items that need to show need to have an item Mac that's bigger than here we can see okay which page are we on and so we referred to the page and then we do for this one minus one okay so the previous page times the number of items okay so the number of items there we have also variable now this is one condition that needs to be true for those items that need to show but there's a second condition because there's also an upper boundary this is kind of the lower boundary and the item rank also needs to be lower than or equal to and here we can just refer to the page times and then again number of items okay so you have the current page times the number of items so let's say you're in page number two you want to see 10 items 2 times 10 is 20. all right that would be over here the upper boundary the lower boundary would then be two minus one is one times the number of items is that that's all 10 to 20 and the item rank needs to be in bigger than the 10 or equal to a lower than the 20. okay so that is basically what it says here okay now then we need to close that filter function and then the last part there we still need one variable with the items and that should show okay so items sure which is equal to now here we want to figure out okay what is the item that we're currently checking for so here we can say selected value car repair shop all right and we want to see if this is in that table and with the filtered items that should show okay so we can just refer to that table okay so this is the condition that I want to check for if it's in that table then returning one otherwise it's zero so I need to wrap the whole thing inside of it if function okay so if this is true then return one otherwise we don't it's a zero okay and this is at the final thing that we can return and use for a filter on the visualization okay so items show Okay so let's select the visualization open here the filter spinal and we have to take the item filter drag it here to the filter section now let's drag it to the top this needs to be equal to one because only those items I want to show where that condition was true from the measure click on apply and you see now at the moment we are looking at only items number 41 250. I want to see 10 items and I'm on page number five so let's go to the first page let's dive in one all right we see we have now items one to ten if I say that I want to have five items now I see it nicely shows me the top five if I go to the next page over here page two six did that all right so the main functionality is working we are dividing the items of multiple Pages now the thing is though still kind of ugly so let's improve how it looks like in the ux okay now what we could do is to make it look a little bit more familiar meaning what the user is probably used to and that is that you can choose the number of pages here at the bottom all right and then the number of items we can put it right next to it or maybe here at the top all right so let's maybe first start with that one so let's take the number of items and I'm going to put it here in the top right corner I'm going to make it a little bit smaller now you see not ideal we have to go to formatting and first of all we can turn the slide off we don't necessarily need it then responsiveness we also need to turn off Advanced options responsiveness off so that when we make it smaller it doesn't disappear and we get the filter icon and then here we have still a header and the header we can also get rid of and then here on the values let's decrease the font size a bit and then let's add a backgrounds let's make it blue or maybe a little bit lighter some more colors and let's shift this a little bit to the left not the overall background we also don't want so let's go to size and style background let's turn it off okay and then we can resize it and I'm going to put it here in the top right corner now the problem is well the header has gone so therefore the user has no clue what that number means so let's put right next to it a little text box all right so text box right next to it let's resize it and let's put it over here and we want to show well number of items column let's make it maybe dark gray instead of black and let's make it a little bit smaller let's resize it and let's get rid of the background as well so background of and then we can just shift it and align it with that number parameter all right so that was the first slicer for the number of items now the text box we needed because well and the formatting options there's no option to say text to the left instead of having the header above it so we needed the text box um and then the second slides here where we have to say to which page do we want to go now let's take that Pages slicer and let's move it over here to the bottom now I'm going to change this one from a single value to dial all right and then selection you can say ah signal select that's important okay and now we want to well make it look a little bit different right so first of all let's make it a bit wider I want to have it here more or less in the middle the header we don't need so that one we can turn off and then we can also hear the responsiveness of okay and you're on the visual let's also make the text a little bit smaller so values I put it to eight let's put another line right below it and then the borders we can also get rid of all right and then the background is already transparent however what shows is the overall background so here the size and style background let's turn it off okay good now here I would like to show it at the bottom but when there's maybe not enough space between that slice and the bars what we could do is just well insert another shape and of course later we could replace that with a nice background that fits the reports that we create here now if you want to know how to do that just check out this video over here okay now here though we're going to go to insert shapes and here I want to have a rectangle with relevant corners and let's make it white and so over here we go two shape Style then fill and make it white okay now borderline we don't need done enough and now we just have to resize it and then position it where we want to have it over here make it as wide as the graph and then we have to push it to the back which you probably can do best over here in the selection pane so that shape needs to be in the back all right and when you deselect it it shows to the back of the slicer all right now here we just need to refine it a little bit just like this okay now that looks kind of okay but you see a problem though right so here if we make it wider we see all of the pages and if I make it white wider all the way up to Pages where we even don't have any items so that means we need to also filter the pages slices that we don't show pages that have no items okay now how to do that another measure okay so let's add this measure and let's call this one page filter and also here we need to make use of variables or at least makes it a little bit easier and I want to first know how many items do I have overall okay so this is going to be the total number of items we can calculate this with a calculated function and we want to do the testing card so this thing counts on and here we can then refer to the car repair shop name that's all my column my field with all of the items and we need to well remove any filter so I'm going to use the all function and we want to remove any filter on that item and that's then close the brackets there for the calculate function and then we can say return and at the moment the only thing that we can return is the total number of items okay now here just to check if it works I'm going to go here to my visual let me just select the first page and then again we can make use of the tool tab right so you select the visualization going into the builds panel tool tabs now item rank is still there we can add now also here the filter for the pages okay so that when I hover over it now you see page filter returns 84. okay so that's the total number of Car Dealers of items that I have now let's then go back to the measure because I want to well compare that number to the number of items that should show that's so number of items that should show so let's call this one show number of items now that we have already in that measure which is called number of items value okay and on the basis of the total number of items and how many items should show what the user picked we can calculate how many pages we want to have there at the bottom of the visualization so we can say over here VAR number of pages and then it's going to be equal to and here we can just divide one by the other so I want to have the total number of items and I want to divide that by the shown number of items okay 1 divided by the other so that means if we have total number of items of 100 and the user wants to see done per page well 100 by divided by 10 gives me 10 pages okay now the problem is though what if you have I don't know like in our case we have 84 items and it doesn't give us a round number so therefore it's probably a good idea if we just put this in a Roundup function and we round it up over here the number of digits zero and then we can close that Roundup function okay so in our case if we have 84 items and the user wants to see damn item so page we have 84 divided by 10 is 8.4 rounded up then we get 9 pages okay now this is almost what we want because the last thing that we need is then to use that result as a filter so another variable and here we have then the page filter which is equal to and here we can again use an IF function and see okay what is B selected value four and then here the pages and if it's equal to or lower than the number of pages that should show that's a number of pages that we already calculated there then we want to return a 1 for all of the other Pages we don't want to see them so therefore zero but the last thing that needs to be returned is then that page filter okay and then we can go to the slicer let's select it and here we have to use a new measure as a filter so I'm going to use the page filter drag it onto the filter section I'm going to say that it needs to be equal to one apply the filter all right and now you see it goes from 1 to 17 if I put in the 10 then it should give me 9. just like predicted perfect now of course we can make that slice also a little bit less wide as so over here let's make it a little bit smaller okay then put it here in the middle then it's like this all right then that looks much better now of course if the user would put a very low number here for the number of items that they want to see you would get a lot of pages right so if I type in here let's say three then we have just three bars on every page and you would have well an hour to the right to see well all of the pages that have some information now if you want this to look good well make it at least as wide as well to a digits so that this still looks reasonable and let's test if everything works as it should work so I'm going to click here on page one shows me one to ten page Two Eleven to twenty perfect now of course if the user still wants that experience with the scroll bar that they see all of the items so then you just have to put in a very large number for the number of items right so if I type in here 25 the number of pages gets lost and last and we have a scroll bar if I put in here 50 then the list becomes longer and longer all right and we just have two pages if I want to see all of them well then I'll just put in 100 but and it doesn't work why because in the numeric range parameter I just kept at 50. all right so if you still want the user to be able to scroll all the way through well with that scroll bar right and just have one page well then we just need to go over here too the data click here on the number of items and then here in the formula bar we have to change the 50 to 100. and with that change the user would be able to type in um right meaning we would have this one page with all of the items which is that other alternative way we don't want to make use of imagination so the end user has now full control they still can go for scroll bar or if they want they can make use of the pagination okay and that's the whole thing now let me know what you think put your comments and questions in the comment section below and if you want to build power bi reports together with me there's a bar bi design transformation program coming up in a few weeks where I teach you a bulletproof process of how to develop and Implement power bi reports in a successful way each single time so make sure to check it out now if you already want to watch more tricks and tips about power bi then check out these videos over here I hope to see you in the next video

Channel: How to Power BI

Views: 48,593

Keywords: paginated visuals, split visual on pages, paginate visual, item filter, rank items, ad pages for long tables, parameters, numeric parameter, pagination, dax, filter measure, power bi ux, power bi ui, power ui, power bi visuals, power bi design, power bi design tricks, improve power bi report, power bi report idea, ux for power bi, design power bi, datatraining, datatraining.io, bas dohmen, how to power bi, how to, hotopowerbi

Id: TiS6vnju_mI

Length: 25min 12sec (1512 seconds)

Published: Wed Sep 13 2023