Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, it sounds like such a simple problem, but I am stuck with it and tried several solutions offered on the forum with no luck.
I have following DevOps data of related parent and children in one table (yeah not the best, but decided to leave it in one table since data is not heavy). It is one to many relaitonships between 1 source to many targets.
I need a measure that will give me how much state Value (closed) is per each Source Item (i.e. for source #1 Closed = 1, for source #2 - o, per #3 - 2.
At the end, I need it to calculate the % of closed children under each parent, which I can do (using countr of children)once figure out the measure per state.
Your help is Very much appreciated
SourceWorkItemID | State | TargetWorkItemID | Count of Children |
1 | New | 11 | 3 |
1 | Closed | 12 | 3 |
1 | Active | 13 | 3 |
2 | New | 21 | 2 |
2 | New | 22 | 2 |
3 | Closed | 33 | 2 |
3 | Closed | 32 | 2 |
Solved! Go to Solution.
@uniqum47
I made a measure to calculate the %, it will work at the total level as well.
% closed Children =
var __Closed =
SUMX(
VALUES(Table2[SourceWorkItemID]),
CALCULATE(
COUNT(Table2[State]),
Table2[State] = "Closed"
)
)
var __Count =
SUMX(
VALUES(Table2[SourceWorkItemID]),
CALCULATE( COUNT(Table2[State]) )
)
var __result =
DIVIDE( __Closed , __Count )
return
__result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@uniqum47
I made a measure to calculate the %, it will work at the total level as well.
% closed Children =
var __Closed =
SUMX(
VALUES(Table2[SourceWorkItemID]),
CALCULATE(
COUNT(Table2[State]),
Table2[State] = "Closed"
)
)
var __Count =
SUMX(
VALUES(Table2[SourceWorkItemID]),
CALCULATE( COUNT(Table2[State]) )
)
var __result =
DIVIDE( __Closed , __Count )
return
__result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
thank you - that helped a lot.
Can I stretch my luck and ask for further help - the requirement has changed -if items are Closed, they are not necessarily released to prod. I have another column with release date mixed with null, I transfered it to 1 - not released, 2 released.
I tried to adjust your query, but it doesnt adjust to count all children with 1 and 2. I need it to group, then count how many 2 are in the group anf give me % of 2 per group (i.e. group 2 is 50%).
I feel like I am missing something basic 😞
Many many thanks!
SourceWorkItemID | State | TargetWorkItemID | Count of Children | release |
1 | New | 11 | 3 | 1 |
1 | Closed | 12 | 3 | 2 |
1 | Active | 13 | 3 | 2 |
2 | New | 21 | 2 | 2 |
2 | New | 22 | 2 | 1 |
3 | Closed | 33 | 2 | 1 |
3 | Closed | 32 | 2 | 2 |
User | Count |
---|---|
55 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
83 | |
54 | |
39 | |
21 | |
15 |