The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
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