Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@zudar , 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])
I don't know, it's still throwing the same 'The syntax for 'FILTER' is incorrect'-error... 😞
Please refer to the reply on the other provided solution. I have a feeling the problem is in the 'MAXX' and/or 'TOPN'.
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.
@zudar , 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]))
Yeah, I know how TOPN works in a situation like that. 😉
Hi @zudar ,
For the function of FILTER, it returns a table that represents a subset of another table or expression. It can be used alone to create a calculated table. So you get the error message: 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value' in a measure.
But for the error: 'FILTER' syntax is incorrect, I'm not clear. I have checked your formula. And I think it should work. Please share more details, like the sample data, expected output and a few screenshots of that error. If you can share a dummy pbix file, it is more helpful for us to understand your situation.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |