Why is your ALL FUNCTION not working? ALL FUNCTION, ALLSELECTED, ALLEXCEPT in Power BI!

why why is my all function not working let's have a look [Music] before we tackle my problem let's have a quick look at what the all function does okay so here we have a small table where i show the sales amount for different subcategories and let me now add a new measure where i calculate the sales amount as a percentage of the grand total now to do this i first need to remove the filter contacts that i have here on subcategory name which i can do with all function so that's where you have the grand total for each subcategory and then i can take the sales amount to divide it by the overall grand total now let's see this in practice i'm going to go to my st sales table and add a new measure and let's call this measure all sales i'm going to use the calculate function because this function lets us modify the filter context and then we can take the sum of our sales amount field and now i would like to remove the filter contacts on subcategory name and this i can do with an all function and we're going to refer to the field name subcategory name and that's the close our calculate function and add it to the table now there you go you see we are returning the grand total for each subcategory now done as a natural next step we could then divide the sales amount by the all sales to get the percentage okay so let's now divide the sum of the sales amount by our all sales and then let's format it as a percentage and add it to our table now the all function comes in different variations for example we have all all selected all except now let me just quickly show you what the difference is now let me just copy over the formula that we just wrote i'm going to go back to my all sales measure i'm going to copy the formula create a new measure and just paste it in there let's rename this one then to all selected and the only change that i'm going to make is that instead of all we're going to have all selected let's do the same for my sales percentage measure so i'm going to copy my formula then also here we're going to add a new measure and let's just rename this one to sales percentage i'll select it and then here we divide not by all sales but by all selected sales and let's format it as a percentage let's then also add these two measures to a table so first i'll select it and then sales percentage i'll select it now at this point all sales and all selected sales they return us exactly the same okay the difference comes when we have an external filter so outside of our visualization for example when we have a slicer or when i go to the filter pane and let's say we select all of the subcategories except computers accessories now there is a difference okay now all sales still returns us exactly the same number as before however all selected now only returns the total sales for the ones that we selected in our filter now all sales also still includes the one that we just deselected computers accessories okay and that's why there's now a difference and that's also why we see that we have now seven and here half percent not hundred percent anymore like we have here okay so that's the difference between all and all selected and then there is also all except when is this relevant well let's say if we have our table visualization like this one and we do not only want to remove the filter from subcategory name but also other fields in our product table okay now let me show you if i for example take away subcategory name now and i put in column name for my products table okay i'm gonna put it on rows and you see all sales actually doesn't work anymore because we only are removing the filter on subcategory name not on colony okay so i would need to go back to my formula and then over here also add another field so here i can then also remove the filter context from colony okay and now it works again all right so what if i now would not want to have color name but let's say manufacturer or let's say class name well then i would have to add those columns to my all function so this would one would be one option another option would be to simply refer to the whole table okay instead of individual columns you see that would also work however the most efficient would be to only refer to the columns from which you actually do need to remove the filter context and that's where all accept comes in so i can go here to my all function and let's rename this one then to all accept and here we have the table name that's in products and then i want to remove the filter context from all of the columns except let's say column name and you see that for column name it's not removing the filter context anymore however as soon as i put in let's say any other field from my products table like subcategory name then it works again okay so we have all we have all selected we have all except and there's also a function that's called remove filters now this one is exactly the same as all okay now let me show you quickly so here i'm back in my all sales measure and i'm gonna use now the remove filters function and i'm gonna remove the filter from subcategory name and you see it gives me exactly the same result so you might be wondering why do we have remove filters if it does exactly the same as all well that's because the all function can be used in two ways so it can be used inside of a calculate function and so that it then it removes the filters or it can be used on its own to return a table that only returns let's say the unique values in a column or in a table okay now because it's a little bit confusing these two different applications they thought it's a little bit more straightforward if we have a function we move filters that only does one thing and can only be used in the calculate function okay so now that you really understand what the all function does let's have a look at my problem okay so here we have the table from before with only the sales amount and the change that i'm going to make is i'm going to take out subcategory and i'm going to put in the product category name so that we have the sales amount broken down by the different categories okay so now i want to use my all function to remove the filter contacts on product category name so that i have the grand total for each product category okay so i'm going to go back to my own sales function now instead of referring to product subcategory name i'm going to change this to product category name now let's add this then to our table and you see it doesn't work but this is a problem that i see quite often and that is that the product category name in our data model is sorted by another column okay so let me show you if i go here to put a category name and then i go to sort by column you see that it's currently sorted by a column product category sort order so because i always want to have a certain sort order for my product category names i have to do this alright and when you have a sort by column like we have here then you also need to include that sort by column in your all function so if i go back to my off function here i just have to include the sort order column for for the category name and you see now the problem is solved and i can use it for example in my sales percentage calculation and you see it nicely works okay so let's have a look at another common scenario that i often encounter where people have troubles with the all function all right so here we have the sales amount and we're gonna have all sales okay now we need to adapt it a little bit so that we remove the filter on the okay so i'm gonna go back to all sales and let's now adapt it to our current scenario so i'm going to remove all and let's now refer to our date column and you see nothing happened now the reason is that i remove that only from one column the date column not from the year the quarter the month of the day etc okay now here we have a breakdown by the year okay so you see we have our hierarchy here quite a month today on our visualization okay so if i want this to work on year level i need to go back to all sales and then instead of referring to date i need to select over here the year and then it works again now but what if i expand down one level to the quarter well then i would also need to include the quarter okay or what you can do is simply refer to the entire table on dim date so that it removes any filter on any column in that table okay so in our case we were working with a custom data table but let's say that we don't have our custom data table but we just have fcd sales where we have one date field the date key okay so i'm going to go back to my report where we have the sales amount okay and let's have a look at all sales again so i'm going to add it over here to values now if we have a look at the function then you see we have all and then we are referring to datekeep.date that means we are now using the hidden date table that's power bi generates in the backend okay and you see it doesn't work so i need to adjust this so that we have over here then the year see now works again let's now also include then the quarter and then if we expand down one level again it doesn't work well this is because if we have a closer look then you see that there was also a quarter number okay so this hidden table in the back end has a sort by column for quarter which is the quarter number okay so you also need to include that one if you use it so these were some common scenarios where it can go wrong when you use the all function now maybe you know some more cases then share it with us in the comment section below if you like the content give it a thumbs up and subscribe to our channel and i hope to see you in the next video

Channel: How to Power BI

Views: 19,807

Keywords: all function in power bi, power bi all function, all function vs remove filters, all function dax, allexcept, allselected power bi, removefilters function, allexcept function, allselected function, power bi removefilters, power bi remove filters, power bi allselected, power bi allexcept, allselected dax, allexcept dax, power bi, power bi tutorials for beginners, power bi desktop, allexcept power bi, allselected in power bi

Id: rvpKSH3zRcg

Length: 12min 49sec (769 seconds)

Published: Tue Sep 01 2020