Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |