Complete Project with Power BI, Power Apps, Power Automate and Dataverse

let's make powerbi even more powerful by combining it with power apps power automate and the data verse now we are going to build this sales leadership ranking report over here what is so special about it is that salese in real time can enter a sales transaction here on the right hand side which is a power app put in the number and click on submit and now that sales transaction gets written back to the source which is not natively possible with powerbi but in combination with power app it is so let's start building this solution together

now for me at the beginning it was quite a big step to start working with tools like power apps power automate and using the data vers as a data source but in this video you will see it is actually quite straightforward all right so let's start building a solution from the beginning all the way till the end now it all starts with the source now here you need to carefully consider what your source is going to be because if we want this real time updating of the report to happen well then we need direct query

not import now what is exactly the difference if we go for import mode that means we import the data into our powerbi data model and that means every time we want to well fetch the new data we need to run a refresh and if we publish our powerbi workbook to powerbi service well there we only have eight refreshes a day so then we run into limitations very quickly so we are not going to use import mode but we want to use di query mode where we directly read from the data source so without importing the

data the data stays where it is and every time when we interact with the report it reads it from the source so that means when we later on have a sales transaction that we write back to the source then well we can read it straight away from that source and display it in the other visualization so that is what we need the question is what sources support di query well an actual workbook doesn't so that's that's not an option so what other Alternatives do we have well one good alternative that is relatively easy to get

started with is the data verse okay so let's build the main tables that we need for this report which is a sales transactions table that's one and we need a table that displays or holds the data for all of our employees okay so how do we get to the data verse well we go here to a browser open a new browser Tab and just type in make. power apps.com and after you log in you get here to the welcome screen now what I want to do first is build a new solution for this report so

I'm going to go to more and here you see we can choose tables flows websites Etc and one of the options that we have here is a solution all right now that solution is going to hold our tables okay now I'm going to build a new solution let's give it a name now this is going to be our sales leadership tracking solution so sales leadership is good now here you want to choose a different publisher than the default publisher why because that is also going to be determining the prefixes that you will have for each

table now here if I would pick the default one and click you on edit you see there's this weird prefix now later on we cannot change it so therefore I would recommend go and set up your own new publisher all right so you click on new display name let's go for how to powerbi so how to powerbi then here the name so how to powerbi description well I leave empty for now prefix now this is important so over here let's maybe just go for powerbi here you have a sample powerbi uncore object click and save

all right and then it brings you back and now you can choose the publisher how to powerbi perfect okay so that's set up let's create our new solution now in that solution we're going to have two tables one for the sales transactions and one for the employees at the moment it's still empty so let's click you on new and then all the way at the button there we have table and let's go and create a table from scratch all right now for the display name let's go for sales but I know that I have a

few other sales tables in other Solutions so therefore I'm going to start off with leadership and then sales okay now here you also have the plural name I just leave it as it is and then here we have a second tab for the primary column so every table will have a primary column now here you could consider renaming it for example to sales name or later on employee name and here on the advanced options you have a few extra options so you see the schema name PBI uncore you cannot change it is dependent on the

publisher that you chose before so we have the schema name sales name if it's required now over here let's make it optional and let's click on save you see our table is there and it already has quite some columns even though we didn't set them up ourselves now that is because the data verse puts in these extra columns like created by created on which could be helpful at some point however you cannot get rid of them all right so here to just focus on the ones that we care about you have here a drop down

and here you can choose the columns that you want all right now I want to First add a few columns so let's do that first and then select the view that we want to have all right so let's click on cancel I'm going to go over here to the plus sign and you see it creates a new column display name is going to be sales amount all right description I just leave empty for now the data type that's going to be a number now pay attention if you also need decimals so I chose whole number

but you see we have also different options here like decimal now the behavior that's going to be a simple one we're just going to write the Val to that column required now also here we could make it required but I just leave it too optional okay now you could dive even deeper into the options if you open up here advanc the minimum value maximum value number of decimal places that allow that's up to you but for this solution we don't need to make adjustments there now let's click on Save and you'll see if you now

click on the drop down we have sales amount over there all right perfect so that's the First Column that I really want to have now maybe that's already select here created on so that we know when the sales transaction happened or when it was entered and over here we have the sales amount now I'm going to click on save T that's it now whenever it's gray you cannot change it whenever it's white you can put in values okay now I need another column though because at the moment I do not know who put in that

sales transaction right so I need the employee so I'm going to click here on the plus icon again now this is going to be the sales employee e right so here I want to have the ID now here we have the description data type so here the data type is going to be a whole number then for the format well none is okay and behavior is again simple required optional now I'm going to save that one as well and there it is straight away it's visible in our view perfect okay now these are the two

main columns that we are going to use for the sales table of course you could add more later on to make it even more realistic but for this solution is going to be enough now you can already put in some dummy values if you want so if we for example click on that sell for S Sales employee ID I enter one then go here to the sales amount let's say this first employee put in a sales record of 2000 press enter all right so our first record was created and you see here these values we

filled out the value that you see in these gray columns like created on automatically were generated so there's automatically a Tim stamp that's in now if you want to just see all of the columns in this table you can go to the schema click on columns and here you find the most important properties like the name but also the data type whether it is a required column yes or no and there are two columns that we need to point out we have here the sales name column which is the primary column and you see it

is the main userfriendly tax identifier for your record often times a name or number and the value in this column will be shown to the user when they need to select from a list of Records so if we go over here to that sales column click on the three dots add it and here instead of going for text we could also go for an alter number right so that the number gets automatically generated so if I leave everything as it this you see the seed value is 1,000 so it starts counting from thousand let's click

on Save now I go back to the leadership sales table okay then click you on drop down and I want to have that primary column sales name in primary okay now let's try that again I see it didn't save the sales amount so I'm going to put in 2,000 all right and over here I'm going to have the second sales transaction for the next employee all right so 4,000 okay and you see in the sales name column we have now 1,000 all right so if I keep on going like this emplo number three he or

she might have sales of 3,000 you see it just Auto increments the sales name go all right so there's another column that you want to be aware of let's go to the columns and here we have one column that is kind of special and that is leadership sales which has the data type unique identifier now that identifier is generated by the data first right so that is a general unique identifier go it right so if you click on it then here I cannot make any changes all right so it's always there now if I then

go back to our leadership sales table and let's add column so on the dropdown and we have sales leadership or leadership sales all right you see it's that long unique identifying number okay now that is very helpful if you want to link it with other objects that are within the data verse but if you want to link this table to well maybe tables that are outside of the data verse where you don't have that unique identifier well then you probably want to make use of well your own identifier your own key okay now here you

see I have still an empty value there for the sales name and didn't record the last value now if we want to we can added that table just directly here in the data verse so if I go back maybe I don't want that first transaction over there maybe I don't want this one there at the end right I can select them and delete these records okay so that was table number one let's go and make the second table so I'm going to go back all right now let's go to tables and add a new one

so new table and this one is going to be leadership employees all right now also here we have a primary column probably makes sense to have here employee name put in a nice description and click on Save also here we need to add a few columns so let's add them one by one starting off with the sales employee name perfect okay now this also going to be text save it and let's go on with the next one click on the plus ion now for each employee we're going to have a nice picture so we have

sales employee image okay let's save it and the last one that is going to be for the employee ID column so here we have sales employee ID okay now you could use that unique identifier that the data verse creates for you however I just want to have well just a sequential number one to 10 we're going to have 10 sales employees okay so the data type is going to be a number again whole number so we can leave everything else as this all right now let's change the view so that we have only the relevant

ones that we want to see now here you see they pop up next to one another because all of them start with sales and ple okay now then click on save all right so now we have our two tables but the data itself is missing so we just need to put in some dummy values now over here we're going to have 10 employees now I'm just going to copy over the values that I need in this table so let me do that quickly now when you fill out the employees in the table just watch out

that if you have a long URL for example for the images it might not fit and then you have to adjust the properties for that column so in this case I had to go to columns then here we have our sales employee image column and if I click on the three dots then go to addit then here in the advanced options you see there's a limit on the maximum character count and by default it's 100 which might not be enough for the whole URL and therefore why I had to change it to 1,000 okay now

let's put in also some dummy values in the sales table so I'm going to go back to tables leadership sales then here click on addit and here we can just put in for every salesperson just a dummy value for the time being all right so I filled out some dummy values and that means we have our two tables we have some data to work with let's now head over to powerbi and connect two are two data verse tables now to connect to the data verse very simple we just go to the Home tab here you

see we have a a separate button for the data verse now let's make sure that you're signed in and once you do that connect all right now here we have all of the tables that are in our data verse environment now to find a table very easily you can just use that prefix because if I now type in the prefix BBI then we expect to see our data ver tables because that was the prefix that I chose before H now just pay attention then sometimes when you create the tables it takes a little bit of

time to see them over here and it might be that you have to refresh so you see once I do that and I try again here we have our two tables now I'm going to select both of them all right and I want to load them now we have the choice do we go for import mode or do we go for direct query mode now here it's very important that we go for direct query because I want to have our new sales transactions be reflected straight away in our report as we put them in with

our power app all right so that was one of the main reasons that we went for data verse tables right instead of a normal Excel table now let's go for direct query let's click on okay and Tada there we have our two tables but the thing is we have all of these other columns in there as well so maybe you want to clean it up a little bit right so it's probably a good idea to go to Power query and just choose the columns our custom columns that we added to the table and not those

standard columns that the data verse adds to each individual table so here I am in power query I have the leadership sales table selected I'm going to click on choose columns all right so now we have to select the columns that we want first of all let's deselect all of them makes it a little bit easier then I want to know when that sales transaction was entered so created on and maybe also of these columns that start with PBI right so over here we have the sales employee ID we have the sales month those two

are really crucial the leadership sales ID and sales name there we have these unique identifying numbers for the sales transactions which we are not really needing for the solution at least not as we going to build it in this video all right so for those for the time being I leave those out we can always bring them back if we would need them okay now here I probably would also rename the column so here we have dat time then we have the sales amount so sales amount and we have the sales emplo D which we

can just call empe ID now the data types look fine and now we can go to the other table to also there choose the columns that we wanted all right so choose columns and also here we can just deselect first all of them then type in PBI and here we want to have the name of the employee the image and the ID all right and also here we can then clean up the names so employee name then we have employee image and we have the employee ID now let's call this table dim employee and then

the other one that one is going to be our sales table and maybe also put F right in front of it all right so we have our fact sales table which has all of the sales transactions which is then going to be linked to our dim employee table where we have the descriptive information about the employees okay now we can click on close and apply and create the relationship between the two tables now these tables look much cleaner now so now we can head over to the data model the relationship is not there yet that

one we still need to create we want to have sales emploee ID on sales employee ID so let's drag one on top of the other one now here we have the emplo table at the top fact sales at the bottom and it's going to be later on one to many at the moment one to one would still work but we're going to have hopefully many transactions for each salesperson the filter direction is going to be signal and let's click on okay okay perfect so the data model also done let's now go here to the report

okay now first of all I just want to have a simple table all right so insert a table visual on this table visual we're going to have from themm the sales employee name and right next to it I want to have the sales amount okay now let me resize a little bit there you go all right so we have the tables in the data verse we have a powerbi file that makes a direct connection to these two tables using direct quy and we have the data model set up as well now it's time for the

power apps part where we're going to create an input form where every sales person can put in a new sales transaction and that should be reflected straight away in that table over there all right now power apps is a separate visual that you can can insert in powerbi report so you find it if you go here to insert in the Power Platform group or can also go here to the drop down and then here other there we also find power apps for powerbi now here before I would do that I would actually create a simple

table first right so we could for example copy this one over here and just slide a little bit to the right and then here on the table you already put the fields that you probably want to use later in your power app okay so here in the build panel let me make this a little bit bigger here I'm going to add all of these fields from the employee table and the sales table so we want to have the sales employee name but maybe we also need the image right if we want to display the picture

of each employee now let's also put in the ID maybe we need that one as well and also here the other ones but here for the created on that one we are not going to use in the power app so I leave that out for the time being now couldn't load the visual let's see what's up all it tells me EMP ID is already there so I put it in twice once from the fact table once from the table one time is enough so I'm going to take it out all right perfect let's make it

a little bit bigger put it here and change it now to a power app okay so I changed the table to a power app Visual and now we have two choices we can choose an existing app or we can create a new one now here we are going to create a power app totally from scratch all right so that you see every single step and that gives you this long URL and if you click on okay power apps will open in your browser Okay so we have an empty canvas where we can build whatever we

want but if you work with power apps in combination with powerbi you always have to double check the integration so you see over here we have the powerbi integration and that means that if it's set up correctly that we can have our power app interact with other visuals that are on a report and that is very important now how do you know if it's if it's working well if I would go here to insert and we're going to to create something like a vertical gallery and a gallery can hold different items so for example our

employees we could list here now here we could also make a connection to the source that we are using in our powerbi report the data verse tables but I actually want to have the well the integration the data that we got from powerbi so if I click here on gallery then here you see we have items and here I want to have powerbi integration data but it's not possible now it used to work like this however now for some reason it doesn't don't ask me why but if we first publish a powerbi report to powerbi

service and then from there create the power app you will see we have this powerbi data integration that we really need okay so this is not going to work this is not going in the right direction so what we're going to do is we go back to Power desk up we're going to save a file and then we go to home and publish the report to a workspace now that gives us a link let's click on it and that one is going to open up in a browser all right now from here we can go

to edit now that power app that we were creating before we're just going to redo these steps right so from here from within powerbi service we're going to click on create new okay now the same thing happens as before power apps is going to open up in a new browser Tab and with without me doing anything well something already happened you see over here we have now already a gallery I didn't inserted power apps there because now we have the powerbi integration that I was looking for you see when the gallery item is selected here

we have in the formula bar powerbi integration. data and that already fills up over here the whole Gallery with different images but now this could be anything that you like if you click on one of these items you see sales employee image could also be sales employee name okay so that is basically already an app doesn't do much just yet right but just to double check the interaction with other visualizations it's maybe a good time already to save over here in the bottom in the top right corner give it a name so this is my

leadership sales tracking app click on Save and also here we can publish it once you have done that you can go back to powerbi service now your report has to load that power app again the first time it can take a little bit of time now you see it's still loading let me just click here on refresh or what you can also do is add a page and just go back to page number one and then it also has to reload and now there you go we have our power app visual inside of our powerbi

report now here this message it is always showing up that you can share it now just click it away you can also resize this app so that it looks a little bit prettier without the sides and if I place these two visuals next to one another it needs to be now interaction so meaning if I click here on Catherine ah it nicely filters the power app and that is that powerbi data integration that I was looking for so that is working properly so always double check this first before you put in a lot of effort

with all of the other functionalities right so that is set up correctly now we can go back to power apps and here we can start making other adjustments because I do not just want to have a list of employees what I want is well ideally the name and picture of the employee that's looking at the report and then once we have that then also an input box where we can put in the sales amount and a button that submits it and writes it back into a new role in the data verse okay now let's start

with the easiest ones which is the name and the picture so I'm going to get rid of that Gallery I don't really need a gallery so often you would go for gallery or form but are we going to do it a little bit differently here I'm just going to have separate elements for each thing that I want to show on this app all right so I'm going to have first of all the name so that's going to be a text label so here we have a dummy value in the formula bar of course I do

not want to hard code a name here it needs to be dynamic it needs to respond to filters that come from other visuals or that I place on the filter section ideally this is going to display the name of the EMP that's looking at the sales report right so here we do not start with the equal sign we can just start typing in the formula and what I need to look at is that powerbi integration now we need to have data you see that powerbi integration do data will return a table now what I want

from the table is the ampli name column and then only the selected value because there will be in our powerbi report later on a filter on certain emploee that is looking at the report right so how can we do that with a function that's called first okay so we're going to W rubp it inside of a first function now that first function you can always have a look at the description right Returns the first row of the source that because of a filter later on there will only be one row so first and last will

give us the same result now then after it we put in a DOT and then you'll see all of the column names now I want to have this sales employee name okay now that gives us Katherine Monroe now let's display this name a little bit bigger so over here we have the property section where we can put in a bigger font so for example 28 we can put the Tex alignment in the middle just like this and we could actually also have a different background call so if we go here to screen you see we

have either a background image or fill Now by default The Fill is white but maybe we want to go for a dark theme right now you see now everything is black so let's go back here to the label and then for the label I want to have a white font so I'm going to scroll down a little bit here we have the font and I'm going to go for white okay so that is basically the first item on a power app which is the label with the name okay now it's time for the second element

so let's go to insert again let's look for an image this time and here I want to have an image of the employe right below it so it can be a little bit bigger just like this now where is that image now we have the emploee image column right and also here if we have a filter on a certain emploee we can basically reuse that same formula as we wrote it before so to be a little bit quicker I'm just going to copy it from here go to our image item paste it in there and

instead of sales employee name I'm going to return the sales employee image okay and now it Returns the image for the corresponding employee now let's put it a little bit more down also here you can play around with the formatting Etc and how big it needs to be all right I think this looks good now to keep everything organized we can also rename this image object to image employee or whatever you want to call it sometimes after you rename it the image disappears so just to make sure that it still shows in the powerbi report

let's publish what we currently have and double check if everything is going in the right direction now over here you still see the old version now we just need to trigger a refresh which we can do just by going back and forth between two pages you can also click on refresh over there in the ribbon and it can take a few seconds so it might be that you have to go back and forth a few times now you see here everything looks fine right so if I click on Daryl mados let's see if Daryl bubs

up yep there he is okay so this is working however of course we still need an input box and a button to submit the sales transaction okay so I'm going to go back all right now let's say you close the browser tab because you have to do something else or the computer crashed that's no problem this over here is saved right so if I click here on Save and then go to editing mode click on the power app and then on the three dots if I click here on ADD it it just brings me back

to that power power app page of course make sure that popups don't get blocked and there we are back again in power apps now if you have another instance open of that same power app in different browser tab then it might be that you have to click on override first before you can start making changes okay now here we are back again in power apps I'm going to have now an input uh text input so over here let's look for input and here you see we have text input I'm going to have it over here

in the middle just like this I want that text to show more or less in the middle and over here by default there should be no text but there should be a h Tex so enter sales amount all right so or sales transaction okay now whatever you like so that will always show up in a little bit lighter color and then as soon as you start typing that will disappear as hint text okay now then we need an option to submit whatever is in there and that you can do with a button right so let's

look for a button that button I'm going to put right below it here this one we can also make maybe a little bit bigger just like this and instead of button we want it to say something else so here for text let's say submit okay and that gives us all of the objects that we want to have on app now here on the left hand side just just make sure that everything is cleaned up so we have here a button for submit and then here we have our text input that is going to be our

uh sales value right so let's call this one sales value and then usually also put them in the order that I want them to be so sales value I want to have a little bit lower so reorder and then if you say send backward right it goes down so that uh submit button I probably want to have over here all the way in the back so re order send to back then it goes all the way down then we have the label that one I want to have all the way up so reorder bring to

front then we have the image the sales value and the button okay now that button has a different kind of color than what I'm using in the report later so I'm just going to change this blue to something else so here we have the color let's go for custom and let's put in different goat so this purple all right and then over here for the Border I don't want border just going to put that to zero and then over here you probably also want to play around with the other colors but I think for now

this is good enough okay good so now let's have a look at the functionality how can we make sure that if somebody presses that submit button that the value that we're going to write here so we want to write a th if we now click on submit that it goes back to the data source that table that we have in the data verse now let's go to the button and you see for on select so when somebody presses on the button nothing happens it returns false okay so it means nothing so let's change this and

here we need something that writes the value the sales value back to our sales table and also figures out okay for which employee this is right so that we can do with a batch function now if you have no experience with power app then you probably have no clue what that means however will become clear in a second so we have batch also here we don't need that equal sign let me just extend this formula bar a little bit if you go onto that function and do control space and then click here on the Arrow

you see applies the updates to the specified R and changes the row in the specified data source Returns the updated row so you see it's the function is being used to either update a value or you can also use it to add a roll okay so we have patch now we have to refer to our table right here our table is called sales but it doesn't pop up why doesn't it pop up because well we have only our powerbi integration we have not connected this power app to the data verse tables yet okay so therefore

if I type in leadership empe leadership sales doesn't pop up as an option so let's go over here to the data sources for this power app and then we can click here on add data and now here we have to make the connection to a two leadership table so we have leadership sales then we have another one so add data and then here we go to have going to have leadership employees okay so now that we have the connection to these two tables now we can go back here to the tree view all right click

on the button on select is equal to and let's try this again I'm going to expand that formula bar and use the batch function okay so patch bracket open ah look here we have leadership sales that is the one that I want to update okay also here you can just put in line brakes with shift enter and so leadership sales then normally you have here the record so you would have here leadership sales again but here we are going to wrap it in a default function now what does the default function do returns default values

for a data source as a record and so we will get all of the values all right so defaults and then here we have leadership sales okay so that is then the second argument of the patch function and then the third argument is the update okay so that is going to be in between curly brackets so over here open the curly brackets maybe shift enter to go down maybe Also let's close that patch function there at the end already with a round bracket and then over here we're going to have the sales amount first okay

so let's select this one it's going to be equal to but we could just hardcode the value 1,000 however I want the value to be not just always 1,000 but the value that's being put in there right now how can we figure out what is being put in there now we can refer to the value that's inside of that input label text label object right so I'm going to get rid of the 1,000 and we're going to refer to the sales value which is the name of that object so just double check how you call

it and then from that we want to have the text now it still gives me error it says data type text okay so we can wrap this inside of a value function convert the text that represents a number to a numeric value so I'm going to wrap this inside of a value function and see the errors are gone all right simple as that now maybe to make it a little bit better let's go back to that label and here see that the format is text um I could also switch this to a number however I

I believe you still need to wrap it inside of that value function okay so let's leave this as it is and and now let's also enter the other things that I want to pass on to our table right so over here let's add another line and over here I also want to have the sales emploee ID now which is going to be equal to well if I type in here employee let's see if employee ID bups up it doesn't right we need to get it from the powerbi data integration again so I'm going to refer

to powerbi integr ation just like we did before data and this we can wrap inside of a first function or last whatever you prefer doesn't be any matter close the brackets and then Dot and then here we have the sales employee ID now don't forget the comma there at the end and if necessary wrap it also inside of a value function so this writes now back the sales emplo D and the sales amount now is there anything else that we need for now maybe this is enough let's first see if it works and before we

make further adjustments so let's click on Save if necessary then click here on publish yep publish this version and then we go back to powerbi service back to the report and just reload that power app and probably you will still see the old version so you have to wait a little bit refresh the page and then after a minute or so it should be there so over here you see we have our new updated app with hopefully the ability to rise back to the source so if I just test this by clicking around now you

see this interaction is still working sometimes the image loads takes a little bit of time I could for example compress the images a bit more so that it goes a little bit quicker however the interaction is working properly okay then the moment of truth I'm going to type in here a value so let's save for theil which is currently 4,500 there I'm going to for her I'm going to type in a sales transaction for th000 I'm going to click you on submit well and now nothing happen however are we sure that nothing happened well let's

go to the data verse table and first check if we have now a new sales transaction recorded for the seal all right I'm going to go back to power apps now in our leadership sales tracking solution we have two tables I'm now interested in leadership sales and from here let's go to edit so that we have a better overview of everything that got recorded and here all the way at the bottom you see we have a new sales transaction being recorded sales emplo ID 9 sales amount 1,000 so how can it be that I do

see the sales transaction correctly popping up over here but not in a powerbi report well that is because our visuals simply didn't get refreshed all right simple as done so if I go back to a powerbi report and now we click here on refresh Boop there we have now 5,500 1,000 more than before for Lo all right okay so of course you don't want to well click on refresh this needs to happen automatically so we need to trigger that refresh so if we go back to power apps and then click you on that button now

we can extend this a little bit further so the patch is just one thing that needs to happen right however I'm going to have a second thing so let's put in here a semicolon and write in front of it I want this powerbi integration to refresh so I'm going to write powerbi integration. refresh then semicolon and you see it still gives me an error right so name is not valid valid refresh is not recognized this is a function so you just need bracket Open Bracket close right after it okay now that is one thing and

another thing that's a little bit confusing for the end user is that there's no no message right so let's extend this a little bit further so over here again another semicolon and then right in between here I'm going to have notification so notify bracket open and here we can go for the text success then what kind of type we want to have a success type and then over here the timeout so let's go for 5,000 this is milliseconds okay so and that should do so let's save the app again and let's also click here on

publish okay so now it will of course take a minute or so and then we can test it in powerbi service okay now actually we can also directly test it from power apps right so if you would just click there on the play button so preview app then here we can just fill out a value and click on submit or if you click your next you can also hold the ALT key and then you see here you can also click on submit all right now let's try again to enter sales amount in our power app

that's integrated in our powerbi report okay so I'm back here in the report in powerbi service going to put in the sales amount now let's do it now for danis so I'm going to select danis which is currently at 1,500 and I want to bring him to 5,000 so let's say he has a sales transaction of 500 then I click here on submit I see now ah success pops up perfect now you can click it away but it will also time out go away after 5 seconds and you see it gets reflected straight away without

me having to click there on refresh visualizations perfect this is how I imagine it to work now that data is written to a table in the data verse so if I go to the data verse table over here now it doesn't show straight away but you need to refresh this one right so that you see the new rows that got added after the last time it was on this page all right so the core functionality we got working however we can enter the sales value for any sales employee and maybe if this is going to

be a real solution you want to have the sales people looking at this report to be able to enter only values for themselves now this we can achieve first of all by having a measure that figures out who's actually looking at the report and then a measure that we can use as a filter to play a filter on the power app Visual and we need to turn off the cross filter interaction between the two visualizations which is actually very quick one so let's do that one first visual interactions added interactions make sure the table is

selected and turn off the cross filter interaction all right and that's it now after you've done that you can go back to visual interactions turn it off and save the file all right so we have turned off the interactions now we have to add the measures to figure out who is looking at the report and the filter measure all right so I can do this in powerbi service by clicking open data model or you can just download the file and do it in desktop okay now let's click on open data model now here I'm going

to go to our demmm table click on add a new measure and let's zoom in a little bit and over here we can have user so user is equal to and then user principal name now user principal name Returns the email of the person that is logged into power RBI service now to see this in action we can go back to our report now here if it doesn't show up just click on refresh there it is and then we can add it to just a card visual a table visual whatever you prefer all right you

see it returns my email perfect now I don't want to have the email I just want to have first and last name now to get that let's go back to our model again and adjust it now we just have to extract everything that's in front of for example the dots or in front of the AD Sign so we can work with left mid functions uh depending on how the email addresses are set up within your organization okay now let me just copy it over to save a bit of time now over here this is one

way in which you could approach it so you see we have the email use a principal name then I extract the left part of the email now over here within my organization we have a DOT to separate the first and last name all right and with the search function you can basically figure out where dot is what position okay so here we extract from on the left hand side the First characters until the dot and then from the dot until the AD Sign well that is the last name okay so over here that's second part

and then we just put it together now let's see if this works I'm going to go back to a report clicking on refresh all right so you see now over here we have the first and the last name and that we can use for the filter imion later now buz do my name is not in the EMP table so there will be no match and therefore I just keep this over here however I'm going to comment it out so I select all of the rows control hashtag or forward slash depending on your keyboard layout and

then over here I'm just going to hard code a username all right so let's say that we are Sandra so Sandra Ross which is an employee in this table that I've set up now we can play around with that name a bit later all right but we pretend to be her okay now I'm going to go back click on refresh and you see over here I'm pretending to be Sandra okay now how can we use that how can we now say okay if Sandra is looking at the report then here the image and the name

that should show should be then also for Sandra now for that we get to the second measure which is going to be the filter measure all right so I'm going to go back over here to the model and insert a new measure and this one is going to be my user filter measure here and if the selected value of the employee name is equal to the user so in our case that's going to be Sandra and I want to return one otherwise I want to return is zero okay now that measure we can use as

a filter measure for the power app visual that we have here you see over here we have sales employee name now if I would just click here on let's say d see that will shows right and if I click here on Gary then Gary shows okay now that needs to happen of course not manually but automatic so therefore I'm not going to go for basic filtering but I'm going to choose top and one buy value now for that we have our new measure but it doesn't show up because I forgot to click on refresh so

over here one user filter click on apply and taada we have here Sandra showing now let's see if this also works for the other ones so let's say we are pretending to be the beta hly all right so I'm going to go back to a model user I'm going to pretend to be T beta hurly now let's see if I did not make a typo I hope not Let's cross your fingers let's go back refresh the visuals and I did not make a dipo there you go we are now to early okay so you see

now if the salesperson goes to this report they only can end the values for themselves okay that's important all right so we have the whole Power App working as we intended to have it now we just have to make everything else also a little bit prettier so that we have really a sales ranking and that is the powerbi part right so let's maybe now save the file and then download it and open up in powerbi desktop where we have sometimes more functionalities and maybe a little bit easier than doing it here in powerbi servers okay

so I'm going to go here to file download this file yep I want to have a copy and open it up okay so here I have the report in powerbi desktop and now we just have to make everything look a little bit prettier so that it's nice to look at and then we have a good overview also who the first three sales employees are and then we can test it all right so I already created a nice background for this report which I'm going to import so I'm going to go here to formatting for the

page and then canvas background image choose the image and then we put the transparency to zero and make sure that the image fit is set to fit all right so let me zoom out and let me then put over here power app on the right inside over here we can also resize it you see when you resize it sometimes it's a you want it to be a little bit bigger smaller doesn't completely work as you intended to to look like um but it's responsive now if you want to you can actually go back to power

apps and then here for the app we can go to settings and then display and then here you see you can also go for landscape but scale to fit you could turn off now just to see you know like how that would look like in your report so just know that one more fails they available space and it's turned on it fits the available space all right so over here if I turn it off click on close save it and then publish it again all right go back to powerbi and after waiting a bit and

refreshing visual it looks like this you see a little B bigger and maybe more in line with everything else on the page so you have to see what works the best in your report okay so I'm just going to resize a bit so that nicely fits on the placeholder just like this then here for size and style we can turn the background off so that we don't have this this one line outside of it there's sometimes a little bit of a line still popping up on the right or the left if you want to get

rid of that you just have to play around with the height and the width if you go to size and Style again and then for example make this width a little bit less I see now it's gone all right so I just perfect the positioning from here all right now let's take that table and let's drag it over here to the bottom and let's make it a little bit wider now of course we want this to be more in line with everything else so we have to change the formatting so here we have to go

to column headers and make the background color black and then the text color white so we have to just do this for all of the different elements and then also here for the totals the same thing here so we want to have the text color in white and then the uh background color in black and then here for the values there we want to have the text color also white the background color black black all right and then the overall background you find in their size and style background and then either turn it off or

make it black so over here I'm going to make it black all right then the Border Lines and the grid lines so grid lines I want to turn off maybe like this and then we have the Border lines are currently blue let's just make it purple for now all right now the table is there let's now also add the ranking so over here in the builds panel here we can keep adding the measures so new measure and let's maybe just start with the total sales so that we have a measure for that so total sales

is the sum of the sales amount and then let's add another one so new measure and this is going to be the employee Rank and for that we can use a rank X function which iterates over table now that table we we can return with all selected we want to iterate over all of the employees right now that all selected function gets rid of any filter that's filter context that's coming from the visual but keeps the outside filters and then for each employee we want to return the total sales iterate over this table calculate the

sales for each employee and then turn return the ranking okay now that is the employee rank measure which we can actually already add it to the table so that we have a nice little ranking right next to it and and you see something interesting happening and that is that we have this empty row so over here let me sort by the emploee rank now that empty row is there because here in our modeling view there this relationship between Dem and FD sales there it doesn't assume referential Integrity right so here if you're working with direct

query then and you're sure that you do have referential integrity meaning we have all of the sales employee in the demm employee table as we have in the FD sales table right which we do we can click on this one so we don't need that blank roll right otherwise it's always empty there with the button so with that one jacked you see it will disappear all right so we have amp ranking and now I want to show the top three over here at the top now for that again we need to have some measures one

measure that Returns the picture or the picture URL image URL for the first person that has the highest sales and then one for the second one and for the Third one so let's go over here add another measure now this one is going to be a little bit longer so we going to have the first ranked employee and over here we're going to have a variable that's going to have the rank table now let me show you what I mean let's build this table with add columns now at the beginning we first want to start

with a table that has all of the employees and then we're going to create a column let's call this one rank now the add sign just indicates that this is a column that only exists within this virtual table that we creating here all right and the rank we can calculate just like we just did before right so rank X and then all selected and we just repeat exactly the same thing we iterate over the table uh them employees and then we have the total sales all right and that gets calculated for each employee and the

rank X turns that into a rank okay now we can close that columns function if you want you can also indent the whole thing all right and now we're going to have a second variable now this is going to be returning the actual uh employee so the rank one employee and here I want to use a calculate function that's going to return the employee so an employee name or the employee image let's go for the image first all right and then here for the second argument we're going to use use a filter function we're going

to filter that rank table from before all right because we want to have only those that have a rank equal to one okay so rank equal to one without that add sign there at the beginning okay and then we can close the filter function close the calculate function and indent this a little bit perfect and then we can return the rank one employe now that was a long one and now we can just uh repeat the whole thing also for rank two and three just seeing okay here we're looking at emplo so for this one

don't want to have the image but the the the name would be a little bit better okay good so I'm just going to do this two more times okay so over here we have one that's going to be two and then once more for the rank three employee all right so we have the measures that return the first the second and the third employee based on the sales ranking okay now let's test them to do that I'm just going to insert a new table visual let's put it over here and to that visual I'm going

to add the three measures now you see this looks a little bit of because for the third ranked employee we have a blank value how is that possible well if you go over here to the table we see we have lucil that's the first one all right that's okay then we have Daryl H Daryl is not showing but ke is now what's going on is that we have well a shared rank too right so if we look here at the employee rank we have well two people that have an equal Rank and then it jumps

the third one and goes to Fourth all right now how to deal with that how can we make sure that we do not have an equal employee ranking all right now for that we have to make a small adjustment to how we rank the employees so that we cannot have the same rank for multiple people all right right let's go to the measure amp Rank and here we just have to make a small adjustment to the values on which the ranking happens now here I'm going to add after total sales I'm going to just return

the emploee number now you might be thinking why the employee number well it is just a random number that is going to be very small so that we do not have two employees with exactly the same ranking now could we make this more sophisticated of course we could we could also make it uh based on the number of sales that the sales people have or we can could consider just alphabetical ranking in case they have exactly the same sales amount but I don't want to over complicate it at this point so I'm just going to

take the emploee number all right now how can we do that we cannot just refer to the emplo ID however we need to have something like a Max function and then we're going to look for the employee ID so this one or here close the bracket and then we need to still wrap this inside of a calculate function so calculate and that calculate function here forces contact transition when we iterate over the sales employees row by row we take that sales employee uh name as filter right so basically just then Returns the amplo D for

well the employee all right now I'm going to divide that by a lot so it doesn't matter so just to make it a very small number because it just needs to be a little a little bit different all right and emplo rank nicely updated and we do not have two or more employes with the same rank it's not possible anymore okay now why is Kei first because well he has a higher employee number than Daryl that's the only reason all right so again we could make it more sophisticated but for now this is enough all

right so we have now a way to break the ties when there are ties now now we can go back then also to our first ranked employee right and here we're going to make that same adjustment right so calculate max and then employee ID all right and then we close the bracket now I'm just going to copy this and then also add this for rank two employe and then also for rank three amply and Tada we solve the problem we have now lucil Kei and Daryl all right so the measures are working so I can

get rid of this visual we don't need it anymore that was just for testing and now we can already use these measures on on a card visual so let's insert the first one all right then I'm going to choose over here new card visual this one over here and then we can go to formatting options all right and then over here I don't want to have the label so the label I'm going to turn off then for the value I want to have this much smaller all right so let's go for 12 color let's make

it white all right and then over here I want to have it in the middle now we where is it it's not visible anymore because we still have a background so I'm going to go here to size and style turn the background off all right and then we do the same for the card itself so I go to cards background turn it off and I also don't want to have the border and now we can resize it and if you watch carefully there you go here we have a card and now we just have to

nicely align it there on the placeholder now I'm just going to copy that two more times and do exactly the same thing so I'm going to put one over here all right and then one more over there and then we just update the measures that were added onto these cards all right so we already displaying the names of the top three employees now we need to do the same thing for the images of these amplies now and we could actually create mashes with the same pattern and then just return the image or and we can

also go over here create a new measure and then let's call this one rank one employee image and that is going to be equal to and first of all we are going to have the name of the first ranked employee so over here I would just write employee and then we can refer to rank one employee and then here we can return the EMP image in another variable and for that we can use the calculate function and we want to have the maximum and then image okay okay now this could also be selected value right

but it doesn't matter Max Min selected value all of it would work and here I want to have the employee name is equal to employee all right and then we can end it and we return the employee image and just like before we have to do it two more times right for employee one two and three okay so we have the measures that return the image URLs for EMP 1 2 and three and now we can insert a new card visual all right I'm just going to place it over there at the top and images

what I want I'm going to add it over here now I don't want to show it as a value so therefore I'm going to go to formatting and turn the values off okay now label we also don't need we want to make use of the image feature over there so here we can turn image on all right we want to have image URL click on that FX button and then here we can look for the image measures now I'm going to do it for the first I believe and that looks good we have Lucy Hall

showing up there at the top okay now I don't want to have that background again so I'm going to go and turn it off and then for the card we're going to do the same background off border off all right and then we just have to nicely place it over the placeholder now here it's just playing around a bit probably easiest if you go to size and style I have here a width and height of 150 and then you know with the positioning we can just do it pixel by pixel to get it perfectly over

the circle all right so this gives me the perfect positioning nice all right now we just have to repeat it again two more times so I'm going to copy paste this one here is going to be employee number two and then we're going to have employee number three over there all right so we have them in place now we just have to update the measures for the images so I'm going to take this one then go to image and then here the image URL so now going to be the second ranked employee okay and then

we go to the third one do exactly the same beautiful and the images of the employees are now also in now just the last Finishing Touch and that is a text box all the way at the top that says that this is our sales leadership I'm going to place that text box in the middle of that part sales leadership all right and I'm going to put it in the middle make it a little bit bigger and then also get rid of the background and let's make it white and maybe I like it capitalized so I'm

just going to capitalize it quickly sales leadership all right so our sales leadership tracking report is almost done now I see that we have Katherine Monro here which doesn't make sense if I look here at the user should be tar hurle so I'm going to double check the filter the filter it's not there anymore something maybe it wasn't saved so I'm going to put it back top end top one and then over here buy value and the value is going to be that user filter and let's click on apply and that's it okay now it's

time to test the report and also still I want to be informed by let's say email when somebody submits a new sales transaction okay so I'm going to save it first now to test it you don't necessarily have to publish it to powerbi service you can do it also from powerbi desktop now let's see we have here tabita which is ranked over here is number seven all right with sales of 2,000 now let's say we're going to put 2,000 on top right so new sales transaction comes in clicking on submit success all right that's good

okay now after five seconds that disappears I'm looking here on the left hand side and is still there well the thing is if you're in power desktop we need to refresh the visualizations not to do that over here we need to go to the optimize step all right and then here we have refresh visuals and then after second or so you see it gets added and the Bea hurling is now M rank five all right so it is working now of course in powerbi service you don't need to refresh the visuals manually it happens straight

away all right so I'm going to publish the report again so home and then click on publish and let's follow the link to to a powerbi report in the service and there we are in powerbi service and let's say the baa keeps on selling more and more and currently is already at number five so let's add another sales transaction of let's say 1,500 okay and without clicking here on refresh visual visuals you see taba is now on rank number two and shows also now here at the top and let's say she is not done yet

and keeps on going and going has an transaction of a th and she made it all the way to the top yes all right now at the beginning I also told you H we are going to learn about power automate right because we do want to be informed not only when we look at this powerbi report because we cannot just be staring at this report all day right we also want to be informed either over teams or by email and for that we can use power automates right and it's all nicely integrated in power apps

so if we go to power apps if you don't have power app is open anymore just take the app click on Three Dots and then go here to addit now there we are I'm going to click on overwrite all right and now here we're going to set up a flow Now to create a flow we can actually do it from power apps right so here there's a separate section power automate and we can create a new flow from within power apps that's really really nice so let's click on it and that opens over here some

templates let's see if there's an email template yes there is we have this one over here see there's also one for teams but let's go for the easy one and that is an email now over here you see I don't need to authenticate anymore there's already a green check mark so that's good and we can give it a a name so email and then we want to have a notification for new sale okay now let's click on next and create the flow and that's it we have created a flow already now if you want to

do more more advanced stuff you just go here to the flow on the left hand side you see it pops up there click on edit you see the trigger is power apps when we are going to click button that's when we want to run that flow and then then the second step is to send an email now over here you can uh set it up by just clicking on it all right now here we have two subject body now you can hardcode it but actually I want it to be set in power apps so you

see there is dynamic content so we have Dynamic content like two some and body all right so we can actually just leave everything as it is just wanted to show how that flow actually looks like and then we just go back to our tree View and we're going to select our button all right and then we want to run that flow when somebody clicks on the button right so over here let's add another part and to run a flow we can just refer to the name of the flow right so we want to send an

email so if I start typing email you see it already pops up we want to have the second one there email new sale run all right and then well this is going to run the flow but we have a few parameters we have three parameters now the parameters we don't see from here when we press control space however we have the two the subject and the bodyy these three parts now we can just specify them in here okay so I want to have as the two address just my personal email address let's not bother anybody

else so buz do at datat trading. and then we have the subject that is going to be new sales transaction and then here for the body there we can have for example the amount now we could hardcode it just like this and just see if this works now at the moment we get an error just because I missed the semicolon right after it now always start with easy hardcoded inputs and then you know make it variable so that you check every step on the way all right so I have all of the parameters filled out

okay now I'm going to save that all right now let's see if we get a notification when we enter a new sales transaction so I'm going to go over here back in powerbi service and type in a sales amount so let's type in 2,000 going to click on submit success all right and the visual nicely updates so we have also here now 2,000 on top and I got a new email notification so let's open my Outlook Tada here we have our email we have sales entry sent from buz to buz do and over here our

sales value number now if I go back to Power app and then take the submit button you see one more change that I made right before I went on with the testing part and that is instead of 1,000 replace it with sales value. tax now what does that refer to again that is the input box right so the input box is called sales value as you can see over there and we want to have the tax now if you want to convert that to a value then you can use the value function that's it all

right now we can extend that further and also return the EMP name and put some nice body tags around it but it is just an adjustment further adjustment of what we already have so our solution is done all right so we have our sales leadership tracking report that uses the data verse for storing our data we have a power app that lets us write back data into the tabls that are stored in the data verse and we are using power automate to send an email notification to us every time there's a new sales transaction being

added and everything is happening real time so that means we are using direct query so that our changes can reflect immediately in the report now a lot of stuff happening in this solution and that is the thing when you go and look outside of power there's so many things that are possible but that also makes it intimidating especially at the beginning but I hope with this video that anybody that was hesitating well is now pushed a little bit into the right direction and that you're going to give this a try because it's really worth it

all right now let me know your thoughts in the comment section below and if you want to see more videos like this one then check out these videos over here I have also another design training coming up at the powerbi design transformation program where we build reports together and share all of my tips and tricks if you want to take part in the next cart then check out these links over here all right now thank you for watching I see you in the next video