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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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