topic Re: Churn rate by tenure group and age group in DAX Commands and Tips

topic Re: Churn rate by tenure group and age group in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3183475#M115167 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="490244" data-lia-user-login="ejafarov" class="lia-mention lia-mention-user">ejafarov</a> ,</P> <P> </P> <P>Do you mean you want the measure results to be affected by the slicer?</P> <P>You can use <A href="https://learn.microsoft.com/en-us/dax/allselected-function-dax" target="_self">ALLSELECTED ()</A> function.</P> <P>Please refer to the following documents for more information.</P> <P><A href="https://www.sqlbi.com/articles/introducing-allselected-in-dax/" target="_blank" rel="noopener">Introducing ALLSELECTED in DAX - SQLBI</A></P> <P><A href="https://www.mssqltips.com/sqlservertip/5417/calculating-employee-attrition-rate-with-dax-part-1/" target="_blank">Calculating Employee Attrition Rate with DAX – Part 1 (mssqltips.com)</A></P> <P><A href="https://community.powerbi.com/t5/Desktop/Customer-Churn-Rate/m-p/181950" target="_blank" rel="noopener">Customer Churn Rate - Microsoft Power BI Community</A></P> <P><A href="https://community.powerbi.com/t5/DAX-Commands-and-Tips/Churn-Rate-Measure/m-p/750366" target="_blank" rel="noopener">Churn Rate Measure - Microsoft Power BI Community</A></P> <P> </P> <P>Best Regards,</P> <P>Neeko Tang</P> <P>If this post  <STRONG>helps</STRONG>, then please consider <STRONG><EM>Accept it as the solution </EM></STRONG> to help the other members find it more quickly. </P> <P> </P> Wed, 12 Apr 2023 08:19:36 GMT Anonymous 2023-04-12T08:19:36Z Churn rate by tenure group and age group https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3172336#M114385 <P>Dear Power BI Community,</P><P>I hope this message finds you well. I am currently working on a project where I need to calculate the churn rate for combinations of tenure and age groups in Power BI. I have multiple measures that find employee count for active employees and employees 12 months before the selected date, for each combination of tenure groups and age groups.</P><P>My goal is to find the churn rate (turnover) for each combination of tenure and age groups for active employees and employees 12 months before the selected date.</P><P>Here are the measures I have:</P><OL><LI>ActiveEmployeeCount:</LI><LI>EmployeeCount12MonthsBefore</LI><LI>Employee count  left during 12 months </LI></OL><P>I need help creating a measure to calculate the churn rate and then display the results in a matrix visualization.</P><P>I have tried the following DAX formula to calculate the churn rate, but I would appreciate it if someone could confirm whether it's correct or suggest an alternative method:</P> Tue, 04 Apr 2023 17:34:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3172336#M114385 ejafarov 2023-04-04T17:34:08Z Re: Churn rate by tenure group and age group https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3172386#M114391 <P>Measure one Filtered =</P><P>CALCULATE (</P><P>[Cnt_Active_AgeGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])</P><P>)</P><P>Measure 2 Filtered =</P><P>CALCULATE (</P><P>[Cnt_LY_AgeGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])</P><P>)</P><P>Measure 3 Filtered =</P><P>CALCULATE (</P><P>[Cnt_Active_TenGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])</P><P>)</P><P>Measure 4 Filtered =</P><P>CALCULATE (</P><P>[Cnt_LY_TenureGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] = 'Join of age and tenure'[Age Group]</P><P>Measure one Filtered =</P><P>CALCULATE (</P><P>[Cnt_Active_AgeGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])</P><P>)</P><P>Measure 2 Filtered =</P><P>CALCULATE (</P><P>[Cnt_LY_AgeGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])</P><P>)</P><P>Measure 3 Filtered =</P><P>CALCULATE (</P><P>[Cnt_Active_TenGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] ='Join of age and tenure'[Age Group])</P><P>)</P><P>Measure 4 Filtered =</P><P>CALCULATE (</P><P>[Cnt_LY_TenureGr],</P><P>FILTER ('Join of age and tenure', [Tenure Bucket for active] = 'Join of age and tenure'[Tenure Group]),</P><P>FILTER ('Join of age and tenure', [Age Bucket for active] = 'Join of age and tenure'[Age Group])</P><P>)</P> Tue, 04 Apr 2023 18:18:49 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3172386#M114391 ejafarov 2023-04-04T18:18:49Z Re: Churn rate by tenure group and age group https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3181059#M115000 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="490244" data-lia-user-login="ejafarov" class="lia-mention lia-mention-user">ejafarov</a> ,</P> <P> </P> <P>Please try this:</P> <LI-CODE lang="markup">Measure one Filtered = CALCULATE ( [Cnt_Active_AgeGr], FILTER (All('Join of age and tenure'), MAX('Join of age and tenure'[Tenure Bucket for active] )= MAX('Join of age and tenure'[Tenure Group]) && MAX('Join of age and tenure[Age Bucket for active]) =MAX('Join of age and tenure'[Age Group]) )</LI-CODE> <P>If the above one can't help you get the desired result, please provide some <STRONG>sample data</STRONG> in your tables (<STRONG>exclude sensitive data</STRONG>) with <STRONG>Text</STRONG> format and your <STRONG>expected result</STRONG> with backend logic and special examples. <STRONG>It is better</STRONG> if you can share a <STRONG>simplified</STRONG> pbix file. Thank you.</P> <P> </P> <P>Best Regards,</P> <P>Neeko Tang</P> <P>If this post  <STRONG>helps</STRONG>, then please consider <STRONG><EM>Accept it as the solution </EM></STRONG> to help the other members find it more quickly. </P> Tue, 11 Apr 2023 05:58:06 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3181059#M115000 Anonymous 2023-04-11T05:58:06Z Re: Churn rate by tenure group and age group https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3183340#M115152 <P>Dear,</P><P> </P><P>Sorry for the unclear format. I am adding the sample table from my file.</P><P>Currently, I have measures to calculate the count of employees for age group and tenure group separately. However, I need a DAX measure that will consider the selected filter (e.g., 31.12.2022) and calculate the active employee count by a combination of age group and tenure group. Ultimately, I want to find the churn rate based on this data.</P><P> </P><P>Could you please explain me steps or dax?</P><P> </P><P>Thanks</P><P> </P><TABLE><TBODY><TR><TD>ID</TD><TD>Start Date</TD><TD>Resignation date</TD><TD>Birth Date</TD></TR><TR><TD>19</TD><TD>25/02/2009</TD><TD> </TD><TD>07/02/1985</TD></TR><TR><TD>37</TD><TD>11/05/2012</TD><TD> </TD><TD>24/12/1980</TD></TR><TR><TD>980</TD><TD>01/03/2017</TD><TD> </TD><TD>24/12/1992</TD></TR><TR><TD>1677</TD><TD>15/04/2022</TD><TD> </TD><TD>18/06/1981</TD></TR><TR><TD>990</TD><TD>20/12/2021</TD><TD> </TD><TD>09/07/1987</TD></TR><TR><TD>1259</TD><TD>03/06/2019</TD><TD> </TD><TD>09/02/1986</TD></TR><TR><TD>912</TD><TD>31/10/2016</TD><TD> </TD><TD>19/01/1988</TD></TR><TR><TD>1216</TD><TD>11/02/2019</TD><TD>28/12/2022</TD><TD>25/09/1994</TD></TR><TR><TD>1171</TD><TD>01/10/2018</TD><TD> </TD><TD>11/04/1995</TD></TR><TR><TD>1540</TD><TD>10/06/2021</TD><TD> </TD><TD>22/11/1979</TD></TR><TR><TD>1256</TD><TD>01/05/2019</TD><TD>14/01/2022</TD><TD>07/10/1986</TD></TR><TR><TD>1245</TD><TD>17/01/2022</TD><TD>12/05/2022</TD><TD>07/10/1986</TD></TR><TR><TD>1664</TD><TD>01/03/2022</TD><TD> </TD><TD>26/01/1993</TD></TR><TR><TD>1395</TD><TD>06/03/2020</TD><TD> </TD><TD>15/06/1977</TD></TR><TR><TD>1057</TD><TD>01/12/2017</TD><TD> </TD><TD>09/05/1996</TD></TR><TR><TD>1362</TD><TD>10/01/2020</TD><TD> </TD><TD>12/01/1990</TD></TR><TR><TD>1082</TD><TD>05/02/2018</TD><TD> </TD><TD>06/06/1995</TD></TR><TR><TD>1252</TD><TD>20/05/2019</TD><TD>05/05/2022</TD><TD>23/08/1985</TD></TR><TR><TD>1422</TD><TD>01/07/2020</TD><TD> </TD><TD>27/05/1988</TD></TR><TR><TD>1775</TD><TD>16/12/2022</TD><TD> </TD><TD>05/01/1999</TD></TR><TR><TD>1488</TD><TD>05/01/2021</TD><TD> </TD><TD>29/01/1994</TD></TR><TR><TD>1401</TD><TD>13/03/2020</TD><TD> </TD><TD>02/06/1966</TD></TR><TR><TD>85</TD><TD>01/02/2013</TD><TD> </TD><TD>08/12/1990</TD></TR><TR><TD>671</TD><TD>14/09/2016</TD><TD> </TD><TD>29/03/1982</TD></TR><TR><TD>1092</TD><TD>02/04/2018</TD><TD> </TD><TD>26/05/1995</TD></TR><TR><TD>1560</TD><TD>01/07/2021</TD><TD> </TD><TD>27/09/1999</TD></TR><TR><TD>1779</TD><TD>19/12/2022</TD><TD> </TD><TD>20/03/2001</TD></TR><TR><TD>290</TD><TD>24/08/2015</TD><TD> </TD><TD>06/11/1991</TD></TR><TR><TD>1634</TD><TD>09/12/2021</TD><TD>09/03/2022</TD><TD>11/01/1997</TD></TR><TR><TD>1695</TD><TD>25/05/2022</TD><TD>09/09/2022</TD><TD>14/04/2001</TD></TR><TR><TD>1479</TD><TD>01/12/2020</TD><TD> </TD><TD>30/01/1991</TD></TR><TR><TD>1534</TD><TD>01/06/2021</TD><TD>06/06/2022</TD><TD>08/12/1982</TD></TR><TR><TD>1358</TD><TD>08/01/2020</TD><TD> </TD><TD>23/04/1993</TD></TR></TBODY></TABLE> Wed, 12 Apr 2023 06:45:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3183340#M115152 ejafarov 2023-04-12T06:45:36Z Re: Churn rate by tenure group and age group https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3183475#M115167 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="490244" data-lia-user-login="ejafarov" class="lia-mention lia-mention-user">ejafarov</a> ,</P> <P> </P> <P>Do you mean you want the measure results to be affected by the slicer?</P> <P>You can use <A href="https://learn.microsoft.com/en-us/dax/allselected-function-dax" target="_self">ALLSELECTED ()</A> function.</P> <P>Please refer to the following documents for more information.</P> <P><A href="https://www.sqlbi.com/articles/introducing-allselected-in-dax/" target="_blank" rel="noopener">Introducing ALLSELECTED in DAX - SQLBI</A></P> <P><A href="https://www.mssqltips.com/sqlservertip/5417/calculating-employee-attrition-rate-with-dax-part-1/" target="_blank">Calculating Employee Attrition Rate with DAX – Part 1 (mssqltips.com)</A></P> <P><A href="https://community.powerbi.com/t5/Desktop/Customer-Churn-Rate/m-p/181950" target="_blank" rel="noopener">Customer Churn Rate - Microsoft Power BI Community</A></P> <P><A href="https://community.powerbi.com/t5/DAX-Commands-and-Tips/Churn-Rate-Measure/m-p/750366" target="_blank" rel="noopener">Churn Rate Measure - Microsoft Power BI Community</A></P> <P> </P> <P>Best Regards,</P> <P>Neeko Tang</P> <P>If this post  <STRONG>helps</STRONG>, then please consider <STRONG><EM>Accept it as the solution </EM></STRONG> to help the other members find it more quickly. </P> <P> </P> Wed, 12 Apr 2023 08:19:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Churn-rate-by-tenure-group-and-age-group/m-p/3183475#M115167 Anonymous 2023-04-12T08:19:36Z