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 D...
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 ))
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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Not working:

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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