The All function is a versatile tool for removing the filter and performing calculations on the entire dataset.
It’s primarily used to count the percentage of specific data based on the total amount. But sometimes, users misconfigure the function and face issues with the All function.
In this article, I’ll demonstrate the reasons and provide tested solutions to fix Power BI DAX All function not working issues.
So, let’s start.
Why is DAX ALL Function Not Working As Expected in Power BI?
The DAX All function isn’t working because the selected table column is filtered. In addition, if the columns are sorted in an order, the All function won’t work without the correct equation. Also, incorrect calendar hierarchy will lead the All function not to work.
The All function depends entirely on the measure. The problem appears when you type an incorrect function in the measure tab.
Here are the reasons behind All function not working issue in Microsoft Power BI:
Table is Filtered
The data in the columns are filtered by default. The All function doesn’t apply when the filter is active in the table.
Column is Sorted
Many users sort the table for better visualization. But sometimes, the sorted table column collides with the All() function and doesn’t provide the expected results.
Let’s check this with an example.
Here, the ProductCategoryName is sorted by another column.
Therefore, when applying the All function for the ProductCategoryName, it isn’t generating the Total amount in the All Sales column.
Incorrect Calendar Hierarchy
When using date as a column, you must provide an accurate date hierarchy to get correct results from the All function.
The following example will clear the confusion about the date hierarchy.
Here, the filter is set to Year (not just date) for the table.
But when you see the measure function, you’ll see the All function is applied for the date. As a result, the All function isn’t providing the Total amount in the All Sales column. 
All the above reasons are straightforward to fix, and the following passage will provide the accurate process to input the function.
How to Fix DAX All Function Not Working in Power BI
To fix the All function not working issue in Microsoft Power BI, you must clear the table filter and include sort-by-column information with the All function. Also, you need to use the correct date hierarchy for Year, Month, and Day with the Date in All function.
Ensuring the accuracy of the function is all you need to do to resolve the All function error. I’ll show you how to use ALL in a DAX Expression in Power BI in the following passage.
Here are the methods to fix DAX All function not working issues in Power BI:
1. Include Sort by Column Function
If you have set column sorting for the table, you must add the sort function in All.
In my test table, the data name is dimProducts. I’ll show how to mention the sort by order function inside All(). You can follow the procedure and apply it to your table.
Here are the steps to include Sort by Column in All function:
- Double-click on the Data name from the right side to open the measure window.
- Type the following function:
All Sales =
CALCULATE(
SUM(fctSales[SalesAmount]),
ALL(dimProducts[ProductCategoryName], dimProducts[ProductCategory - SortOrder])
)- Press Enter to save the changes.
It will fix the inaccurate numbering in ALL Sales columns and show the same number as Total.
2. Use the Correct Date Hierarchy Function
The Date contains information about the Year, Quarter, Month, Day, etc. So, you must specify the information in the All function to get the correct number in the All function column.
I’m only using the Year for the All function in my table. Therefore, I need to mention the Year data name in the function.
Follow the process below to use the correct date hierarchy function:
- Double-click on the All function data name.
- Type the following function and mention the date hierarchy correctly.
All Sales =
CALCULATE(
SUM(financials[ Sales]),
ALL(financials[Date].[Year])
)- Press Enter to apply the changes.
Now, the All function will generate the same number as Total in the All Sales column.
Wrapping Up
The All function is necessary to clear filters and find the average, total, percentage, etc, in the entire dataset.
Sometimes, an error in the function can lead to a problem with the All function. But you can check and add Sort by Column and Date hierarchy to fix the All function not working issue in Power BI.
I hope you’ve followed the examples and managed to resolve the problem. Cheers!
