cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gvg Member
Member

Can't figure out what is wrong with the filter

Hi,

 

I have this measure and can't really figure out, why is it not working. I get error "A table of multiple values was supplied were a single value was expected". 

 

Sum Sales  =
    CALCULATE (
        SUM ( Sales[Amount] ),
        FILTER (
            ALL ( 'Date' ),
            DATESBETWEEN ( 'Date'[Date], DATE (2017,1,1), DATE(2017,9,30) )
        )
    )
4 REPLIES 4
Super User
Super User

Re: Can't figure out what is wrong with the filter

Try this

 

Sum Sales  =
    CALCULATE (
        SUM ( Sales[Amount] ),    
            DATESBETWEEN ( 'Date'[Date], DATE (2017,1,1), DATE(2017,9,30) 
    )


* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
gvg Member
Member

Re: Can't figure out what is wrong with the filter

OK. But how come this works and FILTER doesn't? Doesn't your CALCULATE translates internally to my CALCULATE with FILTER ?

Super User
Super User

Re: Can't figure out what is wrong with the filter

 


@gvg wrote:

OK. But how come this works and FILTER doesn't? Doesn't your CALCULATE translates internally to my CALCULATE with FILTER ?


CALCULATE has 2 syntaxes

Simple syntax - this is what you are describing where the simple syntax is converted to the full syntax you mentioned.

Advanced (or table) syntax - this is what my formula does - it takes a table as a filter to CALCULATE.  DATESBETWEEN returns a table of dates. Whenever you get a table, you must use it directly in CALCULATE (advanced syntax). 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
gvg Member
Member

Re: Can't figure out what is wrong with the filter

Advanced (or table) syntax 


 

OK. But doesn't FILTER return a table as well?