cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

'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. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper V
Helper V

Re: 'FILTER' syntax is suddenly incorrect

I was using another PC that had different settings, which messed up the distinction between ',' and ';'. My bad!

View solution in original post

8 REPLIES 8
Highlighted
Resolver III
Resolver III

Re: 'FILTER' syntax is suddenly incorrect

HI,

Can you wrap table name after filter with all  ? 

 

filter (all(WO)......

 

Regards,

Venkata Nalla

Highlighted
Super User IX
Super User IX

Re: 'FILTER' syntax is suddenly incorrect

@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])


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper V
Helper V

Re: 'FILTER' syntax is suddenly incorrect

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.

 

Highlighted
Helper V
Helper V

Re: 'FILTER' syntax is suddenly incorrect

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'.

Highlighted
Super User IX
Super User IX

Re: 'FILTER' syntax is suddenly incorrect

@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]))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper V
Helper V

Re: 'FILTER' syntax is suddenly incorrect

Yeah, I know how TOPN works in a situation like that. 😉

Highlighted
Community Support
Community Support

Re: 'FILTER' syntax is suddenly incorrect

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.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper V
Helper V

Re: 'FILTER' syntax is suddenly incorrect

I was using another PC that had different settings, which messed up the distinction between ',' and ';'. My bad!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors