 Hey guys, welcome to SSNETech, so till this side and this is continuation of Power BI project. So today we are going to see one more real time project which is related to the Power BI dataset refresh history. For example, if we design any report inside the Power BI desktop, after that we need to do the publish. So once we publish then all those reports will be available inside the Power BI service. And inside the Power BI service either we need to do the scheduled refresh of those reports and those could be on the timely basis. For example, some of those reports may be going to refresh on the weekly basis or the monthly basis may be on the daily basis or the early basis. Here we are having the dataset which is containing all the reports those are available inside the Power BI service and along with the refresh status. Here we could check like how many reports got failed while refreshing and how many got succeed. And I will record a separate session how you can download this dataset from your publish reports inside the Power BI service. So let's get started. So in this report mainly we are having four slicers, first for the time period, second for the workspace from where we are getting the report, third for the dataset and fourth for the report name. So these slicers could be used to filter out the data. Now in the left top side you could see we are having Power BI dataset report in this text box. And below that we are having this text box with the report failed while last run. So it is indicating on this seven days period. So in the last run total seven reports got failed. Four times capacity planning dashboard got failed and rest of these only one times. So that information we can get it from here directly. So here you could see report failed count in last seven days because in the time period we have selected for last seven days. So total report got failed is 40. In this visualization you could see on the date basis. So for example in this date you could see total report got failed which is 24 on the 19th of July and in this failed count by report. So on the report basis we can get it like from last seven days how many report dataset got failed while refreshing. So eight times of this capacity planning dashboard and rest you can see all those counts. So these counts would be 40 as you can see right here. So the detailed information of this available right here. And below that you could see the dataset name then what is the start date and end date of the refresh. Then what is the status then what was the runtime then what is the refresh type. So here it is the scheduled refresh that you could see what is the owner of that report and in this so failed report day hour and slot wise. So basically here you could see the daily basis we are having the report count. If we are going to right click on any one of this chart and go to on the drill down. So here that will be going to have the information on the early basis for that particular day which is 19. So that information that you could see from here. Now we can right click on this and drill upside and below that you could see the average runtime in minute. So this is in the red and this is in the yellow and this is in the green color because this report is going to take very less time while refreshing. And these reports are going to take too much time while refreshing. Now go to on the dataset and we can look into that. So basically we are having this main dataset which is Power BI Refresh dataset. So let me open that. So here we are having the ID then the refresh type which is scheduled. What was the start date then end time after that you can see this service exception. We can ignore that column then what is the status that is completed. And going forward we can see the request ID then from which work space we are getting that report and what is the dataset ID. So that information we can get it directly from here and what is the name of the report that is also available right here. Let me close that and here we are having the work space related information. And then we are having this Power BI report related information and dataset related information. So I am not going to open all these. Let me open only one of these. So let me open this Power BI Workspace. So inside the Power BI Workspace we are having this name then ID then what is the value of that workspace that is read only. So after that we can see other columns like capacity ID description and other columns but those are not required. So we can ignore only the ID and name will be going to use while checking the workspace. Let me close that go to on the Power BI Desktop. So let's get started and how we can get the data from the source and after that how we can design this beautiful dashboard. So here we are having this blank Power BI Desktop. So first we want to get the data so we can directly click on the Excel. Actually this data was available inside the SQL but for the training purpose I have put that data inside the Excel. So we are required to have this Power BI Refresh dataset. We can open that it will take few seconds we can wait. Now we can select the seat one because all the data is available on the seat one only. So here we can see we are having the ID, schedule type, start time all the columns those we were seen. Now we can directly click on load. So here this is loaded successfully. We can rename this seat one name with the Power BI Refresh. Now let me try to get another data source which is for the second file for the Power BI report. We can click on open. We can select seat one. Now we can click on load. So that is loaded successfully. Now let me try to rename this with the Power BI reports. Now go to on the data view and we can check the data. So for this Power BI Refresh everything looks good and inside the Power BI reports we can see the column names those are not the correct one. So for that we need to go on the query editor mode and where the first row should be the column names. Now we can go on the transform data. So we can click on here and in this go to on the Power BI report. Here we could see the first row that should be the column name. So here you could see use first row as header. So we can click on that. So it will jump over there and now we can close and apply. So this chain should be applied and available inside the Power BI desktop. Go to on visualization mode and here go to on the existing one. So that we could see we are having this image. So I am going to copy this image directly from here and trying to paste that image right there. So that you could see let me try to adjust this a little bit. So we can make it a little bit smaller and put it right there. Now it looks good. Now first thing that we need to use the text box. So let me go here and I am not going to take too much time to do the formatting on all these. Let me try to directly copy this and paste that over there. We need to create the calculated columns on the Power BI refresh data set. So go to on the data view and here the first thing that you could see inside the start date and end date. So go to on the Power BI refresh first and here the start time you could see an end time. So by using this start time and end time we want to calculate the completion time. So for that let me add a new column by clicking over here and we need to write the DAX formula for the same. And here let me try to call this column as completion time. Now inside the formula we need to subtract with the start date of this table minus end date from this table. And we can directly put the enter and let me try to change the data type of this as time only. So that we could see over here and next we want to check the last executed status. So what it means we need to check for the particular data set what was the last execution according to the time. So that we need to calculate. So for that I want to have one more column where we will be going to have only two status for that particular data set 0 and 1. Next we want to calculate the last execution. So we can do that we will see in a bit as of now we need to concentrate what it means. So basically we need to pick one data set and on that particular data set we need to check when the last time it was refreshed. That particular value will be 1 otherwise we need to have the value as 0. So that we want to achieve by using the last status. Let me try to add one more column on this table and let me call this column as last execution status. Here first I want to declare a variable and on that particular variable we want to check the current data set. Now let me try to expand this little bit here on the current data set we can directly get the data set ID. Next we want to return and here we need to write the formula that you need to concentrate. First I want to check if that value is last execution or not. So how we can check that? So let me try to use the calculate function and inside the calculate function we want to have the max ID from this table. Now put comma so here we want to get the max ID of this particular C data set. So here we need to do the filter on this refresh data set. Let me put comma and inside the expression what we need to do? We need to check if the data set ID is equal to current data set. Now close the bracket. Now let me put comma and we will have one more filter on this particular refresh now put comma. Here we need to check the status. So the status of this that should not be equals to unknown because we are also having the status with this unknown. Now let me close the bracket. So here we are going to get the maximum ID of the selected data set. If that value is equals to the ID then we want to return as one otherwise zero and let me close the bracket and put enter. Here we need to close a return as well. Now let me put enter. So this is very straightforward formula. First we are going to get the data set of the current row and after that we are checking the max ID of that particular data set from overall table. If that value is equals to the current data set value then we are going to return one otherwise zero. So here in this column you could see the value of the zero and one only the second thing that you need to focus like the end time. So somewhere the end time value will be the blank that you could see directly here. So I want to have one more column that will be going to have the value for the time and that time value will be like the current date for the three days then seven days then 15 days like that in the filter that you have the time period. Now go to the training one and here let me try to add one more column and let me call this column as time. This is the time. Now and here let me add a variable and let me call this variable as x equals to here we want to get the date if and the difference that we want to calculate with the end date and the current date. So for that we will be like end date is here and for the current date either we can use the now or we can have one more column for the current date. But here I am going to use as now and we want to get the difference on the hours only. Let me close the bracket. So here we are going to get the difference when last was executed and the today's date. Now we want to use the return keyword. Now here first we want to check if the value of this x is less than or equals to 24. It means this is the current date. So you could say like current date put comma and let me check for the second value if x is less or equals to 72. It means it is for the three days. Let me put comma and check for the another value if x value is less or equals to 168. It means this is for the seven days. Now let me put comma and we will use one more if condition if x value is less or equals to 720. It means this is for the 15 days. Now let me put comma and check one more condition that is x value should be less or equals to for the 15 it should not be 720 it should be 360. And for the 30 days that should be 720. Let me put comma and it should be 30 days. And on the else part we want to have like all. Now let me close all these if conditions along with the return. Let me put it here. Now we can see here we are having the problem with the data type. So let me check on this. So this 72 should not be inside the single code other than everything looks good. So that you could see the values. So now this part is done. Now I want to have one more column that will be going to indicate the status. So the status if that is the field then 0 if the completion then 2 otherwise we want to return as 1. So let me add one more column on this table and where we will be going to use the DAX formula simply with the if condition. And this will be going to have the status icon. Here we need to check if the status is equals to field. Then we want to return as 0 if status that is completed then 2 otherwise 1 we can close the bracket and put enter. So this will be going to use inside the icon in the table. I will show you later. So 0, 1, 2 that we are having right here. Next I want to have one more column that will be the Rs. So Rs of the start date that will be going to use later on this dashboard. But here let me add that column and for that particular let me call this as Rs. And we want to extract the Rs from the start time. Now we can close the bracket and put enter. So this will be going to return the integer value in the Rs. But we want to see the complete time Rs second like that. So how we can do that? So for that I would suggest we can use the concatenate function. The first test will be like that. And second test we can add like this and we can close the bracket and put enter. So this will be going to have the Rs like this and we can convert this data type as time. So you can click on OK. Now it looks good. So now we are OK with this. Now go to on the visualization view. First I want to add this report field while last one. So let me try to copy this rectangle directly from here and go to on the training one and put it there. So it will save time and we can set it right there. Now go to on the existing one and second I just want to copy the text which is written right here and copy that and go to the training one and paste it there. Now we can scroll it little bit on the top side. So these two rectangles we have completed. Now go to on the existing one and the text that we can see let me copy this text box and paste it here. Let me scroll it little bit. So now everything looks good. Now go to on the existing one. So here the first thing that you could see the total failed report while last run. So for that we need to create one measure. So let me add a measure and on that particular measure we need to calculate. So go to on the modeling and here we need to click on the new measure and that measure will be going to calculate the count of the report while failed on the last run. So we have to wait few seconds. Let me call this failed in last run. Let me maximize this and here first we need to check calculate counting the rows. So we can use the count rows formula and table that is power bear refresh and we can close the bracket then put comma. First we need to do the filter of the execution status that we had created a new column on the power bear refresh table. So that is the execution status if that value is equals to 1. It means we are filtering for the last execution. Second filter we want to check for the status that should be the failed status. So here we have the status that is failed. If that value is blank then we want to return 0. Otherwise we will be going to use the same formula for the calculating of this. So we can paste it there and close the bracket for the if and let me put enter. So this is very straightforward formula. So here first we are checking if the value of the last execution is failed. Then we are counting the rows if that is blank then returning 0. Otherwise 1 we can go here. Let me add a card so we can directly click on the card and we can put like that like this and put it here. And let me try to drag and drop this last execution right here. So that is 80 now go to on the format of this and here in the background we can off and in the data level we can have this as white. So that we could see 80. Next we are going to check about the failed count by report. So for that let me try to complete this rectangle from here and let me try to paste it right there and we can set this like that. Now go to on the existing one and here we are having a text box that containing like the failed count by report. So let me try to complete this and paste it there like this and we can adjust this a little bit and inside the rectangle we can also decrease like that. Now go to on the existing one and here basically we are having this horizontal bar chart. So this horizontal bar chart is not available directly on this visualization pane. So for that we can get it from the marketplace. So how we can get it so for that we should have login with your credentials and after that you can directly click on this marketplace. And here you can search for the bar chart. So the first that you should see the horizontal bar chart so you can directly add I have already added that so I'm not going to add that again. Now go to on the existing one. So here first you could see we are having the report name and after that the failed count by report. So here what we need to do we need to add one more measure that will be going to contain how many failed reports we have. So go to on the modeling click on the new measure. Let me call this measure as report failed. And here we need to use the calculate again with the count rows and we want to count the rows from this power by refresh. And in the filter we want to filter the status from this power by refresh with the field only. So that we can do close the bracket close the bracket and put it now. And try to click on this horizontal bar chart and after that we can put this below of that failed count by report like that and increase this. Now here inside the power by refresh data set we are having the name so we can drag and drop name inside the category and inside the measure the new measure that we have created for the report failed. So that you could see right here so capacity planning dashboard so it got failed on 582 times so that information we can directly check right here. I am not going to do the formatting of this that you can do on your own but on the existing one. So next we are having this failed report day hours and slot wise. So before going to do that go to on this table and we need to create this table. So for that let me copy this rectangle from here and trying to paste that rectangle right there. And here we can decrease the width of this like this and here like that. Now go to on the existing one. So here if you could see this table so this table is containing like data set then the start time and time status so such type of information it is having. So now go to on the new one and here let me try to add a visual that is the table we can click on this table and let me try to put this above that particular rectangle. Now the first column that we have seen for the name so we can directly drag and drop this name inside the value. Second is the start date so here we are having the start time mainly so we can drag and drop the start time and it should have the value not the hierarchy. The same thing for the end time we can do and we don't want to see the hierarchy that should be a particular value. Next we are having the status and runtime so here we could see the status so we can drag and drop the status below that. And inside the runtime that is the completion time mainly so we can drag and drop the completion time. Next we can see the refresh type and owner so here we need to add two more columns first is the refresh type so here we should have the refresh type. So first we need to select the table and after that we can drag and drop this refresh type on this values. The last column that is the owner so owner information is not available on this particular data set so owner information is available inside the Power BI data set. So here in this source you could see by using this workspace ID or ID we can get the owner. So let me grab this inside the Power BI desktop and let me click on open we need to wait few seconds it is connecting. Now we can click on the seat one and after that we can directly click on the load so we need to wait few seconds until this will not be loaded successfully. So it is loaded successfully now so we need to get the owner name from this seat one. So we can rename this seat one as Power BI data set info and let me drag and drop this owner to the values. So this visual is not having anything because inside the relationship we do not have any relationship between this data set along with this Power BI refresh. So that we need to make. So here we are having the ID and here we should have the data set ID so we can map with the data set ID along with the ID between Power BI data set and Power BI refresh. So it looks good one too many relationship has been created. Now go to on the visualization and now we are having all the values as per our expectation. Now everything looks good. Now go to on the next visualization. Here again I am not going to do the formatting of this you can do on your own. Next we want to calculate the average runtime in a minute. So how we can do that? So first let me try to copy this rectangle and paste it over here. Let me try to adjust this like that. Next we want to have the text which is average runtime. Now let me try to copy this and paste it there. We can adjust this on the top side like that. Second here we are having this visualization that is directly available inside the visualization pane and which is clustered column chart. So we can drag and drop the clustered column chart in the training one. So here cluster column chart we can click and we can put above that particular rectangle and below of the average runtime. Now inside the axis we want to drag and drop the name. So here we can directly get it from the data set or from the refresh table. So let me have this from the refresh table. So this axis will be going to contain the name. And after that average runtime in the minute. So for that again we need to create one more measure. So we can click on the new measure. Let me call this measure as average runtime. So this is average runtime. And here first we want to calculate the average. So average of the completion time that we have created a new column. Here we want to see this in the minutes. So we can use the minute formula. Now let me click on enter on this. So that measure is available right there. But it is having the error because we need to use the complete minute name. Now let me click on enter. After selecting this visualization we can drag and drop inside the witness. Now we can wait few seconds. So here we can see the values of this. So the formatting part you can do. I have already explained the formatting in multiple previous videos. So here we can go on the format and we can do the custom format of this color. So that you can do on your own. Now go to on the next visualization. So here we could see we are having this report failed count in last seven days. So what it means let me click on that. Let me copy this and paste it there. If we are going to adjust this and after that let me adjust this as well like that. So now before going to add this text let me add all these slices. Those are available on the top side. So first is the time period. So for the time period we have created a calculated column. So that is for the time. So here let me find the time inside the Power BI refresh. So we can drag and drop this time inside the fields. So here you could see the value. Let me use this as drop down and put it on the top side. You can do the formatting on your own. So if you are going to select this as 15 days then this should be going to refresh. If 30 days then that should be going to refresh accordingly. If seven days if all if current date. Now here we can see this dynamic text. So it is having like report failed count in last 15 days or 20 days or what was the selected value that we have. So for this we need to create one more measure. So go to on the modeling and click on the new measure. We need to wait a few seconds. Let me call this as dynamic text. So this will have like dynamic text. Now here we need to use the concatenate and inside the concatenate first report failed count in last. In the second text we need to use the selected value. And inside the selected value we need to use the particular time that we had for a refresh data set as time. We can close the bracket again close the bracket and put in there. So this dynamic test should be going to use on that particular header side. So let me try to copy this and let me paste it here. As of now it is going to reflect an error. Now let me try to put this on the top side. And here let me remove that and drag and drop this dynamic text. So it is having a value like that. If we are going to select 15 so last 15 days if it is 30 then 30 days. If we select all then that should be all. So now it looks good. We can do little bit decrease the size. Here first we need to display the total count so that we can do like we can add a card directly over here. And we can decrease the size of that particular card and put it right here. And after that here we have created one measure that measure is going to have the total field. So this is the report we can drag and drop it here. So this is the total field. So the count that is 4358. Let me have for 15 days only. So this count is 176. Now we can decrease the size and we can adjust this like so that you can do on your own. Next we need to use a line chart and on that particular line chart we can add the days along with this count. So that we can decrease the size put it on the left side of this. And in the axis we want to have the date. So here we have the start date so we can drag and drop it here. And after that inside the value that should be the report field. So that we could see seems very less in the size. So this should be like that. You can do the formatting. So after that it will be going to looks good. Now go to on the new one. So here basically you can see the field report days are slot wise. So this particular visualization which is the stack column chart. So inside the stack column chart we are having the count and in the lesion we are having the names. So that information we are having. But let me try to copy this and go to on the training one and paste it here. Let me adjust this like that. After that let me have the stack column chart. Let me put right there right here. Now we have the failed report day hours and slot wise. So that text we can copy from here and paste it there. And we can adjust this and inside the visualization we can adjust this as well. Now go to on the field side. Inside the lesion we want to see the names and inside the values we want to see the report field and inside the axis we want to see the date start. So that is available right here. That is the start date. And after that we can have this as start date. And after that we want to see in the hours so that we can have like that. So this visualization is not looking very good. But while we are going to select only for 15 days or only for the 7 days. So that will be going to have more sense. Now the last thing is remaining which is report failed while last run. So here we should have horizontal bar chart. And let me try to adjust this. Now inside the categories we want to drag and drop the name. And inside the measure we need to drag and drop the last execution that we have created. So last execution status that we could see. And inside the filters we can add the filters. Those are available like workspace data set and report that you can do on your own. If you have still doubt any part of this visualization or in this dashboard. Then you can drop your comment in the comment box and I will try to response over there. And if you need the data set of this dashboard then I will provide a link on the description of that video. So you can directly click on that particular link and you can download the source. And after that you can create this beautiful dashboard on your own. And in the other video I will record a separate session where I will show you how we can download the source data that is available over here for your organization. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. See you in the next video.