ALL Function in Power BI – How To Use It With DAX

No comments

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? *****
All Enterprise DNA TV Resources
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – Power BI Resources

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s