Forum Discussion
Helper Ireturned value with related tables
Hi,
I have the two following tables (household and member) linked by an ID. In table household, how can I get the returned value (# people under 5) for each household? What would be the DAX FUNCTION to get this value in ‘’# people under 5’’ column? For example, for the first household with id =01, the returned number of people under 5 years old would be 2. For the second household with Id=02, the number of people under 5 would be 1
Household
Household ID | District | Sex of household head | # people under 5 yrs old |
01 | A | Male | ? |
02 | B | Female | ? |
Member
Household ID | Name | Sex | Age |
01 | A | Male | 20 |
01 | B | Female | 2 |
01 | C | Male | 1 |
02 | D | Female | 3 |
02 | E | Female | 15 |
Hi naeljb
Assuming you have a relationship setup between Household and Members, please try the followingn Calcualated Column on Household
# People under 5 yrs old = CALCULATE(
COUNTROWS('Member'),
FILTER('Member','Member'[Age]<5),
RELATEDTABLE('Member')
)or a slightly shorter version of Calculated column :
=CALCULATE( COUNTROWS('Member'), 'Member'[Age] < 5 )Or you can do a measure which in this case is same code (measure would be my preference):
Kids under 5:=CALCULATE( COUNTROWS('Member'), 'Member'[Age] < 5 )Phil_Seamark one thing i don't understand in your version is the 'RELATEDTABLE('Member')' part - what is the thought behind including?
Not sure I follow. If you enter in Phil_Seamark code as a calculated column as shown in 'Household' it will return for the respective household how many members are under 5....what is the output supposed to look like?
8 Replies
- Phil_Seamark
Microsoft Employee
Hi naeljb
Assuming you have a relationship setup between Household and Members, please try the followingn Calcualated Column on Household
# People under 5 yrs old = CALCULATE(
COUNTROWS('Member'),
FILTER('Member','Member'[Age]<5),
RELATEDTABLE('Member')
)
mattbriceSolution Sage
or a slightly shorter version of Calculated column :
=CALCULATE( COUNTROWS('Member'), 'Member'[Age] < 5 )Or you can do a measure which in this case is same code (measure would be my preference):
Kids under 5:=CALCULATE( COUNTROWS('Member'), 'Member'[Age] < 5 )Phil_Seamark one thing i don't understand in your version is the 'RELATEDTABLE('Member')' part - what is the thought behind including?
- Phil_Seamark
Hi mattbrice, more habit than anything and you are right it's not needed in this particular case, There is some useful info in the Definity Guide to DAX about using RELATEDTABLE on the one side of a one to many relationships.
- naeljb
Thank sooo much Phil. It works.
- naeljb
Thank you very much Phils. It does tell me the total number of people under 5. But, it doesn't return in table househod and for each household the number of people under 5. (i.e, I would like to get in the number of people under 5 for each of househod in table household).
- mattbrice
Not sure I follow. If you enter in Phil_Seamark code as a calculated column as shown in 'Household' it will return for the respective household how many members are under 5....what is the output supposed to look like?
- naeljb
Indeed !!! was my mistake . Thanks all for your help!!!