CALCULATE in DAX #06: Using ALLSELECTED

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
- Ciao friends. In this video, I want to show you how to use the ALLSELECTED modifier in CALCULATE. Let's start with the scenario. I have a report that is showing the color the sales amount and the percentage of this amount against the grand total. This measure is currently computing its result using REMOVEFILTERS. So it's doing a division of sales amount by CALCULATE sales amount REMOVEFILTERS product color. REMOVEFILTERS removes any filter from the column that you specify. Therefore, the effect is that the filter context on blue is removed and you always get at the denominator the grand total. Nevertheless, you need to keep in mind that REMOVEFILTERS removes all the filters, the one which is coming from the current visual but also filters that might come from different visuals. Look what happens if I select some colors like azure, black, blue and brown or gold, the result of the grand total is not 100% and the percentages sum up correctly, but they do not reach 100%. Why that? Because my percentage is removing any filter from the color, the one coming from the current visual, but also the one which might be coming from slicers outside of my visual. If this is not what I want to obtain, I need to use a different modifier. Indeed, if I use REMOVEFILTERS, this is the effect but what I want to do is tell the engine remove the filter from azure, but keep the filter on the four colors that I have selected outside. The modifier that does it is ALLSELECTED. I can use ALLSELECTED product color and ALLSELECTED will remove this filter and keep the outside filter. As soon as I hit Enter, you will see that the percentages will change, they will still be summed up correctly and they produce 100%. ALLSELECTED is useful whenever you want to keep outside filters and remove filters from the current visual. ALLSELECTED right now is working at the column level. So I'm using ALLSELECTED on one column only. Sometimes what you want to do is not keep an outer filter on only one column, but any filter that might be present outside. So, I can use ALLSELECTED product color but I can also use ALLSELECTED product. The result is that I go from a column filter to a table filter, I feel all the filters on the product color and I remove only the filters on any column on the product table which is present in the current visual. ALLSELECTED is very useful whenever you build reports that need to maintain filters from the outside. In other videos we will see other features of CALCULATE. Enjoy DAX!
Info
Channel: SQLBI
Views: 36,735
Rating: undefined out of 5
Keywords: DAX, PowerBI, Power BI, Alberto Ferrari
Id: UehWR5wWWEo
Channel Id: undefined
Length: 3min 15sec (195 seconds)
Published: Thu Oct 15 2020
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.