ALL not working as calculate modifier (possible bug?)

[Anonymous]

Hi everyone,

I have a very weird problem which is either due to the fact that I've instantly forgotten how DAX works or it's some kind of bug

I have this two measures in my model

Products =

COUNTROWS(

'Opportunity Product'

)

Test =

CALCULATE(

[Products],

ALL('Opportunity Product'[Product Family])

)

And as expected this is the result in my chart when I put Product Family on the axis



Now I will modify my Test measure, so that it becomes

Test =

CALCULATE(

[Products],

ALL('Opportunity Product'[Opportunity Stage])
)

And I put Opportunity Stage from the same table in the axis
But this is the result now:



Shouldn't the ALL remove the filter in the Test measure?

I swear I can't figure out what is happening. The only difference between the two columns is that Product Family is a "native" column while Opportunity Stage is a calculated column (is a simple RELATED of the opportunity stage field in the Opportunity table)

Is there something else that could be interfering? I'm at a loss

[v-lionel-msft]

Hi @Anonymous ,

In fact, when you add the ALL() function, [test]measure counts the number of rows in the entire table, because the ALL() function will also ignore the filters on the axis in the visual.

But the result of your this picture is very strange. Is your 'Opportunity Product' [Opportunity Stage] column a calculated column?

As tested, the ALL() function can also remove the filters on the calculated column.

Best regards,
Lionel Chen

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

[Anonymous]

It works if I include in the ALL statement to remove the filter also the column that I use to orderby

I guess it's not a bug but I didn't know that to remove the filter with calculate you needed to include also the column that sorts the column you have on the axis

[Anonymous]

This is what is happening basically:

I have recreated Opportunity Stage as last column in my table even though it is used in other columns before

When I try to sortbycolumn with a previous column, every calculated column goes in "error" and the error message tells me that the column I am using to sort needs to be recalculated

I refresh the data and everything goes back to being fine, but when I try to use the opportunity stage column in my axis now the ALL function to ignore the filter context doesn't work anymore

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Yes, if you first use the "Sort by column" function to sort a column, the ALL() function will not remove the filter on this column.

Sort [Brand] column

Do not sort [Brand] column

The reason may be that the "Sort by column" feature also fixes a filter on this column, something like this:

Test = 
CALCULATE(
    [Product],
    FILTER(
        ALL(Sales),
        Sales[Brand] = SELECTEDVALUE(Sales[Brand])
    )
)

Best regards,
Lionel Chen

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

[Anonymous]

I literally just CTRL+C the code, deleted the column and recreated with CTRL+V and now it works

I guess it was a bug? I don't know. I'll wait on a moderator and then eventually delete the post 

Very weird behavior though