returned value with related tables

[naeljb]

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

[Phil_Seamark · Accepted solution]

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')
 )

[mattbrice · Accepted solution]

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?

[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 · Accepted solution]

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!!!

[Phil_Seamark · Accepted solution]

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')
 )

[naeljb]

Thank sooo much Phil.  It works. 

[mattbrice · Accepted solution]

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.

[mattbrice]

ok - well my only comment on that is to not use it useless it is needed.  I understand what it does, but i just wasn't clear on why it was included.  Thanks for responding.