Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Count by Group by

Hi, I need some help with a calculated column.

 

Following are the details:

client_idsurvey_idcomment_idcomment_textCount of Comments
12323478234835kjsysdihskf3
123234782357545fghfdhh3
123234782534535gfhfghdf3
1235788449594xvxvsd1
667438534434875xvcvxv2
6674385344367685fhdssh2
90565625326698734xvsgsg1

 

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

Need Help

3 Replies

  • FrankAT's avatar
    FrankAT
    Community Champion
    6 years ago

    Hi Anonymous ,

    hope it helps, see figure:

     

     

    Regards FrankAT

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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