Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am struggling with a calculation in DAX. I want to add the column "Children" to my dataset (I have one fact_Table, as displayed below). For example, ID 2 shows up twice under Parent and therefore has 2 children. So the number of times the ID value pops up in other rows is the amount of Children records.
I used COUNTROWS and LOOKUPVALUE to no avail, I am hoping anyone here knows how to calculate this column.
Fact_Table
ID | Type | Parent | Status | Children |
1 | A | 2 | D | 1 |
2 | B | 6 | I | 2 |
3 | C | 2 | I | 1 |
4 | D | 1 | D | 1 |
5 | C | 3 | T | 0 |
Thank you in advance,
Best,
Gijs
Solved! Go to Solution.
[Children] = // calculated column
var CurrentID = FT[ID]
var Result =
COUNTROWS(
FILTER(
FT,
FT[Parent] = CurrentID
)
)
RETURN
Result
[Children] = // calculated column
var CurrentID = FT[ID]
var Result =
COUNTROWS(
FILTER(
FT,
FT[Parent] = CurrentID
)
)
RETURN
Result