Why ALL function is needed when I use column in a FILTER function but not needed when use table?

[Nietzsche]

Hello, 

I am new to DAX and am currently reading the Definitve guide to Dax.

Why is it that in the FILTER function, if I use the entire table in the first paramater, I can run the query with or without wrapping the first parameter in ALL function like this

However, is the first parameter is a column, I have to use the ALL function, otherwise I get an error

why is that?

[OwenAuger · Accepted solution]

Hi @Nietzsche 

The first thing to note with the FILTER function is that the first argument must be a table.

These are both valid table expressions:

• ALL ( 'Product' )
• 'Product'

When no filters have been applied, ALL ( 'Product' ) is equivalent to 'Product', except that ALL ( 'Product' ) will contain an additional blank row (corresponding to values in tables on the many-side of a relationship with 'Product' that do not exist in 'Product').

For your first two DAX queries, when no filters have been applied, the queries both return the same result (since this particular filter condition automatically excludes the blank row if it exists):

EVALUATE
VAR test =
    FILTER ( ALL ( 'Product' ), 'Product'[Color] = "Red" )
RETURN
    test

EVALUATE
VAR test =
    FILTER ( 'Product', 'Product'[Color] = "Red" )
RETURN
    test

For your second set of queries, 'Product'[Color] is a column reference, not a table expression, so it is not valid as the first argument of FILTER.

You can convert a column reference to a table containing the values of that column using functions such as ALL, VALUES or DISTINCT.

• ALL ( 'Product'[Color] ) returns a single-column table containing all values that exist in the column 'Product'[Color] ignoring any filters that may exist, including the possible blank value.
• VALUES ( 'Product'[Color] ) returns a single-column table containing all values that exist in the column 'Product'[Color] within the filter context where the expression is evaluated, including the possible blank value.
• DISTINCT ( 'Product'[Color] ) is similar to VALUES ( 'Product'[Color] ) except the possible blank value is not included.

Two queries that return single-column results in a similar fashion to the first two queries would be:

EVALUATE
VAR test =
    FILTER ( ALL ( 'Product'[Color] ), 'Product'[Color] = "Red" )
RETURN
    test

EVALUATE
VAR test =
    FILTER ( VALUES ( 'Product'[Color] ), 'Product'[Color] = "Red" )
RETURN
    test

-- OR --

EVALUATE
VAR test =
    FILTER ( DISTINCT ( 'Product'[Color] ), 'Product'[Color] = "Red" )
RETURN
    test

If you are reading The Definitive Guide to DAX, the authors will cover this more formally at some point I believe 🙂

Please post back if needed 🙂

Regards

[v-linyulu-msft]

Hi,@Nietzsche 
1.Regarding your question, this is because the first parameter in the Filter() function is required for the table and then the All() function returns the deleted table or column with the filter.
If you use 'Product'[Color] directly as the first parameter of the Filter() function, it does not meet the requirements of the required parameters of the Filter() function.

2.Below is a document of the relevant content, which I hope you will find helpful:

ALL function (DAX) - DAX | Microsoft Learn

FILTER function (DAX) - DAX | Microsoft Learn

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