Forum Discussion
Using Group By and MAX
Hi,
I am new to power BI. I am having a table as below
EmployeeID | DesigDate | YearMonth | Designation |
1 | 4/1/2017 | 201704 | Engineer |
4 | 4/4/2017 | 201704 | Engineer |
2 | 4/1/2017 | 201704 | SR.Engineer |
3 | 3/1/2017 | 201703 | Engineer |
2 | 4/2/2017 | 201704 | Asst.Manager |
2 | 4/3/2017 | 201704 | Manager |
3 | 4/4/2017 | 201704 | SR.Engineer |
5 | 5/5/2017 | 201705 | Engineer |
1 | 5/6/2017 | 201705 | SR.Engineer |
4 | 5/20/2017 | 201705 | SR.Engineer |
From the tables I need to get below result set
YearMonth | Designation | Count |
201703 | Engineer | 1 |
201704 | Engineer | 2 |
201704 | SR.Engineer | 1 |
201704 | SR.Engineer | 1 |
201704 | Asst.Manager | 1 |
201705 | Engineer | 1 |
201705 | SR.Engineer | 2 |
Basically I need to find, at each month how many people has been promoted to each designation.
In April Month
EmpoyeeId 1 and 4 has been promoted to Engineer, So in the result we are getting count as 2.
EmployeeID 2, has got 3 promotions within the month and his recent designation within the month is Manager, So in the manager we are marking the count as 1.
Kindly help me in achieving this result. It would be very much helpful.
Hmm, you are probably looking at having to use SUMMARIZE then
Hi madan27,
Based on the sample data you provided, please refer to below steps:
Create a new table to filter records
Employee Table2 = FILTER ( 'Employee Table', 'Employee Table'[DesigDate] = CALCULATE ( MAX ( 'Employee Table'[DesigDate] ), ALLEXCEPT ( 'Employee Table', 'Employee Table'[YearMonth], 'Employee Table'[EmployeeID] ) ) )Then, summarize above table and get the count value.
Employee Table3 = SUMMARIZE ( 'Employee Table2', 'Employee Table2'[YearMonth], 'Employee Table2'[Designation], "Count", COUNT ( 'Employee Table2'[EmployeeID] ) )Best regards,
Yuliana Gu
6 Replies
I'm not sure I follow this completely given your sample output but what it looks like you want to do is to essentially count the difference in Designations between the current YearMonth and the previous YearMonth. More or less, correct?
Seems like you could get there using a simple COUNTROWS measure and then another measure that another measure that subtracted the previous month's count. I really think you are probably going to need to end up with a date table so that you can use the time intelligence functions.
Hi Smoupre,
Thanks for your response.
I am not Sure, I will be able to achive this using COUNTROWS, please correct me if I am wrong. Within the same month a employee may have mutiple degination change also, if so I need to consider the latest designation only for the count.
In SQL query we can achive this using like GROUP BY Employeed, MAX(DATE) and COUNT the designation which is related to this EmployeeID and DATE.
Let me know if you need any further details.
Yes, I do have date table.
Hmm, you are probably looking at having to use SUMMARIZE then
Hi madan27,
Based on the sample data you provided, please refer to below steps:
Create a new table to filter records
Employee Table2 = FILTER ( 'Employee Table', 'Employee Table'[DesigDate] = CALCULATE ( MAX ( 'Employee Table'[DesigDate] ), ALLEXCEPT ( 'Employee Table', 'Employee Table'[YearMonth], 'Employee Table'[EmployeeID] ) ) )Then, summarize above table and get the count value.
Employee Table3 = SUMMARIZE ( 'Employee Table2', 'Employee Table2'[YearMonth], 'Employee Table2'[Designation], "Count", COUNT ( 'Employee Table2'[EmployeeID] ) )Best regards,
Yuliana Gu
