 Hey guys welcome to SSUnitech, so see in this side and this is continuation of Power BI Real Time Scenarios. So today we are going to discuss one more real time scenario and this is very interesting. Here in the right side you can see the pie chart and in the pie chart we are having the locations and their occurrence in the table. So in the left side table you can see the UP that is occurring 3 times. That's why in the right side you can see UP as 3. After that you can see the Bihar, so Bihar is occurring 2 times. That's why in the right side you can see Bihar as 2. Then Mumbai is occurring 2 times. That's why here you can see the Mumbai as 2. Rest of the locations are occurring only once. As you can see the USA, then Nevada, then Nagpur, then Nasik. So these 4 locations are occurring only once. That's why it should go inside the others category. Which is you can see in the right side of this pie chart. So how we can do that? So for that we have to create this count if for duplicate location column by using DAX. So here you can see we are having the UP, so it is occurring 3 times. First is here, second is here and third is here. So it is going to count the UP and here you can see the occurrence. Once we generate this column, after that we will add one more column. That will see if the count if for duplicate location value is 1, then that should be the others. Otherwise the location that is having in our existing table. So this is our concept. So first of all we need to look how we can calculate this count if for duplicate location. So for that let me open another probably desktop and here I am already imported this table. Now let me add a new column. So we can click on this. So it will add a new column and we need to wait few seconds. So here the new column. Now let me maximize this. Now let me call this as count if for duplicate location. Now what our logic will be the first thing I am going to declare a variable. So for that we have to use the where. Then we need to specify the underscore. After that we need to specify the variable name. So that will be the current ID I guess. Then we can specify equals to then our table name and the location value. So table name is seat 1. After that what is the column? So that is the location. So this will be hold the current location value which is you can see the first column that is the Mumbai. So it will hold the Mumbai on this variable. Next we want to return the count rows. So we can use the count rows. So you can see this count rows. Here it will ask for table name. So for that we want to put the filter. So we can put the filter for that particular location. So this is the filter then the table name that is the seat 1. After that we can put the comma. Here we want to filter where our existing location should be equals to the location that is containing in this table. So for that we can specify the underscore then the current ID. So this is the variable. It should be equals to the seat 1 location. Now we can close the bracket and we can put the inter. So that's it. It's very easy and let me check the values. So here we can see the Mumbai. So Mumbai is coming two times. That's why you can see the two and the Nasik, Nagpur you can see only once. Bihar is coming two times. UP is coming three times. So this is as we are expecting. Let's have a look one more time of this formula. So here basically we are going to hold the location for that particular row. So first time it will holding the Mumbai on this current ID variable. After that we want to return where we can see the count rows. This is our existing formula and we want to filter this on this table which is our seat 1. That should be equals to the variable which is holding the current location. That should be equals to the location which is available in our table. So Mumbai it will hold over here. It will search the Mumbai how many times it's coming in this table. So that is doing this count rows and it will filter only that particular row. So for example here it will filter for Mumbai first time. So let me put the filter for Mumbai and here we can click on OK. So it will have only two rows. So in this formula you can see it will get only two rows. Like it will filter first after that it is counting the rows. So here it will find two rows that's why it is returning the two rows. That you can see right in this column. So this is very easy. After that what next we need to do we need to have one more column that will have the new location. So let me add a new column and here let me call this as location new that might be this one. After that we can use the if formula if seat 1 count of this duplicate location is equals to 1 then it should be others. Otherwise we want to use the same location. Now we can close the bracket and put the enter. So in the new location you can see we are having the Mumbai Mumbai then others others because it is having only one. Then Bihar UP others like that. So hopefully you have understand how we can create these two columns. Now go to on the visualization and here go to on the pie chart and let me try to make it bigger. Next what we want to do here you can see the location new. So this location new should go in the listen so we can drag and drop it here. After that here you can see the ID. So this ID should go in the values like counting these so we can check this and it's counting. So here we are having the same value as we were expecting. So hopefully you have understand how we can do that. Next what we want to do we want to have one more filter over here in the slicer. So let me add a slicer and in the slicer let me try to make it little bit smaller. And here we want to use the date column so that is the PLC date. So we can drag and drop this PLC date in the slicer. Now let me make it as list. If we will select any particular then here we can see only UP and if we select any another. So that should be working according to the selection of this. So how we can do that the selection of that slicer value. As of now it will not gonna work because here what we have created that is the static value like counting for duplicate location. It is the static value it will not gonna check for any particular date value. So next we wanna put the filter for the date value. So that will be seen in the next video. 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. Thank you so much. See you in the next video.