Automatically select KPIs based on the USER | Mastering UX in Power BI Report Design

each report has different uses and that means different needs and different kpis that they might want to see on the report now in this video I'm going to show you how to show the right kpis to the right people automatically without them having to click around so that means if person a logs in they might want to see these kpis but if person B logs in they might want to see something completely different now let's see how you 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 then make sure to hit that subscribe button to stay up to date on all of my videos and we'll just share everything I know about power bi now let's see how we can build this example over here where we have the kpi cards showing at the top and then right below it we have a line chart also showing the relevant kpis over time and in the top right corner you see who is looking at the report now if I switch to a different person now we switch to the user Olivia Davidson and she sees completely different kpis now this can be set up automatically depending on who is looking at this report now let's start at the beginning and here we have two placeholders for our visualizations at the top we have a new guard visual which only got introduced in June 23 and here at the bottom there we have a line chart all right now our first step is going to be to create a field parameter now to do that we go here to the top to modeling that new parameter and from here we can choose a Fields parameter now let's change the name of this parameter to kpis and now we can pick and choose the kpis that we want to add to this Fields parameter so the kpis then I want to switch between later on now it doesn't have to be perfect yet because we're going to make changes to this anyways but let's say we want to have first of all the catalog sales the online sales we sell the sales store sales and then over here we have also the sales measures but then for each individual category all right and the slicer I don't need okay now and let's click on Create and you see on the right hand side a new table popping up kpis and inside of the table there is one column visible at the moment kpis and here in the formula bar we have the formula that returns that table now if we switch to the modeling view then you see over here we have the kpis table which is disconnected from all of the other tables now then in the data view you see we have actually two columns that are hidden kpi Fields with the structured reference and over here also the kpis order which is then the order in which they show when we would add the kpis for example to a slicer all right now let's use that Fields parameter in the visualization so let's go back to the report view now let's take the kpis parameter and drag it onto the new card Visual and it gives us an overview of all of the measures that we added to that parameter now we can do the same for the line chart here at the bottom I want to have first of all from them date a breakdown by year and month so I put that on the x-axis and then we can take the kpis parameter and drag it on to the y-axis okay now at the moment it looks a little bit overwhelming too many lines however it's going to change very soon because now it's time for the next step now we're going to create categories to which we assign our kpis now this we can do also in the parameters table however we have to do this manually so we can go over here to the kpis parameter this opens up the formula bar that generates that field parameter table and over here we're going to add a new color now let's shift everything one line down with shift enter all right and then here we can add a fourth column just by adding values here at the end so we enter a comma before the closing bracket and here we are going to create the first group of kpis so I put in a one and we repeat that for all of the other measures so over here the next one I also want to be in group one and then the next one as well and the next one as well now over here we have basically the measures that return the sales for different sales channels and then the last three those are the sales for different categories okay so I'm going to assign those to group two all right now once I have done this you will see there's a new column if we go to the table for that Fields parameter so let's switch to the data view now here you see the new column popping up and this one we can rename this is going to be my kpi group ID so now that we have this column we can go back to the report View and here we could insert a new slicer let's place it here in the top right corner let's resize it and let's place that kpi group ID on the slicer so I'm going to drag and drop it onto the slicer that's now also go to the formatting options slice the settings and change the style to maybe okay now if I click here on one we see the kpis for group one if I click here on 2 I see the kpi is 4 kpi group two okay however this is not exactly what I had in mind because the user would still have to manually click on the kpi group that they want to see now the question is how can we set it up in such a way that we don't need that slicer and it automatically picks up the kpi group for that amplating so we are going to assign the amplitis to different kpi groups later okay but before we do that let's first see how we can well set this up without the slicer okay so I'm going to add OVA to the kpis parameter and measure all right and this measure is basically going to choose the right kpi group now let's call this magic kpi filter and here we can use the F function to check if the selected value of the kpi group ID so the selected value function only Returns the kpi group ID when there's only one to display and if this is equal to one then we want to return one otherwise we want to return a zero okay so this if function needs to check the kpi group ID for each measure and only for those measures where the kpi group ID is one I want to return one for the other ones I want to return 0. and this measure we're going to use as a filter now what is important is that this gets evaluated for each kpi that we have in the parameter table let's see how we can do that first of all I'm going to get rid of that slicer again so let's take the slicer press delete all right then I take the card visual now if I would simply take that kpi filter measure and drag it here onto the filter section for this visual well I cannot make any changes here I cannot use it as a filter because kpi field imagine needs to be evaluated for each row in the parameter table now how could we achieve that well there's a little trick I can just take the kpis column right in the parameter table I'm going to add it onto the filter section for this visual let me just drag it to the top okay and then to go row by row we can use Adobe and filter now what do I want to have I want to evaluate each row with that kpi filter measure okay and I only want to have those with a one so I'm going to say over here I want to have the top one so only those that have one with the kpi filter measure only those measures I keep okay now let's apply the filter we have our filter in place and now we only see those measures of group one now let's test it then also for the other group so I'm going to take the kpi filter measure open up the formula bar and here I change the one to two and now it should show me the other three matches and there you go Works beautifully however it's still not completely automatic because there's no link between this measure and the user that's looking at the report and that's the next thing that we need to solve step three now before we go to do that let's also apply the same logic to the line chart here at the bottom so I'm going to select the line chart I'm going to add the kpis column over here to the filter section of that visual let's put it here at the top and over here I want to change it from basic filtering to top end then we want to have the top one on the basis of the kpi filter and that's it let's click here on apply you see also here we have now just these three measures showing up and let's make it also a little bit more realistic by adding further kpi groups to the parameter table so let's select the kbis parameter I'm going to select everything and I'm going to replace this with something that I already set up before in a different example so I'm just going to copy that over and you see we have here four kpi groups more measures that I want to be able to switch between so here we have kpi group one you see a number here at the end one and then we have kpi in group two kpi group three kpi group four okay now now that we have this let's see how our visualizations look like now because group two has now different measures designed the kpis that show are again a little bit different okay so now the next part we have to assign the users to kpi groups and for that what we need to have an employee table now I've already done now let me show you here in the data View and then I take here my employee stable and you see it's just a table where we have employee ID name role and most importantly at the end here we have kpi group ID so the kpi group that is assigned to that employee and also important here we have the email address of that employee which we can use as an identifier for each employee a table like this you can simply create an Excel import and that's it so now that we have this table we can go back to a measure where we have to first figure out who is looking at our report at the moment then figure out what is the corresponding kpi group ID for that person and then use that for a kpi filter so that's we only show those kpis that are relevant for that person okay now let's do this step by step I'm going to open here the kpi filter measure now first of all we have to figure out who is the user that looking at the report now for that let's use variables so over here we have the variable kpi filter okay so kbi filter all right that is what we already had before and then right above it we first need the user okay so I'm going to create a variable called user and the user you can figure out by the user principle name and so normally you would say use a principal name and that Returns the email address of the person that's looking at the report when you publish the board to PowerBack service okay now for the time being I'm just going to comment this out all right and I'm just going to hard code one of the users that we have in amplit table okay so here we have Liam Anderson at data training dot IO all right and that is done by the user that we are going to use to test that and then the second part this is where we can figure out the kpi group ID for that person okay so over here we have kpi group ID for the selected person and so for the select person um and this we can figure out with a calculate function now calculate function that's you well put a filter on an expression or modify a filter now over here we want to find D kpi group ID from the kpis table we can do that with a maximum should not make a difference because we are only going to well have one row because here the filter that we put in place is going to be on the amply table email column right so here we only want to have that row with that where the email is equal to the user's email not the user that's looking at the report and that's only going to be one row so max Min selected value would would not make a difference okay so now that we have that second part we have to update the third part now instead of hard coding here two we want to have well the kpi group ID of the selected person that's looking at the report okay so I only want to have uh over here a one four well with the kpi group ID and the kpis table is equal to the kpi group ID that's for the selected user okay and otherwise is zero no because we're using variables we need a return statement so we can say return and then get the high filter okay and that's the whole Magic now to check this we first need to know okay Liam Anderson and which kpi group does this person have let's go into employees table and let's double check Liam well kpi group one okay so if I now go to the report view I should be seeing well not all of the measures something went wrong so let me just double check here that measure and over here the problem is on line five here I should be returning the kpi equivalently that's that was correct however I'm referring here to kpi group ID from the wrong table should come from the employee stable okay so employee table kpi group ID okay now why because well we are filtering the amplitude table where the email address equals to the email address of the user that's looking at the report and then on that corresponding well we want to have the kpi group ID from the employees table okay so now that I change this it should work no let's double check and that looks better all right good so now that we have this working what's then the next step well now we have to just double check it to see if it also still works for other people and then we have to make the part of who's looking at the report Dynamic instead of hard coding it okay so I'm gonna go back here to the kpi filter and instead of Liam Anderson I'm just going to switch to Well a different person that is in the employee table for example Jackson Carter all right and let's see what it returns now and the kbi is nicely updated you see here we have the sales for the different sales channels and if I double check this in the data view you see over here we have Jackson Carter this kpi group ID is two and kpi group ID 2 if I go to the kpis table should be these lines over here total sales catalog sales online store sales and reseller sales and those are exactly at the metrics that show in the card visual as well as over here in the line chart perfect now to make it a little bit more visible who's looking at the report it might also be a good idea to insert a visual like a multi-roll cards okay put this maybe in the top right corner or the top left corner wherever you want it and then we can create a new measure and this measure is going to be well containing the username and also here we can just type in use the principal name so and this is what the final version should contain using principal name however that's not going to work for the time being because I would need to publish it to power bi service and if I take the user and drag it here onto the card visual you see well not my email address right so that's the problem it only shows when it's published to power bi service so therefore I'm just going to comment that out and then over here I'm just going to switch to Leanne Anderson again at data training dot IO put it in between quotation marks and there you go I'm just going to resize it and then we have to go back to the kpi filter instead of hard coding it here I'm just going to have a reference to that measure user okay so at the moment we pretend that Liam is looking at this report and you see the measures that are relevant to Liam then somebody else would log in to the same report then what would happen is that here this measure well the user principal name with return a different email address so let's do that for example Olivia Davidson and then Olivia honestly is the measures of the kpi group that she was assigned to all right and that's the whole solution now you might still want to make a few refinements so for example you might not want to show all of the kpis here in the line chart but you do want to show all of the kpis here at the top now to be able to do that we could again add a new column to the parameters table that we then use as a filter for well the visualization let me do that I'm going to go back over here once more to the data view the kpis parameter and here we can again add another column where we return a one for those measures that we want to see in the other charts that are on the page for example over here I want to see total sales for the first kpi group but maybe not a total cost then I would put in a zero okay and I do that for all of them let's do this quickly and once you have that then you will see a new column being added over there in the table now we can also rename that column and go to just call this one visual filter okay so visual level filter okay which is going to be the filter for it in our case the line chart you see we just have ones and zeros so for kpi group one we would only show total sales not nothing from the other matches okay now then I go back to the report view take over here the line chart and then I'm going to take the visual level filter and add it also as a filter over here to the lines right now let me drag it up a bit and here we only want to return those measures where we have a one so I'm going to click here one and you see total sales disappeared now we just have to figure out okay Olivia Davidson is Science 2 kpi group two all right it's not kpi equal to let's go here to the kpis table and you see over here for kpi group two we have a zero for total sales and we have a one there for the other ones that's exactly what happened the total overall sales disappeared and we only have well catalog sales online sales store sales and reseller sales the difference uh sales distribution channels okay one more refinement for the line chart let's maybe also add a title now for that I'm going to add another measure and here I'm just going to copy over what I created before I see I use the switch statement for this Dynamic title to just check the kpi group header again and then return either sales over time Channel sales over time basically the definition of that kpi group okay and this measure we can then use as the measure to dynamically return the title for the line chart so I take the line chart go to formatting General title and then here we have the FX button field value and here we can choose the new measure Dynamic title there it is okay let's click on okay I see for Olivia Davidson that would then be the kpi group general sales over time all right so that's it this is how you can dynamically show the right measures the right users on a report page now let me know in your thoughts put your questions in the comment section below and if you like this video then check out these videos over here no thank you I'll see you in the next video

Channel: How to Power BI

Views: 33,547

Keywords: power bi kpis, kpis power bi, automatic kpis by user, user kpis in power bi, different kpis by user, KPIs depending on the user, KPIs by user, switch KPIs based on user, same report different KPIs, UX power bi report, power bi UX, power bi UI, power bi design, build power bi report, power bi report design, best power bi report, power bi top report, power bi tricks, power bi KPIs, power bi kpi, power bi visual, customized visuals power bi, different users different visuals

Id: NqlgTXCfqSs

Length: 19min 56sec (1196 seconds)

Published: Tue Aug 22 2023