Count how many values exist in one column in relation to another

[eyysee]

Hey all!

I've been struggling to create the right query for a new column to calculate how many values exist in one column in relation to another. I'll try my best to explain the problem I'm having.

Person	Manager	Number of Managees
John	David	2
Jeff	Bill	2
Jules	Sam	1
Jake	David	2
James	Bill	2

The "Person" column is uniqie and all names are different and each Person has a manager. A Manager may have more than one person to manage and I'd like to display this in the "Number of Managees" column so I can then filter by each unique manage and count how many managee's they have each.

Can you help me curate the DAX formula in the "Number of Managees" colum that would give me the desired result?

I'm open to suggesstions on how this may be better represented... a new table with a relationship maybe?

[Ashish_Mathur]

Hi,

This calculated column works

Number of managers = calculate(countrows(Data),filter(Data,Data[Manager]=earlier(Data[Manager])))

Hope this helps.

[v-zhangti · Accepted solution]

Hi, @eyysee 

You can try the following methods.

Column:

Count = 
CALCULATE ( COUNT ( 'Table'[Manager] ),
    ALLEXCEPT ( 'Table', 'Table'[Manager] )
)

Measure = CALCULATE ( COUNT ( 'Table'[Manager] ),
    ALLEXCEPT ( 'Table', 'Table'[Manager] )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[piotr_gor]

I've used your sample data. It just simply COUNTROWS. 
Filter context will do job for you.