topic Understanding Expanded Tables: removing filters with ALL() in DAX Commands and Tips

Understanding Expanded Tables: removing filters with ALL()

Anonymous — Tue, 26 Oct 2021 21:29:33 GMT

Hello,

Please consider the following measures in this simple scenario: Product and Sales tables, with a one-to-many relationship.

NumOfProducts = COUNT( Product[Product Name] )

NumOfAllProducts = CALCULATE ( [NumOfProducts], ALL ( Sales ) )

Why does the NumOfAllProducts measure return the total number of products regardless of the color filtering?

Color is an attribute of the Product table. Yes, it does also belong to the expanded table of Sales, but here we are not using the expanded tables of sales, since we are only removing filters from it. Why does this affect the behavior/outcome of the measure?

If it was using the expanded table of Sales the total number of products would be different, it would only show the number of products with Sales (it is less than the total)

What expanded table is this measure using?

I believe that this ALL() filter should not affect the measure in this case. What am I understanding wrong?

Thanks

Re: Understanding Expanded Tables: removing filters with ALL()

AlexisOlson — Tue, 26 Oct 2021 21:42:23 GMT

This is discussed here:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

"When CALCULATE evaluates the filter in the denominator, it finds ALL. ALL requires the removal of any filters from the expanded Sales table, which includes Product[Color]. Thus, the filter is removed but no result is ever returned to CALCULATE. Because no result is returned, the expanded Sales table is not used as a filter by CALCULATE."

Since Product[Color] is part of the expanded Sales table, the calculation removes the only filter we have. Since NumberOfProducts is defined as a distinct count of Product[ProductName] and there are no remaining filters, we get all of the products, not just the ones in Sales.

Remember, when used like this, the only thing ALL does is remove filters, not add any.

"In the very specific case of CALCULATE filters – and only in this specific case – ALL is not used to retrieve values from a table. Instead, ALL is used to remove filters from the filter context."

Re: Understanding Expanded Tables: removing filters with ALL()

Anonymous — Tue, 26 Oct 2021 22:01:18 GMT

Hello,

Thanks for your answer.

I don't understand this reasoning: Since NumberOfProducts is defined as a distinct count of Product[ProductName] and there are no remaining filters, we get all of the products, not just the ones in Sales.

Maybe I'm understanding wrong fundamental ideas.

What is the difference between counting products from the expanded table of Sales, and counting products using the expanded tables of Sales as CALCULATE filter?

I thought that using it as a filter in CALCULATE only results in using that expanded table to compute the measure (and getting a DISTINCT COUNT of the values of rows that are referenced, like a Sales LEFT OUTER JOIN Product) .

With ALL() removing filters, it looks like a Sales FULL OUTER JOIN Product.

I know that is dangerous to think in terms of SQL concepts.

Re: Understanding Expanded Tables: removing filters with ALL()

AlexisOlson — Tue, 26 Oct 2021 22:43:54 GMT

The Product table has more products than exist in Sales and the NumOfProducts counts the rows in Product that are the current filter context. So, without any filter context, we get all of the products regardless of if they exist in Sales.

As an argument in CALCULATE like this, ALL removes filters, nothing else. The visual introduces the Product[Color] as filter context for each row but since this column is in the expanded Sales table, it gets removed by ALL ( Sales ) and there are no remaining filters, thus nothing limiting Product to only the products in Sales. The actual Sales table itself is never part of the query in this example.

In practice, you would probably write the measure as

NumOfProducts = CALCULATE ( [NumOfProducts], ALL ( Product ) )

to avoid confusion.

Everything in DAX is about columns and filters. Tables are important concepts but not as fundamental.

Re: Understanding Expanded Tables: removing filters with ALL()

Anonymous — Wed, 27 Oct 2021 13:49:29 GMT

Thanks again for your patience.

I understand what you mean, but still there is something that prevents me to 'click' it all together.

The expanded table of Sales contains Product columns. When applying ALL(Sales) we remove filters from the Product derived column, then, in this context, we always count all posible products in Product. Fine.
Are these Product columns the same that belong to the Sales expanded table? Is there any difference? Are they different objects? If they are the same, why are there fewer products in the Sales expanded table?

I mean, I have a bit of confusion here. Why it counts all possible products when using ALL(Sales), so using the expanded table of Sales. But then when you use the expanded table of Sales as a CALCULATE filter there are fewer objects. I know that are the only that have references in Sales table, but one more time, it is like it has two behavious, as a Sales LEFT OUTER JOIN Product and then as a Sales FULL OUTER JOIN Product.

And, could you explain what rules does this measure follow?

Test = CALCULATE(COUNTROWS ( 'Product Subcategory') , Sales, ALL('Sales'))

Is this nonsense? It behaves like ALL('Sales') has no effect. The outcome keeps being affected by Product columns slicers, etc.

Thanks one more time and excuse my stupidity.

Re: Understanding Expanded Tables: removing filters with ALL()

AlexisOlson — Wed, 27 Oct 2021 15:36:37 GMT

Your measure expression is not nonsense. The ALL(Sales) part removes filter context but you also have Sales as a table filter, which limits the rows of Product to ones corresponding to Sales (or whatever subset of Sales is in the current filter context).

The wall you are running into is trying to interpret ALL(Sales) as a table at all (it can be, but isn't in this context). It doesn't correspond to any kind of join of Sales and Product. It doesn't represent anything except instructions to remove certain filters.

Here's the relevant part of the article I referenced earlier:

This formula CALCULATE ( [SalesAmount], ALL ( Product[Color] ) ) reads as follows:

ALL returns a table containing all the colors; this table represents the valid colors to be used in the new filter context of CALCULATE. Forcing all the colors to be visible is equivalent to removing any and all filters from the Color column.

This description consists of two sentences: both are wrong. This is not to say that the description is completely wrong. It is accurate most of the time, but not always. The correct description of the behavior of ALL in the PctOverColors measure above is much simpler indeed:

ALL removes any active filters from the Color column.

In the correct description there is no statement about the result of ALL – in fact, it does not return anything – and there is no equivalence between a table with all values and the removal of a filter. The reality is much simpler: filters are removed. At first sight, it looks like a very pedantic detail. However, this small difference may yield very different results when used in more complex DAX expressions.

You seem to be understanding things more like the first (incorrect) description.

The expanded Sales table uses LEFT OUTER JOIN for tables it has a many-to-one relationship with (see here). ALL(Sales) is not an expanded table but it does remove all active filters on the expanded Sales table.

A couple more selected quotes:

Using ALL ( Sales ) does not mean, “filter using all the rows in Sales”. It means, “remove any filters from Sales"

It is worth noting that all the tables used as filter arguments are, indeed, expanded tables. Therefore, the action of removing filters impacts not only the base table but the entire expanded table. ALL ( Sales ) acts as REMOVEFILTERS on the expanded version of Sales, removing filters from the table and from all related dimension.

Re: Understanding Expanded Tables: removing filters with ALL()

Anonymous — Wed, 27 Oct 2021 17:54:22 GMT

I understand ALL(Sales) doesn't return any table, and that only removes filters.
The article mentions that it removes filters from the Sales base table and the derived tables, which includes Product table.

If I count then the products of the table using NumOfAllProducts I find all the products. But if, in another measure — CALCULATE( [NumOfProducts], Sales) — I use the Sales expanded table as a filter and count the products, I only find products that have sales.

I think I can group all my doubts: why does this happens given that in both measures we are counting rows of the same object, the derived Product table?

Re: Understanding Expanded Tables: removing filters with ALL()

AlexisOlson — Wed, 27 Oct 2021 19:20:26 GMT

It's not the same object. In one case, we are counting rows in the Product table without any filters, and in the other, we are counting only the rows that correspond to Sales. It's like counting Product versus counting Product INNER JOIN Sales.

Re: Understanding Expanded Tables: removing filters with ALL()

Anonymous — Fri, 29 Oct 2021 01:49:11 GMT

Anonymous

Measure calculates with current row context, when you add all to remove all filters, the context becomes the all table, so it is no long calculate based on the current row context. Instead, the result is based on the entire table rows. Hope this makes sense.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.