Forum Discussion
FILTER vs CALCULATETABLE based on Boolean Expression
Hi Experts,
I am looking to understand why is that for FILTER to work in my code, I have to used DATESBETWEEN as a true/ false expression whereas for CALCULATETABLE to work similarly, it needs to be used as a table filter expression not Boolean
1) With FILTER, using DATESBETWEEN as
This wouldn't work if I change the highlighted portion to just this
It works with the code in blue above and not with how FILTER worked as highlighted by green line
Please suggest!
That's because the second argument to FILTER must ALWAYS be a logical condition (or castable to it) that operates for each row of the table in the first argument (FILTER is an iterator) and all CALCULATETABLE's arguments must ALWAYS be tables. DATESBETWEEN returns a table.
Even the seemingly logical condition in:
calculatetable( A, T[Col] = Value )
is internally translated into:
calculatetable( A, Filter( All(T[Col]), T[Col] = Value ).
So the "logicals" under CALCULATETABLE are just syntactic sugar.
If you read the ultimate source of knowledge on DAX, which is The Book, you'll find the explanation of the use of the seemingly "logical expressions" under CALCULATETABLE that I gave you. No, what the docs mention is syntactic sugar. Only.
Check this out: How CALCULATE works in DAX - SQLBI
Well, Microsoft is just not telling you the whole truth but The Italians do.
5 Replies
That's because the second argument to FILTER must ALWAYS be a logical condition (or castable to it) that operates for each row of the table in the first argument (FILTER is an iterator) and all CALCULATETABLE's arguments must ALWAYS be tables. DATESBETWEEN returns a table.
Even the seemingly logical condition in:
calculatetable( A, T[Col] = Value )
is internally translated into:
calculatetable( A, Filter( All(T[Col]), T[Col] = Value ).
So the "logicals" under CALCULATETABLE are just syntactic sugar.
Anonymous You mentioned that "CALCULATETABLE's arguments must ALWAYS be tables"
Before posting this question, I checked the MS docs, and what I noticed there was the following:-
1) That CALCULATETABLE does allow boolean expressions- it can be either boolean or table expression
2) Boolean expressions must not use a function that returns a tableI am highlighting these points in green
Now, coming back to my question. Because of these 2 points above (which seem cotradictory to your point that they should always be tables, pardon my ignore) I wanted to try and use the boolean expression using IN as used in FILTER in CALCULATETABLE too
If you read the ultimate source of knowledge on DAX, which is The Book, you'll find the explanation of the use of the seemingly "logical expressions" under CALCULATETABLE that I gave you. No, what the docs mention is syntactic sugar. Only.
Check this out: How CALCULATE works in DAX - SQLBI
Well, Microsoft is just not telling you the whole truth but The Italians do.
And here's another explanation from Alberto Ferrari himself: Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI