DAX YEAR Function

[Anonymous]

hi,

can anyone help me to explain what this error message means & how to resolve it -

'A single value for column 'Date' in table 'msjd-cldr' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

it's when i'm actually trying to get the year from 'Date' field in a date table using the DAX function - YEAR. It works in another instance in the same file though.

tks & krgds, -nik

[amitchandak]

If in a measure filter you try to use columns from two tables. You need to use some function to give one value.

First one will not work

filter(Date,Date[Date]<Sales[Sales])
filter(Date,Date[Date]<max(Sales[Sales]))

[Greg_Deckler]

Would need to see sample data and formul to be certain but this usually happens when you refere to a column in a measure without specifying an aggregation like MAX, MIN, SUM, etc. or you are using somthing like FILTER that returns a table of values. Very difficult to know without the formula.

[Anonymous]

hi @Greg_Deckler  & @amitchandak ,

the dax formula is 

YR = YEAR('cldr'[Date])

where i'm trying to get the year from [Date] in date table 'cldr'.

actually, i'm doing so as part of a test for the following formula:

        Monthly.MFRR =
        SWITCH (
        TRUE (),
        YEAR('cldr'[Date]) = 2017, DIVIDE ( [NLQA.EM], [AAE16] ),
        YEAR('cldr'[Date]) = 2018, DIVIDE ( [NLQA.EM], [AAE17] )
        )

may b i havent'r specified the aggregation to a column in a measure as u mentioned.
  
krgds, -nik 

[Greg_Deckler · Accepted solution]

Right, you would need something like:

        Monthly.MFRR =
        SWITCH (
        TRUE (),
        YEAR(MAX('cldr'[Date])) = 2017, DIVIDE ( [NLQA.EM], [AAE16] ),
        YEAR(MAX('cldr'[Date])) = 2018, DIVIDE ( [NLQA.EM], [AAE17] )
        )

If NLQA.EM and AAE16 and AAE17 are colums, you will need an aggregation around them as well.

[Anonymous]

much apprec again, @Greg_Deckler.

[Greg_Deckler]

Sure thing @Anonymous!