Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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?
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

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

FreemanZ
Super User
Super User

Hi @Anonymous 

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

Anonymous
Not applicable

I deleted the last ) but I get: 

"cannot find table 'DoubledHCPSTable'" 

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
Not applicable

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 applicable

Not working:

haim89_0-1671284911904.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.