topic Re: ALL showing an unexpected behavior in a matrix in DAX Commands and Tips

ALL showing an unexpected behavior in a matrix

zoubeyrB — Mon, 09 Sep 2024 11:37:04 GMT

Hello,

I'm working on a dataset based on the following data model (well, it's part of it) :

Il created the following measure to calculate the sum of quantity without taking into account the product's sub-category :

Measure = CALCULATE(sum(sales[quantity]),ALL(products[product_subcategory]))

When the measure is used in a matrix view it shows a peculiar behavior :

Both visuals (A) and (B) are filtered on the products' prices (slicer in the bottom left) and of Product_Category = "Office Supplies". Table (B) matches the expected behavior, which is not the case for table (A).

Note that using ALLSELECTED instead of ALL fixes that issue.

When the slicer is based on a calculated column in the sales table
( product_price = related(products[product_salePrice]) ) the expected behavior is shown in the visual (A).

Is that the normal behavior for ALL ?

Thanks in advance,

Re: ALL showing an unexpected behavior in a matrix

lbendlin — Mon, 09 Sep 2024 13:27:34 GMT

Any sort column involved?

Re: ALL showing an unexpected behavior in a matrix

zoubeyrB — Mon, 09 Sep 2024 15:02:24 GMT

No. I think that DAX aggregates on product_saleprice as well. If I make the following calculations I get the same results :
1) initial dataset ==> filter on product_saleprice > 60 ==> aggregate on product_Category and product_saleprice and calculate the total quantity for each combination of values (sum1)
2) initial dataset ==> filter on product_saleprice > 60 ==> select distinct values of product_Category, product_subCategory and product_saleprice

3) join results from steps (1) and (2)
4) result of step 3 ==> aggregate on product_Category and product_subCategory and calculate the sum of sum1

The calculation seems coherent (filter then aggregate on all fields excluding product_subcategory), but the viewed result is misleading. A more correct result can be obtained from the following formula :

measure2 = CALCULATE(sum(sales[quantity]),
                       ALLSELECTED(products[product_subcategory],products[product_salePrice]))
                    )

Re: ALL showing an unexpected behavior in a matrix

FlipFlop1 — Mon, 09 Sep 2024 14:43:52 GMT

The ALL() function used in as a filter in Calculate is removing the filter on product_subcategory, so it works on Matrix B which uses product_subcategory in the first row,
but not matrix A which has product_category in the first column. Matrix A is not being filtered by product_subcategory, so there
is nothing to remove.

ALL(products[product_subcategory]

Re: ALL showing an unexpected behavior in a matrix

Anonymous — Tue, 24 Sep 2024 07:22:42 GMT

Hi all ,thanks for the quick reply, I'll add more.

Hi zoubeyrB ,

Regarding your question, this may involve the underlying principle of the 'ALL' function. When I removed the slicer and used the filter condition of 'price > 60' as the parameter of the Calculated function, the 'ALL' function worked properly, so I think if a column is affected by indirect filtering, the 'ALL' function cannot clear the filter.

Best Regards,
Wenbin Zhou