Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

A DAX question

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 lookin...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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