Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,
Happy to help!
@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.
@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())
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,
Happy to help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |