Forum Discussion
Does function FILTER() work with another function given as a parameter?
Hi,
I’m trying to calculate table B from filtered table A (the filter condition is a cutting date):
Does anyone know what I'm doing wrong in case B?
In your file Table3 is a calculated table ...
Calculated tables are static parts of a model : their content will not change based on selections or slicers.
You can use table expressions in measures to obtain dynamic selections, though ...
6 Replies
This may be related to: http://www.sqlbi.com/articles/usage-of-dates-argument-in-a-row-context/
The short explanation is: FIRSTDATE has an implicit CALCULATE and is evaluated depending on the row context of TableA.
If correct, your condition probably evaluates to TRUE for each row.
Try with the MIN function instead.
Hi Laurent Cuarto, first of all many thanks for your prompt response.
I would like to apologise for don't explain correctly the situation before.
You can see in Case C the real problem. In fact, once I give the measure to the filter function, the filter doesn't work as I want.
It seems that the filter function doesn't accept a measure
The article I linked to explains this in details.
For the short explanation ...
FILTER( Table, Table[date] <= FIRSTDATE(Dates[date]) )
FILTER( Table, Table[date] <= CALCULATE(MIN(Dates[date])) )
FILTER( Table, Table[date] <= [MINDATE measure] )
... will all give you unexpected results because the right-hand side of your filter expression is calculated within the row context (it returns the date for the current row, that is Table[date]).
As a consequence, your inequality always evaluates to true.
On the contrary, this should work:
FILTER( Table, Table[date] <= MIN(Dates[date]) )
The reason is this MIN(Dates[date]) is calculated for the current filter context your measure is evaluated in returns the same result for all rows in Table. As a consequence, it will be true for some rows and not for others.