Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
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
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.
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
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.
@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)
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
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |