Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter calculate table / summarize by aggregate function column

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?
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      FreemanZ
      Super User
      3 years ago

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

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    Hi Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I deleted the last ) but I get: 

      "cannot find table 'DoubledHCPSTable'" 

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        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.