topic Count the number of related records in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-the-number-of-related-records/m-p/1793168#M37786 <P>Hi,&nbsp;</P><P>&nbsp;</P><P>I am struggling with a calculation in DAX. I want to add the column "Children" to my dataset (I have one fact_Table, as displayed below). For example, ID 2 shows up twice under Parent and therefore has 2 children. So the number of times the ID value pops up in other rows is the amount of Children records.&nbsp;</P><P>&nbsp;</P><P>I used COUNTROWS and LOOKUPVALUE to no avail, I am hoping anyone here knows how to calculate this column.&nbsp;</P><P>&nbsp;</P><P>Fact_Table</P><TABLE><TBODY><TR><TD><P><STRONG>ID</STRONG></P></TD><TD><P>Type</P></TD><TD><P><STRONG>Parent</STRONG></P></TD><TD><P>Status</P></TD><TD><P><FONT color="#FF0000">Children</FONT></P></TD></TR><TR><TD><P>1</P></TD><TD><P>A</P></TD><TD><P><STRONG>2</STRONG></P></TD><TD><P>D</P></TD><TD><P><FONT color="#FF0000">1</FONT></P></TD></TR><TR><TD><P><STRONG>2</STRONG></P></TD><TD><P>B</P></TD><TD><P>6</P></TD><TD><P>I</P></TD><TD><P><FONT color="#FF0000">2</FONT></P></TD></TR><TR><TD><P>3</P></TD><TD><P>C</P></TD><TD><P><STRONG>2</STRONG></P></TD><TD><P>I</P></TD><TD><P><FONT color="#FF0000">1</FONT></P></TD></TR><TR><TD><P>4</P></TD><TD><P>D</P></TD><TD><P>1</P></TD><TD><P>D</P></TD><TD><P><FONT color="#FF0000">1</FONT></P></TD></TR><TR><TD><P>5</P></TD><TD><P>C</P></TD><TD><P>3</P></TD><TD><P>T</P></TD><TD><P><FONT color="#FF0000">0</FONT></P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Thank you in advance,&nbsp;<BR />Best,</P><P>Gijs&nbsp;</P> Mon, 19 Apr 2021 15:51:52 GMT Gijs93 2021-04-19T15:51:52Z Count the number of related records https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-the-number-of-related-records/m-p/1793168#M37786 <P>Hi,&nbsp;</P><P>&nbsp;</P><P>I am struggling with a calculation in DAX. I want to add the column "Children" to my dataset (I have one fact_Table, as displayed below). For example, ID 2 shows up twice under Parent and therefore has 2 children. So the number of times the ID value pops up in other rows is the amount of Children records.&nbsp;</P><P>&nbsp;</P><P>I used COUNTROWS and LOOKUPVALUE to no avail, I am hoping anyone here knows how to calculate this column.&nbsp;</P><P>&nbsp;</P><P>Fact_Table</P><TABLE><TBODY><TR><TD><P><STRONG>ID</STRONG></P></TD><TD><P>Type</P></TD><TD><P><STRONG>Parent</STRONG></P></TD><TD><P>Status</P></TD><TD><P><FONT color="#FF0000">Children</FONT></P></TD></TR><TR><TD><P>1</P></TD><TD><P>A</P></TD><TD><P><STRONG>2</STRONG></P></TD><TD><P>D</P></TD><TD><P><FONT color="#FF0000">1</FONT></P></TD></TR><TR><TD><P><STRONG>2</STRONG></P></TD><TD><P>B</P></TD><TD><P>6</P></TD><TD><P>I</P></TD><TD><P><FONT color="#FF0000">2</FONT></P></TD></TR><TR><TD><P>3</P></TD><TD><P>C</P></TD><TD><P><STRONG>2</STRONG></P></TD><TD><P>I</P></TD><TD><P><FONT color="#FF0000">1</FONT></P></TD></TR><TR><TD><P>4</P></TD><TD><P>D</P></TD><TD><P>1</P></TD><TD><P>D</P></TD><TD><P><FONT color="#FF0000">1</FONT></P></TD></TR><TR><TD><P>5</P></TD><TD><P>C</P></TD><TD><P>3</P></TD><TD><P>T</P></TD><TD><P><FONT color="#FF0000">0</FONT></P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Thank you in advance,&nbsp;<BR />Best,</P><P>Gijs&nbsp;</P> Mon, 19 Apr 2021 15:51:52 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-the-number-of-related-records/m-p/1793168#M37786 Gijs93 2021-04-19T15:51:52Z Re: Count the number of related records https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-the-number-of-related-records/m-p/1793221#M37788 <LI-CODE lang="csharp">[Children] = // calculated column var CurrentID = FT[ID] var Result = COUNTROWS( FILTER( FT, FT[Parent] = CurrentID ) ) RETURN Result</LI-CODE> Mon, 19 Apr 2021 16:20:32 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-the-number-of-related-records/m-p/1793221#M37788 Anonymous 2021-04-19T16:20:32Z