topic Re: Calculat of distant related values in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048944#M104932 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="506141" data-lia-user-login="mami" class="lia-mention lia-mention-user">mami</a>&nbsp;<BR />However, I have to say that School_Manage table is better to be part of the School table. There is absolutely&nbsp;no point of having two tables while adding few columns will do exactly the same job even more efficiently.</P> Sun, 29 Jan 2023 14:09:01 GMT tamerj1 2023-01-29T14:09:01Z Calculat of distant related values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048724#M104918 <P>I would like to calculat of distant related values, can someone please tell me how to do it?<BR />This is my scenario and the table</P><P>&nbsp;</P><P>3 main table</P><P><STRONG>School</STRONG></P><TABLE><TBODY><TR><TD>School_cd</TD><TD>School_name</TD></TR><TR><TD>100</TD><TD>School0</TD></TR><TR><TD>101</TD><TD>School1</TD></TR><TR><TD>102</TD><TD>School2</TD></TR><TR><TD>103</TD><TD>School3</TD></TR></TBODY></TABLE><P>&nbsp;</P><P><STRONG>Student</STRONG></P><P>HW: If homework is done 1, if not 0</P><TABLE><TBODY><TR><TD>student_cd</TD><TD>student_name</TD><TD><P>HW</P></TD></TR><TR><TD>300</TD><TD>student_0</TD><TD>1</TD></TR><TR><TD>301</TD><TD>student_1</TD><TD>1</TD></TR><TR><TD>302</TD><TD>student_2</TD><TD>1</TD></TR><TR><TD>303</TD><TD>student_3</TD><TD>0</TD></TR><TR><TD>304</TD><TD>student_4</TD><TD>0</TD></TR><TR><TD>305</TD><TD>student_5</TD><TD>1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P><STRONG>manager</STRONG></P><TABLE><TBODY><TR><TD>manager_cd</TD><TD>manager_name</TD></TR><TR><TD>200</TD><TD>ｍanager0</TD></TR><TR><TD>201</TD><TD>ｍanager1</TD></TR></TBODY></TABLE><P><BR />In addition to the above, there are two other tables.</P><P><STRONG>School_manager</STRONG></P><TABLE><TBODY><TR><TD>school_cd</TD><TD>Area</TD><TD>manager_cd</TD></TR><TR><TD>100</TD><TD>A</TD><TD>200</TD></TR><TR><TD>101</TD><TD>A</TD><TD>200</TD></TR><TR><TD>102</TD><TD>B</TD><TD>200</TD></TR><TR><TD>103</TD><TD>B</TD><TD>201</TD></TR></TBODY></TABLE><P><BR /><STRONG>School_Student</STRONG></P><P>The student belongs to only one main school, and the main school is flagged 1 for "main_school_flag".</P><TABLE><TBODY><TR><TD>school_cd</TD><TD>student_cd</TD><TD>main_school_flag</TD></TR><TR><TD>100</TD><TD><FONT color="#FF00FF">300</FONT></TD><TD><FONT color="#FF00FF">1</FONT></TD></TR><TR><TD>100</TD><TD><FONT color="#FF00FF">301</FONT></TD><TD><FONT color="#FF00FF">1</FONT></TD></TR><TR><TD>101</TD><TD>300</TD><TD>0</TD></TR><TR><TD>101</TD><TD><FONT color="#FF00FF">302</FONT></TD><TD><FONT color="#FF00FF">1</FONT></TD></TR><TR><TD>102</TD><TD>300</TD><TD>0</TD></TR><TR><TD>102</TD><TD><FONT color="#FF00FF">303</FONT></TD><TD><FONT color="#FF00FF">1</FONT></TD></TR><TR><TD>102</TD><TD><FONT color="#FF00FF">304</FONT></TD><TD><FONT color="#FF00FF">1</FONT></TD></TR><TR><TD>103</TD><TD><FONT color="#FF00FF">305</FONT></TD><TD><FONT color="#FF00FF">1</FONT></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The result I want to get is&nbsp;the number of students in each manager who are doing and not doing their homework.</P><P>Students attend more than one school and I would like to count them in the main school.<BR />Since a manager may be responsible for more than one area, we also output the areas.</P><P>&nbsp;</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Area</TD><TD>manager_cd</TD><TD>HW done</TD><TD>not yet</TD><TD><FONT color="#808080">school</FONT></TD></TR><TR><TD>A</TD><TD>200</TD><TD><FONT color="#FF00FF">3</FONT></TD><TD>&nbsp;</TD><TD><FONT color="#808080">100,101</FONT></TD></TR><TR><TD>B</TD><TD>200</TD><TD>&nbsp;</TD><TD>2</TD><TD><FONT color="#808080">102</FONT></TD></TR><TR><TD>B</TD><TD>201</TD><TD><FONT color="#FF00FF">1</FONT></TD><TD>&nbsp;</TD><TD><FONT color="#808080">103</FONT></TD></TR></TBODY></TABLE><P>&nbsp;</P> Sun, 29 Jan 2023 08:13:04 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048724#M104918 mami 2023-01-29T08:13:04Z Re: Calculat of distant related values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048921#M104928 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="506141" data-lia-user-login="mami" class="lia-mention lia-mention-user">mami</a>&nbsp;</P> <P>how are these tables connected? Can you please share a screenshot of tge dara model showing the relationships?</P> Sun, 29 Jan 2023 13:09:37 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048921#M104928 tamerj1 2023-01-29T13:09:37Z Re: Calculat of distant related values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048924#M104929 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317289" data-lia-user-login="tamerj1" class="lia-mention lia-mention-user">tamerj1</a>&nbsp;<BR />Thank you for your help.</P><P>This is how I connect.</P><P><img /></P><P><img /></P><P>&nbsp;</P> Sun, 29 Jan 2023 13:17:35 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048924#M104929 mami 2023-01-29T13:17:35Z Re: Calculat of distant related values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048943#M104931 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="506141" data-lia-user-login="mami" class="lia-mention lia-mention-user">mami</a>&nbsp;<BR />Please refer to attached sample file with the solution</P> <P><img /><img /></P> Sun, 29 Jan 2023 14:05:09 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048943#M104931 tamerj1 2023-01-29T14:05:09Z Re: Calculat of distant related values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048944#M104932 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="506141" data-lia-user-login="mami" class="lia-mention lia-mention-user">mami</a>&nbsp;<BR />However, I have to say that School_Manage table is better to be part of the School table. There is absolutely&nbsp;no point of having two tables while adding few columns will do exactly the same job even more efficiently.</P> Sun, 29 Jan 2023 14:09:01 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3048944#M104932 tamerj1 2023-01-29T14:09:01Z Re: Calculat of distant related values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3050440#M105048 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317289" data-lia-user-login="tamerj1" class="lia-mention lia-mention-user">tamerj1</a>&nbsp;<BR />thank you for your advice.</P><P>You are correct.School table was meaningless.</P> Mon, 30 Jan 2023 13:11:54 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3050440#M105048 mami 2023-01-30T13:11:54Z Re: Calculat of distant related values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3050446#M105051 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="317289" data-lia-user-login="tamerj1" class="lia-mention lia-mention-user">tamerj1</a>&nbsp;<BR />Thank you for your help!</P><P>This was very helpful<span class="lia-unicode-emoji" title=":grinning_squinting_face:">😆</span></P><P>I didn't&nbsp;know crossfilter<span class="lia-unicode-emoji" title=":grinning_face_with_sweat:">😅</span></P> Mon, 30 Jan 2023 13:15:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculat-of-distant-related-values/m-p/3050446#M105051 mami 2023-01-30T13:15:46Z