All() function doesn't work properly in calculate function

[Anonymous]

Hi, 

I am making a consolidated sales report where I calculate also the Intercompany margins.

So, I created a column customerType where you can see when the sale is to a third party, supplier/customer or its an intercompany sale. For the margin we want the intercompany margin in there, but off course not the sales and volume. 

for the actual margin its okay, but the measure of the margin budget is changing when you deselect the intercompany. So, we want all the budget in there. Therefore, I created a measure with calculate and all like this: 

MARGIN BUDGET 2023 = CALCULATE(SUMX(Sales,Sales[BU23 MARGIN Corrected by BEN]),ALL(Sales[CustomerType]))

But when I then click deselect the intercompany sales (via the filter customerType), the margin budget is changing. 

Why is this happening and what am I doing wrong in this measure. 

Thank you in advance!

Stijn

[Anonymous]

Hi, 

In the meantime I have found the solution myself. And it was indeed because of the all() was used in the same fact table. So, I created a live connection from it into excel and then uploaded that table as a dimension table. Related it to the fact table, changed the all function to the filter in the dimension table. And the case was closed. 

Thanks for this additition. 

Kind regards, 

Stijn

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

It may be that your data is too complex generating auto-exist reactions.

The normal behavior for DAX expressions containing the ALL() function is that any filters applied will be ignored. However, there are some scenarios where this is not the case because of auto-exist, a DAX technology that optimizes filtering in order to reduce the amount of processing required for certain DAX queries. An example where auto-exist and ALL() provide unexpected results is when filtering on two or more columns of the same table (like when using slicers), and there is a measure on that same table that uses ALL(). In this case, auto-exist will merge the multiple filters into one and will only filter on existing combinations of values. Because of this merge, the measure will be calculated on the existing combinations of values and the result will be based on filtered values instead of all values as expected.

More details: ALL function (DAX) - DAX | Microsoft Learn

You can learn the following document about auto-exist:

Understanding DAX Auto-Exist - SQLBI

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.