Forum Discussion
All() function doesn't work properly in calculate function
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:
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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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
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 _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.