ALL() leads to wrong calculations

[Anonymous]

Hello all,

I am experiencing some problems with the ALL() function. 

In a few words, I am want to display the total sales + market shares for a few item from field 1. 
- To do so, I am filtering my table to display only the wanted items.
- Also, in the DAX formula for my Market Share, I used the ALL() function for the denominator to take into account all the items in field 1, and not only the one filtered. Here it is
Denominator =  CALCULATE ( SUM ( TABLE[Sales] ), ALL ( TABLE[field 1] ) )

=> However, I noticed that this calculation is lower than it should be, as if Power BI was not taking into account all the lines in my table. 
=> Randomly, I added an item in my table (which accounts for a large number of lines in my TABLE). Then the calculation was correct as if this forced Power BI to take into account all the lines in my TABLE when calculating.

I looked through the Microsoft documentation (https://docs.microsoft.com/fr-fr/dax/all-function-dax), it did not help since it does not seem to be a problem of "Dax auto-exist' (https://www.sqlbi.com/articles/understanding-dax-auto-exist/).

Many thanks for your help !!

[plainly]

Hello,

I typically approach this type of challenges making things as simple as possible. Make a new data model where you have only the table in question, and add there exactly only this one measure without any filters. 

Filtering in DAX is slightly tricky, and testing if something works with a filter is likely not the first step to test if all values all returned. For example the following deliver the same result no matter how you filter with Power BI report filters product table.

• The KEEPFILTER function does not override the existing set of filters. Instead, it uses the intersection of values present in

KEEPFILTERS(FILTER(ALL(product[column] ); product[column] > 10)  
= FILTER(product;product[column] > 10)
= KEEPFILTERS(product[column] > 10)

However:

• The FILTER function overrides any existing set of filters on a column applied via slicers. So now if you filter by product table values will not change.

CALCULATE([measure];product[column] > 10)
= CALCULATE([measure];FILTER(ALL(product[column]);product[column] > 10))

See more interesting content on my Blog: https://www.plainlyresults.com/tag/dax/

[Anonymous]

Hello @plainly  and thanks for participating.
Unfortunately, your comment does not help. My problem here is not a matter of filtering but a bug issue linked with the use of ALL() function. 

FYI, I have attempted to use the REMOVEFILTERS() function but it does not solve it.

[CNENFRNL]

@Anonymous , your measure

Denominator =  CALCULATE ( SUM ( TABLE[Sales] ), ALL ( TABLE[field 1] ) )

only assures nothing but filters on TABLE[field 1] are removed; any other filters on other columns still affect calculation of SUM(TABLE[Sales]).

[Anonymous]

By the way, I have understood that any other filters than TABLE[field1] are active. It is the purpose of ALL() function.
Anyway, this sum() does not give the right number... Power BI is facing a "bug": the result of such sum() evolves when I change the selection filtered on TABLE[field1]. It should not be the case due to the use of ALL() function

[Anonymous]

Thank your for your answer and your help @CNENFRNL !

You are right, but this is not a matter of filtering. Proof is the calculation becomes true when I randomly add an item in my filtered selection. I think this is due to a Power BI optimization bug linked with the use of ALL() function ...