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

[jimbob2285]

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 · Accepted solution]

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]

That worked a treat, thanks for that

Everyday's a school day

Cheers

Jim