Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
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
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 Smoupre,
Based on the below link
https://msdn.microsoft.com/en-us/library/gg492171.aspx
I think we can use GROUP BY in SUMMARIZE, will we able to use MAX also or its not needed.
I am now only started to learn DAX..
Will you able to suggest some links or videos that would help me learn DAX from Beginner to Expert level. It would help a lot to understand Power BI.
Thanks,
I learn by looking at how other people have solved problems. In that vein, take a look at this article as it may help you:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
If you are more of a book learner, check out Matt Allington's excellent book "Learn to Write DAX"
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |