'FILTER' syntax is suddenly incorrect

[zudar]

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. 

[zudar · Accepted solution]

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

[amitchandak]

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

[zudar]

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

[nvprasad]

HI,

Can you wrap table name after filter with all  ? 

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

Regards,

Venkata Nalla

[zudar]

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.

[amitchandak]

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

[zudar]

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

[v-xuding-msft]

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.