Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

'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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago
    I was using another PC that had different settings, which messed up the distinction between ',' and ';'. My bad!
nvprasad's avatar
nvprasad
Icon for Solution Sage rankSolution Sage
6 years ago

HI,

Can you wrap table name after filter with all  ? 

 

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

 

Regards,

Venkata Nalla

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Yeah, I know how TOPN works in a situation like that. ğŸ˜‰

      • v-xuding-msft's avatar
        v-xuding-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        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.