Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
NanPowerBI
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @NanPowerBI 

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

vxiaotang_0-1652765656159.png

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.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @NanPowerBI 

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

vxiaotang_0-1652765656159.png

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
Super User
Super User

@NanPowerBI , 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)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

 

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.