Forum Discussion

gcp_kumar's avatar
gcp_kumar
Frequent Visitor
5 years ago
Solved

Column or Measure based on value from Child Table

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 educat...
Need Help
  • ibarrau's avatar
    ibarrau
    5 years ago

    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,

gcp_kumar's avatar
gcp_kumar
Frequent Visitor
5 years ago

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.