(explanatory screenshot included) Writing DAX code for number of values in column per specific value

[Anonymous]

Hello,

I would like to create an additional column called "Index" that shows the number of surveys per employee. For example, in this screenshot, it looks like employee S-249 has four corresponding Surveys in the "Survey #" Column. What code would I write for an additional column that would show the number of surveys per employee?

Thanks, Kendall

[Anonymous]

great, that worked out. thanks, kendall

[Anonymous]

Thanks serpiva. Forgive me as this is a basic question: where would I find the Group By function in the query editor? Thanks. 

[serpiva64]

Hi,

here it is

[serpiva64]

Hi,

you have to Group By; something like this

then you expand your nested table and in the column Count you have your result (if you need to count for a period of time you have to add another Grouping

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[Anonymous]

Hi Serpiva, so I am doing this, but when I put this out my Index column keeps coming out as "1". Why is it unable to count the number of surveys per employee? Thanks, Kendall

[serpiva64 · Accepted solution]

Hi,

you have to group only for Emplyee and then the count is going to be on surveys

[Anonymous]

When I do that, it comes out with only two columns: 

Is there anyway I can do this by adding a custom column to this existing Query using the Table.AddColumn function?

[serpiva64]

Hi,

that's because you have to add All Rows as a new column.

Then you expanded it and you have all of your rows with the total for employee

See it in the attached example