Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to resolve the error mentioned

Hi All,

 

When I'm creating the column using below logic  I'm getting the below error how we can get rid of it(i have tried to create measure also buit same error).

Logic:

Column = IF(WEEKNUM('Datedim'[entrydate].[Date] ) = WEEKNUM( TODAY() ),
"Current week",
IF(WEEKNUM('Datedim'[entrydate].[Date] ) = WEEKNUM( TODAY() ) - 1,
"Prior week"))

 

Error:

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

 

 

Thanks,

Anand

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Try removing both of the ".[Date]".  You are referencing the date table behind the scenes.  Removing should reference the value on that row.  You can uncheck Auto DateTime in the options to prevent that, as it is better practice to make your own Date table.

    Regards,

    Pat

  • Pragati11's avatar
    Pragati11
    5 years ago

    Hi Anonymous ,

     

    Try removing ".Date" attribute from your DAX as suggested by mahoneypat .

     

    Thanks,

    Pragati

7 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Try removing both of the ".[Date]".  You are referencing the date table behind the scenes.  Removing should reference the value on that row.  You can uncheck Auto DateTime in the options to prevent that, as it is better practice to make your own Date table.

    Regards,

    Pat

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    Hi Anonymous ,

     

    After reading your ERROR expression, it seems the DAX expression that you have shared is a measure.

    Try creating the same DAX using a calculated column. This error will go.

     

    If you need the above calculation as a Measure, use a summarised function on your date column like MAX, MIN, etc. For example:

     

    Column = IF(WEEKNUM(MAX('Datedim'[entrydate].[Date] )) = WEEKNUM( TODAY() ),
    "Current week",
    IF(WEEKNUM(MAX('Datedim'[entrydate].[Date]) ) = WEEKNUM( TODAY() ) - 1,
    "Prior week"))
     
    Thanks,
    Pragati

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Pragathi,

       

      I did the same thing but still error persist.

       

      Thanks,

      Anand

      • Pragati11's avatar
        Pragati11
        Super User
        5 years ago

        Hi Anonymous ,

         

        Try removing ".Date" attribute from your DAX as suggested by mahoneypat .

         

        Thanks,

        Pragati

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You have to create it as column not measure

    Week Type = Switch( True(),
    WEEKNUM('Datedim'[entrydate] ) = WEEKNUM( TODAY() ),"This Week" ,
    WEEKNUM('Datedim'[entrydate] ) = WEEKNUM( TODAY() )-1,"Last Week" ,
    [Week Name]
    )

     

    Also refer : https://youtu.be/hfn05preQYA

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    Hi,

     

    if your Datedim follows the Model then you might want to use this formula to add your column

     

    Column = IF('Calendar'[WeekNum] = MAX('Calendar'[WeekNum]), "Current Week", "NON Current Week")
     
    where 
    WeekNum = WEEKNUM('Calendar'[Date].[Date],1)