Using Group By and MAX

[madan27]

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.

[Greg_Deckler · Accepted solution]

Hmm, you are probably looking at having to use SUMMARIZE then

[v-yulgu-msft · Accepted solution]

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

[Greg_Deckler]

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.

[madan27]

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.

[Greg_Deckler · Accepted solution]

Hmm, you are probably looking at having to use SUMMARIZE then

[madan27]

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,

[Greg_Deckler]

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"