Count Function

[davidv85]

Hi All

im after some help, i have a table which lists out all current employees and also lists their line reports all data is within the same table, im trying to count for each resource how many line reports they have - see below what im trying to achieve (in red)

the first column are all unique whereas they could appear multiple times in the second column throughout the table, i have tried various count functions and cant get it to work, relatively new to power BI, any help is much appreciated

Current emp	Line report	No of line reports
Resource 1	Resource2	0
Resource2	Resource2	3
Resource3	Resource2	0

[JonClemo]

You could achieve the count by using 'group by' on the line report column. This would however effectively remove the other column - so you would be left with Resource and No. of line reports. I guess if you duplicated the table you could then join the grouped table to the original using the resource as the key - this would give you your desired layout . Someone else may have a more elegant solution. 

[mahoneypat · Accepted solution]

You can try this expression in a measure, replacing Table with your actual table name.

No. of Line Reports =
VAR thisresource =
MIN ( Table[Current emp] )
RETURN
CALCULATE (
COUNTROWS ( Table ),
ALL ( Table ),
Table[Line Report] = thisresource
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[davidv85]

Worked perfectly Thank you very much!