The ALL function is an extremely important part of the DAX language in Power BI. It can be used in a number of different ways within formulas that allow you to calculate results seamlessly when combined with the filtering available in the data model.
Simply put, the ALL function can remove any context that is coming from any filter or dimension that you have aligned to a formula. This is perfect in situations like when you want to calculate the percent of a total.
For example, say you want to calculate the percentage of sales in a particular region versus all sales ever made. To do this logically, you need to divide the sales for the region by the total sales across all regions. Getting sales for every region though is not that easy with the natural filtering that will occur in your models.
This is where the ALL function comes in perfectly to solve this for us. When combined with CALCULATE you can remove any region filters and get the correct denominator in place so that the calculation will be correct.
Think of the ALL function as like the ‘remove all filters’ function. You can place either a table or column in here, and it’s important to understand the effects of doing either.
I go into this in more depth in this video. Check it out to learn more.
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online