Column or Measure based on value from Child Table

[gcp_kumar]

Hello Everyone!

I have two tables linked by Parent-Child Relationship. To put it in a more practical way, the Parent table contains information about a person, and the child table contains education information about his/her children. A person can have one or more children with different educational backgrounds. I want to add a column or measure(whichever is more appropriate) in the parent table whose value will be true if any of the children is a 'Doctor'. The table structure is something like this:

Regards,

Abishek

[gcp_kumar]

@ibarrau , @amitchandak  Thank you for your quick response. I think I should inform you that I am using Direct Query mode to import data from the Azure SQL database.

I tried both queries but it is throwing "COUNTX not recognized" error. I am trying to add a custom column in the Transform data window.

My aim here is to find parents having at least one child who is a doctor. Once I have this information, I can visualize different attributes for these parents.

Edited: Thank you @amitchandak @ibarrau , your solution worked! I tried creating measure and I am able to achieve the result I wanted.

[amitchandak]

@gcp_kumar , Try a new column in parent table

if(isblank(countx(filter(children, children[parent_id] = parent[id] && children[educational background] ="Doctor"),[ID]_)),false(), true())

[ibarrau · Accepted solution]

Hi, the solution might be different regarding on how you want to show this data. First of all the tables must be related to make thie following DAX work. I think the easiest to understand the model would be a new column in Parent table like this:

ColumnDoctor =
COUNTX(
    FILTER(
        RELATEDTABLE(Children), Children[EducationalBackgroud] = "Doctor"
    ), 
    Children[ID]
)

 That will count the doctors in children for each row in parent. Then you can filter by > 0 to get the rows or add an IF like

ColumnDoctor=
VAR previousCalulation = COUNTX(...
RETURN
IF ( previousCalculation > 0, TRUE(), FALSE() )

You can also solve this with a measure, but I would prefer to know a bit more about the data visualization for this to understand the context before suggesting it.

Hope this helps,