Forum Discussion

jimbob2285's avatar
jimbob2285
Icon for Advocate IV rankAdvocate IV
2 years ago
Solved

Calculate(Count(), Filter()) - A single value cannot be determined

Hi

 

I'm hoping that someone cleverer than me can help please, as this is something that I come up against time and time again, and I don't really understand why... I often get the error message "A single value for column 'Date' in table 'Calendar' cannot be determined" when trying to calculate ratios (percentages) as a Measure, rather than a Column, so that it can be aggregated, if that's the right word, I mean so that I can show the ratio by month, rather than just date

 

In this example, I've got:

  • A table of projects that we've quoted (with a quoted date)
  • A second table of projects that we've won (with a won date), With the following measure formula, that throws up the above error:
  • A calendar table, calculated from the min and max dates of the other two tables

 

In the Calendar table I'm trying to run the following measure, but keep getting the above eror: 

WinRate =
VAR ProjectsWon = CALCULATE(
    COUNT(Won[ID]),
    FILTER(Won, Won[Date] = 'Calendar'[Date])
)
VAR ProjectsQuoted = CALCULATE(
    COUNT(Quoted[ID]),
    FILTER(Quoted, Quoted[Date] = 'Calendar'[Date])
)
RETURN DIVIDE(SUM('Calendar'[ProjectsWon]), SUM('Calendar'[ProjectsQuoted]))
 
But I'm getting the error: "A single value for column 'Date' in table 'Calendar' cannot be determined"
 
I can do the counts as columns in the Calendar table and then calculate the WinRate as a measure, so why can't I do it all in a single measure, to negate the need for the helper columns?
 
There are only unique dates in the 'Calendar'[Date] field, so it seems as though the error message is wrong, but I must be missing something
 
Can anyone please advise what I'm doing wrong?
 
Thanks
Jim
  • jgeddes's avatar
    jgeddes
    2 years ago

    It is an issue of context. 
    As a measure, the formula you have written does not know what to do with the 'Calendar'[Date]'. That is why it suggests SUM, MAX etc.

    I have been able to solve this usually by using the SELECTEDVALUE() function. In your case it would look like;

    FILTER(Won, Won[Date] = SELECTEDVALUE('Calendar'[Date]))

    If you wrote your initial formula as a calculated column it would work as written as the row of the table would immediately provide the context for the 'Calendar'[Date] value.

    Hope this helps a bit.

2 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    2 years ago

    It is an issue of context. 
    As a measure, the formula you have written does not know what to do with the 'Calendar'[Date]'. That is why it suggests SUM, MAX etc.

    I have been able to solve this usually by using the SELECTEDVALUE() function. In your case it would look like;

    FILTER(Won, Won[Date] = SELECTEDVALUE('Calendar'[Date]))

    If you wrote your initial formula as a calculated column it would work as written as the row of the table would immediately provide the context for the 'Calendar'[Date] value.

    Hope this helps a bit.

    • jimbob2285's avatar
      jimbob2285
      Icon for Advocate IV rankAdvocate IV
      2 years ago

      That worked a treat, thanks for that

       

      Everyday's a school day

       

      Cheers

      Jim