Create column with count of entries by ID

[Anonymous]

Hi! I am relatively new to DAX, and I've been trying to replicate something like the group_by function in R. 

I wrote some sample data below, with the column "count" being what I need. In sum, I need to create a column (and not a table) that displays how many entries an identifier has in the sample EmployeeID column, filtered by hours > 0. Essentially I need to count the entries by ID. 

I tried solutions with GROUPBY that I've seen here but they output a table, and not a column. 

Employee ID	Hours	Count
0001	0	2
0001	7	2
0001	7	2
0002	4	1
0003	7	2
0003	7	2
0004	0	2
0004	2	2
0004	3	2

Thanks!

[Anonymous]

Thanks @amitchandak ! problem solved

[smpa01]

@Anonymous  you need a measure like this

Measure =
CALCULATE (
    COUNT ( 'Table'[Hours] ),
    ALLEXCEPT ( 'Table', 'Table'[Employee ID ] ),
    'Table'[Hours] <> BLANK ()
)

[Anonymous]

Hi @amitchandak , thank you for the quick reply! 

I tried that solution, but the output is inaccurate: the count doesn't correspond to the actual values. For instance, using the sample provided, I can see that even for IDs that only have 0 hours (which we filtered), the count is f.ex. 16, and even as the number of entries is f.ex. 4. Plus there are also some blanks. Do you know what could be wrong? 

Thanks again!

EmployeeID	Hours	Count
0001	0	16
0001	0	16
0001	0	16
0002	5	10
0002	3	10

[amitchandak]

@Anonymous ,  I tried same as new column, and did not 16 and 10 

Column = COUNTX(FILTER(Data, [EmployeeID] =EARLIER(Data[EmployeeID]) && [ Hours] >0 ), Data[EmployeeID])

[Anonymous]

Sorry, I didn't express myself correctly. I meant that I tried the solution in my original dataset (which is proprietary), and the output was something like that second table I inserted. 

[amitchandak · Accepted solution]

@Anonymous , a new column

countx(filter(Table, [Employee ID] =earlier([Employee ID]) && [Hours] >0 ) , [Employee ID])