 Hey guys welcome to SS Minute Tech, so see in this side and today we are going to see one of the real time interview question and answer. So this question was asked in PwC interview. So recently one of my friend has attended interview in PwC so this question was asked over there. So let me quickly go inside the YouTube so I have already uploaded few of the videos which is based on that interview only. So you can go on the YouTube and you can search for PwC Power BI interview questions and answers and after that my YouTube channel name. Then you will see the first video which is scenario based question. So this question was asked on the same interview. So I would strongly recommend to watch this video as well. So let's start with today's video. So in this video what we need to do? So here as we can see we have this slicer. So this slicer is having the values which is one two and three and here as well the columns are one two and three. So once we select any one of this then we can see the color of this column has been changed. Once we select two then the color of top two will be changed. Once we select three then top three color will be changed. So accordingly this will be changing. So first we have to create this slicer because this is the dynamic slicer. If the column will be having four here then the value that should be four instead of three. So instead of static it should be dynamic the first case. The second case we will be implementing the row numbers that we can see here in this table and this row number is totally based on the total sale which is the value here. So if the value is having the highest for the UK so rank will be one. For the US the second highest so it is two and then India it is three. So once we will be implementing this and at last we will be implementing for this coloring option. So let's start how we can do that. So I am having a dummy file so where I have imported this data model and in this data model we have this table. So the first thing that we need to create is slicer here. So that slicer should be the dynamic and the column values we are having here which is a country. So if the country will be having five distinct values then the slicer value should be starting from one to five. So for that we have to create a new table by using the dash. So we can go into the data modeling side here modeling tab and we can see this. So we can click on this. So we need to write a dash expression to generate that sequence from one to whatever the number of countries. So let me call this as country list. Now here we have to use the generate series. So this generate series will be going to start whatever the starting value we are passing whatever the end value we are passing and what will be the incremented value. So it will be generating the sequence. So it should be starting from one and the end value. So end value we cannot specify any specific value. So this should be dynamic. So we can use the distinct count function of the country column. So we can select that and after that we can incremented one by one. Let me put it in. So this will be generating a new table and the sequence will be one, two and three because total three countries we have here. So in the country list that should be having total three values that we can verify. So this is the first part of our question. So let me try to use a slicer now. Let me format the slicer a little bit and after that let me use the value column here and after that let me make this as list. So the first part of the question is done. Now the second part of the question that we need to generate a rank here and here let me try to create a new measure for creating the rank. So as we can see this measure, so we need to write the formula for creating the rank. So let me call this as rank value and here for creating the rank we need to use the rank x function and under the rank x function first as we can see it is asking the table. So we need to specify the table here. So the table which is the sales table so we can directly specify the table name then it is asking the expression. So the expression will be having the summarize of the value column. So if we are going to use the sum directly here so that will not work because it will be going to check row by row. So we don't want to check row by row. We want to check on the entire table. So for checking the entire table first like in this table we have to use the all. So it is not going to indicate for any particular row the expression that we are applying on total column. So second we need to use the calculate function then the expression will be the sum of the value. So we can select like this then we can see the value. So that is optional. Let me skip that order we need to specify descending order and after that we can close the bracket. So let me put enter. So it is generating the rank but the problem is that we will see here let me try to drag and drop this rank value in this table. So here we will be going to see the rank the new column will be added but the rank we can see 1, 2 and 5. So why this is pretending like that because if we can go in the data model then the rank which is generating on this table. So whenever we are generating the rank on this table so mainly if we can drag and drop the city value here as well then it will more clear. So now we can show you that. Let me sort this. So now we can see it is sorted according to the unique combination of the country and city. So that is why we are returning 1, 2, 3, 4, 5 like that. If we are removing this city column here the rank which is not updated. So the main concern how we can do that for this ranking. So for that we need to go on the rank value measure and here we can see we are supplying all the table. So instead of supplying whole table we can supply only the column by which we want to generate the rank. So we want to generate the rank on country column so we can specify the country here and put enter on this. So now we will see this rank will be updated and it will be having values like 1, 2, 3 as we can see. So now it has the proper rank value here. Now the last requirement is so we want to change the color of this column by selecting the values from the slicer. So how we can do that? So for that we have to create one more measure and under that measure we will be writing the expression. So go to on the new measure and let me call this measure as color code. Now here we need to check if then the logical test. So what is the logical test? We need to check the rank and the selected value from the slicer. So first let me use the selected value. So selected value from the country list value. So this is the slicer value. If this value is greater than or equals to the rank which we have generated so that is the rank value last column that we have generated. If the rank value is greater or equals to of this country value that is selected one then we want to make the color as green. So let me use the green here otherwise we want to make this as blue something like this. Let me put it in here. So we have generated this expression and we can use this expression on the dynamic coloring option. So we can select this visualization that is the column chart go to the format and here we can see the data color. So default color we can see this. So instead of the default color let me click on this expression and under this expression we can go on the format by and need to select the field value. So the field value that we have generated the color code measure. So we need to select the color code measure here. So this is under the sales table and we need to select this color code. Once we select this measure everything has gone. We can click on OK directly. So now we can see this is all in blue color that is OK. Once we click on one then it should be filtered and only top sale will be having the green color. Once we select two top two will be having the green color. Once we select three then top three will be having the green. So this is the question was asked and this is very important question and we can say this is the complex as well. So let me recall what we have done in this video. So first we have generated this series as per the distinct countries and after that we have generated this rank and this rank is based on the country. So let me recall the rank function again. So we have used the rank X and after that all along with the country. So this will be checking for all these countries and then calculate with the sum function. So I will record another video where we will explain why we have used the calculate over the sum. So directly we cannot use the sum in this case. So we will make a separate video on that. So thank you so much for watching this video. See you in the next video with new scenario.