Count by Group by

[Anonymous]

Hi, I need some help with a calculated column.

Following are the details:

client_id	survey_id	comment_id	comment_text	Count of Comments
123	234782	34835	kjsysdihskf	3
123	234782	357545	fghfdhh	3
123	234782	534535	gfhfghdf	3
123	57884	49594	xvxvsd	1
667	438534	434875	xvcvxv	2
667	438534	4367685	fhdssh	2
9056	562532	6698734	xvsgsg	1

My goal is to calculate the count of comments for each survey. I could achieve this using the below calc column on my sample data. However, my real data comes from direct query and I am not able to use CALCULATE as it is not allowed with direct query. Is there a way or different approach to get this done ? 

Count of Comments = CALCULATE(COUNT(Table1[comment_id]), FILTER(Table1, Table1[survey_id] = EARLIER(Table1[survey_id])))

Appreciate your prompt help.

-SN

[v-shex-msft]

Hi @Anonymous,

AFAIK, most of calculation DAX functions has been limited to use in direct query mode. You can use measure formula instead.

Measure =
CALCULATE (
    COUNTROWS ( VALUES ( 'Table'[comment_id] ) ),
    ALLSELECTED ( 'Table' ),
    VALUES ( 'Table'[client_id] ),
    VALUES ( 'Table'[survey_id] )
)

Regards,

Xiaoxin Sheng

[FrankAT]

Hi @Anonymous ,

hope it helps, see figure:

Regards FrankAT

[Anonymous]

@FrankAT  Thanks for reply. The count column in my sample data is the result of the calculated column. I want that to be replicated for a direct query.