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.
Hello,
I have a Table that contains information about Parent and Child records together (ID, Parent_ID, Type, Gender). I need to add 2 columns that calculate the number of children of each gender for each parent (Boy, Girl) but cannot figure out how to do it.
ID | Parent_ID | Type | Gender | Boy | Girl |
1 | 1 | Parent | 3 | 1 | |
2 | 2 | Parent | 2 | 2 | |
3 | 1 | Child | Boy | ||
4 | 1 | Child | Boy | ||
5 | 1 | Child | Boy | ||
6 | 1 | Child | Girl | ||
7 | 2 | Child | Boy | ||
8 | 2 | Child | Boy | ||
9 | 2 | Child | Girl | ||
10 | 2 | Child | Girl |
Thank you,
Hugues.
Solved! Go to Solution.
Got it @huguest
Here you go
Boy = IF ( Table1[Type] = "Parent", CALCULATE ( COUNT ( Table1[Gender] ), Table1[Gender] = "Boy", ALLEXCEPT ( Table1, Table1[Parent_ID] ) ) ) Girl = IF ( Table1[Type] = "Parent", CALCULATE ( COUNT ( Table1[Gender] ), Table1[Gender] = "Girl", ALLEXCEPT ( Table1, Table1[Parent_ID] ) )
)
)
Do you expect to get the results in the same table in a calculated column, or it's ok to use a measure and see your results like shown below?
Parent_ID | Boy | Girl |
1 | 3 | 1 |
2 | 2 | 2 |
N-
Ideally as a calculated column in the same table.
Got it @huguest
Here you go
Boy = IF ( Table1[Type] = "Parent", CALCULATE ( COUNT ( Table1[Gender] ), Table1[Gender] = "Boy", ALLEXCEPT ( Table1, Table1[Parent_ID] ) ) ) Girl = IF ( Table1[Type] = "Parent", CALCULATE ( COUNT ( Table1[Gender] ), Table1[Gender] = "Girl", ALLEXCEPT ( Table1, Table1[Parent_ID] ) )
)
)
Thank you, I will give it a try as soon as I get a chance.
Hi, try with this calculated column
Boy = IF ( Table3[Type] = "Parent", COUNTROWS ( FILTER ( Table3, Table3[Parent_ID] = EARLIER ( Table3[Parent_ID] ) && Table3[Gender] = "Boy" ) ) )
Regards
Victor
Lima - Peru
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |