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 - 

ProjectEmployee
11
21
22
23
31
32

 

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?

 

EomployeeCounts of Multi People Project
12
22
30

 

Thanks!
NM

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.

3 Replies

  • 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

       

       

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    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.