I am using the ALL function to create a denominator for a percentage calculation, but the results are confusing. When I use ALL(Table[Column]), it seems to only return a list of unique values, ignoring the actual number of occurrences (repeating values) in the original column. This is throwing off my totals because I need the sum of all rows, not just the sum of distinct entries. Why does DAX treat the column this way, and how do I get it to respect every single row in the calculation?
3 answers
The behavior you're seeing is actually by design. In DAX, when you use ALL(Table[Column]), it returns a one-column table containing all the unique values in that column, effectively stripping away the filter context and any duplicates. It acts like a DISTINCT list that ignores filters. If your goal is to calculate a denominator that accounts for every single row regardless of repeats, you should use ALL('Table') (referencing the whole table) or ALLEXCEPT. By referencing the entire table, you preserve the underlying grain of the data. For a percentage, your measure should look like DIVIDE(SUM('Table'[Sales]), CALCULATE(SUM('Table'[Sales]), ALL('Table'))). This ensures the denominator represents the true sum of all records, not just a sum based on a unique list of attributes.
That explains the unique list, but does this mean ALL is less efficient than ALLSELECTED when working with large datasets? If ALL is generating a virtual table of unique values in the background, does that consume more memory than just ignoring the filter on the existing table
Think of ALL on a column as a way to get a "Values" list without filters. If you want the grand total of the whole table, always point ALL to the table name itself.
Spot on, Sarah. I’ve seen so many people get stuck on this when building "Percent of Total" visuals. A great way to visualize this is to put your ALL(Table[Column]) into a Table visual as a DAX Query—you’ll see right away it’s just a unique list. Switching to ALL(Table) is the standard fix in data science workflows to ensure your ratios are mathematically sound across different levels of a hierarchy.
That's a deep-dive question, Mark! Actually, ALL is incredibly efficient because it interacts directly with the VertiPaq engine's dictionary. Since Power BI stores data in a columnar format where values are already encoded as unique lists, ALL just points to that existing dictionary. The performance hit usually comes from how you use that list in a CALCULATE statement. If you use ALL(Table), it’s a simple "remove filter" command, which is usually faster than ALLSELECTED, because ALLSELECTED has to maintain a memory of the user's manual slicer toggles, adding an extra layer of complexity to the query plan.