ALL DAX function - Power BI Tutorial

- [Instructor] The ALL and ALLSELECTED DAX functions remove filters applied to calculations. These functions are helpful for calculating overall totals as well as percentages of a total. It's important to note that neither ALL nor ALLSELECTED are standalone functions and measures, but rather they work in conjunction as part of an aggregate calculation. These functions can reference an entire column or they can reference an entire table. It just depends on the filters already applied to the calculation or the visual we're adding them to. Both ALL and ALLSELECTED override the pivot coordinates at which we're evaluating the measure at. The difference between ALL and ALLSELECTED is the way they negate filters. The ALL function overrides any external filters including slicers. While the ALLSELECTED function uses filters like slicers to enable the end user to determine the filters over which they want to calculate the result. In Power BI let's create another measure to calculate the maximum loan period value over the entire loan period table. Let's create this new measure using the CALCULATE function where we'll reference the loan periods in the first parameter as we see with the existing measure using the CALCULATE to determine the loan period of the pivot coordinates in the model. For the second parameter filters, we want to use the ALL function to override the pivot coordinates for the loan periods we already see in the loan period table that we'll add them to. We'll then add this measure to the existing loan period table where the dimensions of the loan periods serve as the backbone for the model we're calculating it against. We see it returns 360 for all the loan periods. This represents the total number of periods in loan for each of the pivot coordinates because the ALL function overrides those pivot coordinates in the calculation. Once we calculate this measure for the total periods in the duration of the loan, we can use it in other calculations. Let's create another measure where we subtract the current period of the loan that we determined using the parameter harvesting formula from the total periods measure we just calculated. Once we add this measure to the same loan period table, we can see that it works. We see it works in the opposite direction of the total loan periods, and that as the period in the loan gets larger, as we're counting up, the periods remaining goes down by the same unit of one. For each of these rows, we see the total adds up to 360 which represents the total periods in the loan. Because stocks' measures are independent, this means they're evaluated against the data source and not the pivot table because they use the same iterative process for each pivot coordinate in the visual. This also means that even if we're subtracting one measure from the other, the result of the third measure isn't dependent on either of the two measures that go into the calculation being in the visual, because they're all calculated independently. So if we remove the total periods in the loan, we would still be able to get the periods remaining properly calculated. Let's leave it in the model though. Let's also create a very similar calculation to show how the ALLSELECTED function works as a filter, by first copying our total loan period formula and to noting this calculation with an A at the end of it. We'll call this the selective periods in the loan. We'll then change the ALL function in the second parameter to ALLSELECTED instead and add it to the same table. Now let's add a slicer visual for the loan period. When we adjust the selections in the slicer, we see how it changes the results we see in the last column of our table because the maximum of the loan period depends on the selection in the slicer. Especially as we adjust the top end of the slicer below 360, we see how this changes the calculation using the ALLSELECTED function because we're selecting from a narrower range of loan periods, but it doesn't change the calculation for the total periods in the loan, which uses the ALL function instead.