Repeatable Counting Calculation in Query Editor

[ClintClark]

I am creating an application that needs to be able to look at how often different employees have changed jobs. I need to know how many "Job Changes" each person has.  To do this, I have the records of all employees and the different Business Titles that they have held. The logic for the calculation would be to count the number of Business Titles that a person has, subtract one, and you have their "Job Changes" amount.

An example of the data would be something like this:

Employee ID                      Business Title

1548964                              HR Generalist

1548964                              HR Specialist

1548964                              HR Manager

1548964                              HR Director

The value I would want for this person would be 3 Job Changes.

How can I calculate this value for each specific employee ID? Is there a way to do a loop calculation or something similar? I came up with a solution by creating a new measure in Dax, but I need the value to be in the query editor so that I can filter on the values later. For exampe, I will need to select only those who have had 4 job changes.

Any help is appreciated.

Thanks!

[Vvelarde · Accepted solution]

@ClintClark

Hi, in Query Editor (no M way)

1. Select Employee Id- Pivot Columns - Count Business Title

2. Select both columns - Unpivot Columns

3. Select Value Column --Transform - Standard - Substract  Enter 1 Ok

4. Change Headers Name

[ClintClark]

This worked perfectly.

Thanks for the help!

[BetterCallFrank]

Hi @ClintClark,

you can use a calculated column with this DAX : 

# Job Changes = 
CALCULATE( 
 COUNTROWS( VALUES(t1[Business Title]) ),
 ALLEXCEPT(t1, t1[Employee ID])
) - 1 

HTH,

Frank