[Music] foreign Jeremiah Hersey here and this video is the last video in a six part series I'm doing on Virtual tables with inside of Dax today we're going to be talking about the summarize columns function now the summarize columns function is kind of a built to be an all-in-one type function where you have the ability to group by just like summarize you have the ability to add columns just like the add columns function but you also have the ability to filter just like calculate table now to summarize columns function is designed to run queries it is not recommended that you use it with inside of a measure and I'll talk about that a little bit later the reason for that though just a quick heads up is that if filter context caused by context transition it's not going to allow our table to filter and so we'll talk about that a little bit but just know that there is a lot with inside of this one function to too much to cover in one video but I'll talk about the different features that this function has so if you want to follow along in the video just click the link down below to download the pbix file and let's go ahead and get started so here I am inside the power bi desktop and once again we're talking about the summarize columns function so we're going to go up here and we're going to create a new table so the summarize columns function once again as the ability to group by like summarize it has the ability to add columns like the add columns function and it also has the ability to filter like calculate table so summarize columns is going to automatically remove any row that has blank values and so what we're going to see here uh is that you know as I'm dealing with the different columns I'm going to be adding with inside of this query that blanks are going to be removed and so something to take into consideration so let's go ahead and build out this table here so we're going to I'm going to call this summarize columns all right and so let's summarize based on let's do the product uh we'll do the class here so we'll Group by product class and we'll also Group by the year coming from our date table there it is and then we can also you know add that expression if we wanted to so we can say all right this is going to be the amount column using double quotes to name the column and then what's the expression going to be well that's just going to be our total sales measure here and so what you'll see is although the there might be some blanks in the class there are not going to be any blanks in total sales any blanks that were in total sales will be removed and so as we look at this results in table we can see they all have values here but let's say that you do not want to you want to return everything even if there's blanks well one thing we can do is add the ignore function with inside of this calculation so if we add ignore what ignore is going to do is it's going to allow us to bring back any blank values that are associated with the amount okay so we're going to use the ignore function here and what you're going to see is that as soon as I return this we're gonna start getting some blank values in the amount column so summarize columns by default removes these blanks but we can bring them back using the ignore function now the summarize columns function once again like I said has the ability has a lot of capabilities that we're not going to have time to discuss some of those capabilities are for roll-up or subtotal columns and so you have the ability using rollup add subtotal and Roll Up group these two functions with inside of summarize columns will allow you to create subtotals once again we don't have time to go through that today but I just want to let you know of the capability so we see the ability to group by specific columns such as product class we can also add new columns such as the amount column here and the third thing that I said that summarized columns has the ability to do is it has the ability to filter and so what I'm going to do is I'm going to go right here after the second category and I'm going to add a filter function and what I'm going to do is I'm going to filter down uh let's do the customer table I'm going to use the all function here to return all the values ignoring any filters that might be applied here we're going to go to the customer table so filter all the customer table what's the filter expression here um let's do the marital status here so customer table we're going to do where the marital status is equal to single okay going to close up that filter function and put a comma there we go all right so we'll go ahead and return this so what you're going to see is that the values here are going to change because now the filter of marital status is now being applied so we'll let that run here for just a second all right so what I want you to do is I want you to keep your eye on this number right here so the class is L for 2005 and the amount is forty one thousand nine forty five now as I change this filter from single to married all right watch the value change here so we can see that the filter is being applied and so we can now see that changing to 37 000. so it has the ability to group by specific columns you can filter you can also add new columns but one thing to take into consideration is that while summarized columns is extremely powerful it does come with a strong limitation it cannot be called if the external filter context has performed context transition okay so kind of get an idea filter the context transition is where the row filter or the filter that's on the row transitions into the filter context of the table that is context transition if that occurs summarize columns will not work so if you're going to be using these functions with inside of measures it is recommended that you use the combination of AD columns and summarize rather than summarize columns if you're just running a query summarized columns is going to work just perfectly for you but if you're going to be using it in a measure you have to make sure that context transition is not performed because it's going to cause your summarized columns function to not work properly thank you so much for joining me in this six part series on Virtual tables look out for my other series coming up I'm gonna do a introduction to power bi series just talk about some Basics how to navigate around the the different tools with inside of the power bi desktop and the power query editor so look out for that thank you so much for joining me make sure you give me a thumbs up on this video And subscribe to my channel for more content see in the next one [Music]