Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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:
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.
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
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:
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.
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
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |