Count of rows where criteria is met in related table

[pbisandie]

Hi, I'm looking to create a measure for the sample data set below. There is a one-to-many relationship on the Parent ID.

I want to count the number of parent IDs with the name Karen who have a female (F) child named Alex. The result I'm looking for woudl be 1. 

Any help is much appreciated. Thanks!

Parent Table

Parent ID	Parent Name
1	Bob
2	Karen
3	Jason
4	Karen

Parent-Child Table

Parent ID	Parent Name	Child Name	Child Sex
1	Bob	Amy	F
1	Bob	Kelsie	F
2	Karen	Randy	M
2	Karen	Timmy	M
2	Karen	Alex	M
3	Jason	Aaron	M
4	Karen	Alex	F
4	Karen	Dolly	F

[Bibiano_Geraldo · Accepted solution]

Hi, @pbisandie!

Please try the following DAX to create your measure:

CountKarenWithFemaleAlex = 
CALCULATE(
    DISTINCTCOUNT('Parent Table'[Parent ID]),
    'Parent Table'[Parent Name] = "Karen",
    FILTER(
        'Parent-Child Table',
        'Parent-Child Table'[Child Name] = "Alex" &&
        'Parent-Child Table'[Child Sex] = "F"
    )
)

Make sure to replace column and tables names with your owns.

If this answer satisfies your needs, please consider to mark it as solution and give a Kudo.

Thank you