Filter calculate table / summarize by aggregate function column

[Anonymous]

Hello,

I created a table using CALCULATETABLE and SUMMARIZE, trying to count the number of people who answered my question more then once, but my code is not working:

Test Doubled hcps =

var DoubledHCPSTable = CALCULATETABLE(

SUMMARIZE(

'Calls Analsis',

'Calls Analsis'[sm_id],

'Calls Analsis'[QuestionID],

"Total Answers",COUNTA('Calls Analsis'[Answer])

)

,FILTER('Calls Analsis','Calls Analsis'[QuestionID]="i204")

)

RETURN CALCULATE(

COUNTROWS(DoubledHCPSTable),DoubledHCPSTable[Total Answers]>1)

)

How can I filter by the aggregate colum?

[Anonymous · Accepted solution]

Here is the answer: 

Test Doubled hcps = COUNTROWS(

FILTER(CALCULATETABLE(SUMMARIZE(

'Calls Analsis',

'Calls Analsis'[sm_id],

'Calls Analsis'[QuestionID],

"Total Answers",COUNTA('Calls Analsis'[Answer] )

)),'Calls Analsis'[QuestionID]="i204" && [Total Answers]>1 ))

[FreemanZ]

struggled and figured it out by oneslef would be the best. 

[FreemanZ]

Hi @Anonymous 

the last ) seems unnecessary. What warning do you get?

[Anonymous]

I deleted the last ) but I get: 

"cannot find table 'DoubledHCPSTable'" 

[FreemanZ]

try to delete the DoubledHCPSTable:

Test Doubled hcps =

var DoubledHCPSTable = CALCULATETABLE(

SUMMARIZE(

'Calls Analsis',

'Calls Analsis'[sm_id],

'Calls Analsis'[QuestionID],

"Total Answers",COUNTA('Calls Analsis'[Answer])

)

,FILTER('Calls Analsis','Calls Analsis'[QuestionID]="i204")

)

RETURN CALCULATE(

COUNTROWS(DoubledHCPSTable),[Total Answers]>1)

p.s.: no need to specify the table name for virtually added column.

[Anonymous]

This also not working for me:

Test Doubled hcps =

var DoubledHCPSTable = CALCULATETABLE(

SUMMARIZE(

'Calls Analsis',

'Calls Analsis'[sm_id],

'Calls Analsis'[QuestionID],

"Total Answers",COUNTA('Calls Analsis'[Answer])

)

,FILTER('Calls Analsis','Calls Analsis'[QuestionID]="i204" && [Total Answers]>1)

)

RETURN CALCULATE(

COUNTROWS(DoubledHCPSTable))

[Anonymous]

Not working: