 Hey guys, welcome to SSunitech, so see this side and this is continuation of Power BI scenarios. So today we are going to discuss one more real-time scenario. If you haven't watched the last video of this video series, so I would strongly recommend to watch that video because this is the continuation of that video. So in the last video we have seen we don't have this slicer which is our date slicer and here if we will select any particular date then we should see the location on that particular selected date. So if the count of the location on that particular day is 1 then that should go inside the others, otherwise that will go on that location. As you can see we have selected this 12th number and here we can see the UP is coming 3 times. That's why in our pie chart we can see the UP as 3 times, other Mumbai and Nagpur is coming only once. That's why we can see inside the others that is one. Let me select any another date so here we can see we have only one that is Mumbai that's why it's going inside the others. So basically for that we have to create this repeated location column you can see on the right side of this table. So in this what we need to do we should have a filter criteria in our DAX formula where we will put the filter for the location on that particular row and the date as well. So this time we will have two filters first for date and second for the location and we will do the aggregation. So how we can do that go to on the Power BI desktop for the training second one and here we need to implement that one. So go to on the data view and here let me add a new column so we can click on this. Now we need to wait few second this time we can call this as repeated location count. Now the first thing that you need to understand we want to do the counting so for that we have to use the summarize so we can use the summarize. After that it is saying we need to specify the table name so on that table name we should apply the filter for the date. So for that we have to use the filter over here so we can use the filter then the table name that should be the seat one then we can put the comma here we need to specify the filter criteria. So here we are going to filter for the date so we can use the earlier and here we can specify the PLC date and this should be equals to seat one dot PLC date so this is the PLC date. Now we can close the bracket so filter is done now we can put the comma so as of now what we did here we can see the seat one. Earlier date one so for example we are in the first row so this row will have the 12th row number so it will be going to filter this table on this number so it will have the first row after that you can see the fourth row and you can see the sixth row and you can see one more the third last row and the last one row. So these six row will be filtered according to this filter criteria after that here what we need to do so this time it is asking group by column name so we want to group by on the basis of location now we can put the comma. Now it is saying group by column two so we have only one column that is going to use for grouping next we need to specify the column name of the aggregated so we will do the aggregation the next parameter but before that we have to specify the column name. So for that we can specify as I guess count now we can put the comma and here we can see expression so in this expression we need to specify what we want to do so we want to do the count of the location ID so this is a location we can close the bracket and again we can close the bracket. So now we have done with the summarize so as of now what it will do it will do the filter on the date and counting the total locations and grouping by the location so this time it will filter for 12th of November the first row the in the second row it will filter for 10th of November then 11th the first of October so going forward it will be filtering for each row. Now after that it will be going to count those rows and we will see only two columns first will have the location second will have the total count of that particular location on that date so this is for the summarize now we want to put one more filter this time and this will have for the location because here we have applied the filter for the date so we can use the one more. filter and got on the last put comma here we can specify like earlier then location this should be close to seat one location now we can close the bracket now this results it will be filtered for the location as well inside the summarize we are going to filter for the date outside this we are going to filter for the location. And here the last thing that we need to do we can use the max x function so let me use the max x and here the table name so this is our table we can put the column in the expression we want to do the max for the newly created column which is our count so we can use the count now we can close the bracket and we can press the enter. So now we need to wait few seconds and here you can see we are having this new column which is having the data as we are expecting let's one more look of this DAX formula so we are doing the summarize inside the summarize we are filtering the data on the date basis and after that we are trying to count the locations and group by the location and this count will be the column name. And after that we are trying to filter the data on the basis of locations and after that we are just trying to get the maximum number of counts that we are having for that particular date so this is done now we need to add one more column that will have the location if this count is one then that should be others otherwise we'll have the same as we have. So let me add one more column click on this and here let me call this as new location I guess and here we need to specify if we need to check this REP location count if this value is equals to one then we want to have the others otherwise we want to have the location that we have. Now we can close the bracket and press the enter so now it will have that column and here you can see the UP UP UP three times the rest of the others now go to on the report view and here let me quickly add the slicer and let me try to drag and drop the PLC date on this so this is a PLC date we can drag and drop in the feeds. After that we can make this change as list now here go to on the pie chart click on that and try to make it bigger now here we can see the new location that we have created so we can drag this and drop in the legend and in the counting we want to have this ID so we can drag and drop this ID on the values so it will be going to count this. So we can see it will be counting this now let me select this so once we select then we can see two then three here we are not seeing what value it has here we can see it is UP and this others so go to on the format quickly and here go to on the let me select this and now go to on the format and here we can see data level in the data level we need to have all details level. Now you can see the UP and others if we select any another value then we will see the others only so that is working as we are expecting so I hope guys you have understand how we can use this and this is very important and real time scenario if you still have any doubt then you can drop your questions on the comment box so I will try to response on your comment please like the video and do subscribe my channel see you in the next video.