A DAX question

[Anonymous]

Dear all, I have a DAX question - 

I have a table that has relations between projects and employees - 

Project	Employee
1	1
2	1
2	2
2	3
3	1
3	2

Now I am looking for a project count for each employee, for only projects with multiple employees assigned (exclude single person project) - How should go about with the DAX?

Eomployee	Counts of Multi People Project
1	2
2	2
3	0

Thanks!
NM

[v-xiaotang · Accepted solution]

Hi @Anonymous 

You can try this, create the measures below,

EpCountPerProject = CALCULATE(DISTINCTCOUNT('Table'[Employee]),ALLEXCEPT('Table','Table'[Project]))

Measure = CALCULATE(DISTINCTCOUNT('Table'[Project]),FILTER(ALL('Table'),'Table'[Employee]=MIN('Table'[Employee])&& [EpCountPerProject]>1))

for Employee=3,  Counts of Multi People Project=1,

result

In this scenario, you don't need to go seek better data model. 

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@Anonymous , Try a measure like

Measure =
var _tab = filter(summarize(Table, Table[Project], "_1", count(Table[Employee])), [_1]>0)
var _tab1 = summarize(_tab, [Project])
return
countrows(Table, Table[Project] in _tab1)

[Anonymous]

Thank you @amitchandak !

If the output table is the # of multi people projects per employee, with employee on each row from the same table, does these summarize used in measure aleady get the filter context of the employee on the row? Or summarize has access to the entire table, not filtered?

also does this part miss a calculate? - countrows(Table, Table[Project] in _tab1)

My other question is, if the DAX is this complex to get to such KPI, should I go seek better data model, such as having a separated Project table, and project-employee table, where project has the granularity at project level should help a lot in this case?

Thanks!
NM