Forum Discussion
'FILTER' syntax is suddenly incorrect
Hi all,
I've been working with this particular report for quite some time. I created it ages ago and it has been reloading on a daily basis and people in my company are using it quite often. But as I open the report in PBI desktop, there is this one measures that throws up an error about the 'FILTER' syntax when I click on it. That's quite weird since the report is working beautifully on the Report Server and reloading on a daily basis without any issues. I'm 100% sure that this error wasn't there when I first created the report and I haven't made any changes since then. I've been staring at the formula in this problematic measure for some time, but I don't see what's wrong with it.
Does anyone see a problem with the 'FILTER' syntax in the measure below?
Relevant WO =
MAXX(
TOPN( 1, FILTER (
WO,
WO[I_DATE] <= MAX ( Calendar[Date] )
&& (WO[G_DATE] >= MIN ( Calendar[Date] ) || WO[G_DATE] = BLANK())
&& WO[I_DATE] <> BLANK ()
), WO[I_DATE], DESC),WO[WO_CODE])
Your help is very much appreciated!
EDIT, some extra info:
When I strip down the formula to:
Relevant WO =
FILTER (
WO,
WO[I_DATE] <= MAX ( Calendar[Date] )
&& (WO[G_DATE] >= MIN ( Calendar[Date] ) || WO[G_DATE] = BLANK())
&& WO[I_DATE] <> BLANK ()
)
PBI tells me there is no 'red' error, but that 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'
I'm lost.
8 Replies
HI,
Can you wrap table name after filter with all ?
filter (all(WO)......
Regards,
Venkata Nalla
That didn't fix it, unfortunately.
What I did find out is that when I strip down the measure to:
Relevant WO = FILTER ( WO, WO[I_DATE] <= MAX ( Calendar[Date] ) && (WO[G_DATE] >= MIN ( Calendar[Date] ) || WO[G_DATE] = BLANK()) && WO[I_DATE] <> BLANK () )... PBI throws another error which says 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value'. I have a feeling the problem wasn't so much in 'FILTER' but in 'MAXX' and 'TOPN', which makes the situation even weirder for me.
Anonymous , This how used top N in one example
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
Anonymous , Try like
Relevant WO = MAXX( TOPN( 1, FILTER ( WO, WO[I_DATE] <= MAX ( Calendar[Date] ) && (WO[G_DATE] >= MIN ( Calendar[Date] ) || isblank(WO[G_DATE]) ) && not(isblank(WO[I_DATE])) ), WO[I_DATE], DESC),WO[WO_CODE])
