Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX YEAR Function

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

Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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