topic Count of Employees in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3723056#M144985 <P>Dear Experts,</P><P>I wanted to know how many employees joined in both years from the following sample data.</P><UL><LI>If I am using the distinct count function on code then it is calculating the employee twice since the code is different but the employee’s name and CNIC is the same.</LI><LI>We can only calculate the headcount by CNIC number since it is the same in 2023 and 2023 like Mr. Ali in the sample data, using the distinct count function but if I use that it counts Mr. Ali on Jan 24 as well where it has already been joined on Jan 2023.</LI></UL><P>Please suggest the DAX calculation as suggested in the required table:</P><P>&nbsp;</P><P>Sample Data:</P><TABLE><TBODY><TR><TD>S.No</TD><TD>Code</TD><TD>Name&nbsp;</TD><TD>CNIC&nbsp;</TD><TD>Joining Date</TD></TR><TR><TD>1</TD><TD>10438</TD><TD>Imtiaz</TD><TD>45555887771</TD><TD>01-01-23</TD></TR><TR><TD>2</TD><TD>10320</TD><TD>Safdar</TD><TD>45555887772</TD><TD>01-01-23</TD></TR><TR><TD>3</TD><TD>10553</TD><TD>Ali&nbsp;</TD><TD>45555887773</TD><TD>01-01-23</TD></TR><TR><TD>4</TD><TD>10042</TD><TD>Hussain</TD><TD>45555887774</TD><TD>01-01-23</TD></TR><TR><TD>5</TD><TD>10399</TD><TD>Arbaas</TD><TD>45555887775</TD><TD>01-02-23</TD></TR><TR><TD>6</TD><TD>10953</TD><TD>Waseem</TD><TD>45555887776</TD><TD>01-02-23</TD></TR><TR><TD>7</TD><TD>10993</TD><TD>khurram</TD><TD>45555887777</TD><TD>01-02-23</TD></TR><TR><TD>8</TD><TD>10164</TD><TD>adil</TD><TD>45555887778</TD><TD>01-02-23</TD></TR><TR><TD>9</TD><TD>10847</TD><TD>sunny</TD><TD>45555887779</TD><TD>01-02-23</TD></TR><TR><TD>10</TD><TD>10959</TD><TD>maheen</TD><TD>45555887780</TD><TD>01-03-23</TD></TR><TR><TD>11</TD><TD>10541</TD><TD>Idrees</TD><TD>45555887781</TD><TD>01-03-23</TD></TR><TR><TD>12</TD><TD>10326</TD><TD>Ijaz</TD><TD>45555887782</TD><TD>01-03-23</TD></TR><TR><TD>13</TD><TD>10507</TD><TD>Sibghat</TD><TD>45555887783</TD><TD>01-03-23</TD></TR><TR><TD>14</TD><TD>10319</TD><TD>Alina</TD><TD>45555887784</TD><TD>01-03-23</TD></TR><TR><TD>1</TD><TD>10333</TD><TD>Ali&nbsp;</TD><TD>45555887773</TD><TD>01-01-24</TD></TR><TR><TD>2</TD><TD>10945</TD><TD>zakir</TD><TD>45555887774</TD><TD>01-01-24</TD></TR><TR><TD>3</TD><TD>10951</TD><TD>shehzad</TD><TD>45555887775</TD><TD>01-01-24</TD></TR><TR><TD>4</TD><TD>10671</TD><TD>Muslim</TD><TD>45555887776</TD><TD>01-01-24</TD></TR><TR><TD>5</TD><TD>10392</TD><TD>ghulam</TD><TD>45555887777</TD><TD>01-02-24</TD></TR><TR><TD>6</TD><TD>10011</TD><TD>rashid</TD><TD>45555887778</TD><TD>01-02-24</TD></TR><TR><TD>7</TD><TD>10796</TD><TD>khurram</TD><TD>45555887779</TD><TD>01-02-24</TD></TR><TR><TD>8</TD><TD>10606</TD><TD>adil</TD><TD>45555887780</TD><TD>01-02-24</TD></TR><TR><TD>9</TD><TD>10328</TD><TD>sunny</TD><TD>45555887781</TD><TD>01-02-24</TD></TR><TR><TD>10</TD><TD>10401</TD><TD>maheen</TD><TD>45555887782</TD><TD>01-03-24</TD></TR><TR><TD>11</TD><TD>10372</TD><TD>Idrees</TD><TD>45555887783</TD><TD>01-03-24</TD></TR><TR><TD>12</TD><TD>10058</TD><TD>Ijaz</TD><TD>45555887784</TD><TD>01-03-24</TD></TR><TR><TD>13</TD><TD>10735</TD><TD>Sibghat</TD><TD>45555887785</TD><TD>01-03-24</TD></TR><TR><TD>14</TD><TD>10507</TD><TD>Zakir</TD><TD>45555887774</TD><TD>01-03-24</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Required result:</P><TABLE><TBODY><TR><TD>Year</TD><TD>Month</TD><TD>No. of Emp</TD><TD><STRONG>Required</STRONG></TD></TR><TR><TD>2024</TD><TD>Jan</TD><TD>4</TD><TD>3</TD></TR><TR><TD>2024</TD><TD>feb</TD><TD>5</TD><TD>5</TD></TR><TR><TD>2024</TD><TD>Mar</TD><TD>5</TD><TD>4</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>14</TD><TD>12</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Regards,</P><P>Saad Hanif</P> Mon, 26 Feb 2024 07:01:43 GMT Saad_Hanif456 2024-02-26T07:01:43Z Count of Employees https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3723056#M144985 <P>Dear Experts,</P><P>I wanted to know how many employees joined in both years from the following sample data.</P><UL><LI>If I am using the distinct count function on code then it is calculating the employee twice since the code is different but the employee’s name and CNIC is the same.</LI><LI>We can only calculate the headcount by CNIC number since it is the same in 2023 and 2023 like Mr. Ali in the sample data, using the distinct count function but if I use that it counts Mr. Ali on Jan 24 as well where it has already been joined on Jan 2023.</LI></UL><P>Please suggest the DAX calculation as suggested in the required table:</P><P>&nbsp;</P><P>Sample Data:</P><TABLE><TBODY><TR><TD>S.No</TD><TD>Code</TD><TD>Name&nbsp;</TD><TD>CNIC&nbsp;</TD><TD>Joining Date</TD></TR><TR><TD>1</TD><TD>10438</TD><TD>Imtiaz</TD><TD>45555887771</TD><TD>01-01-23</TD></TR><TR><TD>2</TD><TD>10320</TD><TD>Safdar</TD><TD>45555887772</TD><TD>01-01-23</TD></TR><TR><TD>3</TD><TD>10553</TD><TD>Ali&nbsp;</TD><TD>45555887773</TD><TD>01-01-23</TD></TR><TR><TD>4</TD><TD>10042</TD><TD>Hussain</TD><TD>45555887774</TD><TD>01-01-23</TD></TR><TR><TD>5</TD><TD>10399</TD><TD>Arbaas</TD><TD>45555887775</TD><TD>01-02-23</TD></TR><TR><TD>6</TD><TD>10953</TD><TD>Waseem</TD><TD>45555887776</TD><TD>01-02-23</TD></TR><TR><TD>7</TD><TD>10993</TD><TD>khurram</TD><TD>45555887777</TD><TD>01-02-23</TD></TR><TR><TD>8</TD><TD>10164</TD><TD>adil</TD><TD>45555887778</TD><TD>01-02-23</TD></TR><TR><TD>9</TD><TD>10847</TD><TD>sunny</TD><TD>45555887779</TD><TD>01-02-23</TD></TR><TR><TD>10</TD><TD>10959</TD><TD>maheen</TD><TD>45555887780</TD><TD>01-03-23</TD></TR><TR><TD>11</TD><TD>10541</TD><TD>Idrees</TD><TD>45555887781</TD><TD>01-03-23</TD></TR><TR><TD>12</TD><TD>10326</TD><TD>Ijaz</TD><TD>45555887782</TD><TD>01-03-23</TD></TR><TR><TD>13</TD><TD>10507</TD><TD>Sibghat</TD><TD>45555887783</TD><TD>01-03-23</TD></TR><TR><TD>14</TD><TD>10319</TD><TD>Alina</TD><TD>45555887784</TD><TD>01-03-23</TD></TR><TR><TD>1</TD><TD>10333</TD><TD>Ali&nbsp;</TD><TD>45555887773</TD><TD>01-01-24</TD></TR><TR><TD>2</TD><TD>10945</TD><TD>zakir</TD><TD>45555887774</TD><TD>01-01-24</TD></TR><TR><TD>3</TD><TD>10951</TD><TD>shehzad</TD><TD>45555887775</TD><TD>01-01-24</TD></TR><TR><TD>4</TD><TD>10671</TD><TD>Muslim</TD><TD>45555887776</TD><TD>01-01-24</TD></TR><TR><TD>5</TD><TD>10392</TD><TD>ghulam</TD><TD>45555887777</TD><TD>01-02-24</TD></TR><TR><TD>6</TD><TD>10011</TD><TD>rashid</TD><TD>45555887778</TD><TD>01-02-24</TD></TR><TR><TD>7</TD><TD>10796</TD><TD>khurram</TD><TD>45555887779</TD><TD>01-02-24</TD></TR><TR><TD>8</TD><TD>10606</TD><TD>adil</TD><TD>45555887780</TD><TD>01-02-24</TD></TR><TR><TD>9</TD><TD>10328</TD><TD>sunny</TD><TD>45555887781</TD><TD>01-02-24</TD></TR><TR><TD>10</TD><TD>10401</TD><TD>maheen</TD><TD>45555887782</TD><TD>01-03-24</TD></TR><TR><TD>11</TD><TD>10372</TD><TD>Idrees</TD><TD>45555887783</TD><TD>01-03-24</TD></TR><TR><TD>12</TD><TD>10058</TD><TD>Ijaz</TD><TD>45555887784</TD><TD>01-03-24</TD></TR><TR><TD>13</TD><TD>10735</TD><TD>Sibghat</TD><TD>45555887785</TD><TD>01-03-24</TD></TR><TR><TD>14</TD><TD>10507</TD><TD>Zakir</TD><TD>45555887774</TD><TD>01-03-24</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Required result:</P><TABLE><TBODY><TR><TD>Year</TD><TD>Month</TD><TD>No. of Emp</TD><TD><STRONG>Required</STRONG></TD></TR><TR><TD>2024</TD><TD>Jan</TD><TD>4</TD><TD>3</TD></TR><TR><TD>2024</TD><TD>feb</TD><TD>5</TD><TD>5</TD></TR><TR><TD>2024</TD><TD>Mar</TD><TD>5</TD><TD>4</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>14</TD><TD>12</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Regards,</P><P>Saad Hanif</P> Mon, 26 Feb 2024 07:01:43 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3723056#M144985 Saad_Hanif456 2024-02-26T07:01:43Z Re: Count of Employees https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3723155#M144987 <P>=COUNTROWS(Except(SUMMARIZE(Table1,Table1[CNIC],Table1[Name]),CALCULATETABLE(SUMMARIZE(Table1,Table1[CNIC],Table1[Name]),'Table1'[Joining Date]&lt;MIN('Calendar'[Date]),ALL('Calendar'))))</P> <P><img /></P> <P>&nbsp;</P> Mon, 26 Feb 2024 07:39:38 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3723155#M144987 wdx223_Daniel 2024-02-26T07:39:38Z Re: Count of Employees https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3723802#M145020 <P>Thank you for the help but it is not happening, may be i have a slicer of Calendar [month] and calendar [year]</P> Mon, 26 Feb 2024 12:08:30 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3723802#M145020 Saad_Hanif456 2024-02-26T12:08:30Z Re: Count of Employees https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3725897#M145125 <P>Dear Experts,</P><P>&nbsp;</P><P>Please help me in this regards</P><P>&nbsp;</P><P>Regards,</P><P>Saad Hanif</P> Tue, 27 Feb 2024 03:51:00 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3725897#M145125 Saad_Hanif456 2024-02-27T03:51:00Z Re: Count of Employees https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3728310#M145254 <P>I have also attached the sample PBIX files in the google drive, need to calculate the Hires month wise and condition mentioned in the remarks column</P><P><A href="https://drive.google.com/file/d/1f2A6KLuCjnq05UxS0xB8Li7XD-E9SqgG/view?usp=sharing" target="_blank">https://drive.google.com/file/d/1f2A6KLuCjnq05UxS0xB8Li7XD-E9SqgG/view?usp=sharing</A></P><P><BR />Please help me in this regards so that i am able to move forward.</P> Tue, 27 Feb 2024 17:05:36 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Count-of-Employees/m-p/3728310#M145254 Saad_Hanif456 2024-02-27T17:05:36Z