Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I am looking to create two columns - Old Job Title and New Job Title.
My table has the following columns -
Emp No, Name, Job Title, Valid From, Valid To.
The Valid From and Valid To are dates showing the start and end date of the job.
I am looking to display my data as below:
Emp No, Name, Old Job Title, New Job Title, Valid From
Solved! Go to Solution.
Hi @yaman123 ,
Try this code:
Table 2 =
VAR _ADDRANK =
ADDCOLUMNS (
'Table',
"Rank",
RANKX (
FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) ),
'Table'[Valid From],
,
ASC
)
)
VAR _SUMMARIZE =
SUMMARIZE (
_ADDRANK,
[Emp No],
[Name],
[Rank],
"Old Job Title", CALCULATE ( MAX ( 'Table'[Job Title] ) ),
"New Job Title",
MAXX (
FILTER (
_ADDRANK,
[Name] = EARLIER ( [Name] )
&& [Rank]
= EARLIER ( [Rank] ) + 1
),
[Job Title]
),
"Valid From",
MAXX (
FILTER (
_ADDRANK,
[Name] = EARLIER ( [Name] )
&& [Rank]
= EARLIER ( [Rank] ) + 1
),
[Valid From]
)
)
RETURN
SUMMARIZE (
FILTER ( _SUMMARIZE, [New Job Title] <> BLANK () ),
[Emp No],
[Name],
[Old Job Title],
[New Job Title],
[Valid From]
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@yaman123 , Try new columns like
Old job Title =
var _max = maxx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] < earlier(Table[Valid From])), Table[Valid From])
return
maxx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] =_max), Table[Job Title])
Next job Title =
var _max = Minx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] > earlier(Table[Valid From])), Table[Valid From])
return
maxx(filter(Table, Table[Emp No] = earlier(Table[Emp No]) && Table[Valid From] =_max), Table[Job Title])
Hi @amitchandak
I am looking to display the old job and new job titles on one line. The query you have provided is giving me two or more rows per person. Can this be done to show in the one line only?
Hi @yaman123 ,
According to your statement, I think you have old Jobs Titles and new Job Titles to same [Emp No] in your original table. We can determind whether the Job Title is new or old by Vaild From Date. Currently your requirement, is to show old Jobs Titles and new Job Titles to same [Emp No] in one line.
I think your data will display like as below.
Emp No, Name, Old Job Title, New Job Title, Valid From to Old Job Title, Valid From to New Job Title
My Sample table is as below.
Try this code to create a calcualted table.
Table 2 =
SUMMARIZE (
'Table',
'Table'[Emp No],
'Table'[Name],
"Valid From to Old Job Title", CALCULATE ( MIN ( 'Table'[Valid From] ) ),
"Old Job Title",
CALCULATE (
MAX ( 'Table'[Job Title] ),
FILTER ( 'Table', 'Table'[Valid From] = MIN ( 'Table'[Valid From] ) )
),
"Valid From to New Job Title", CALCULATE ( MAX ( 'Table'[Valid From] ) ),
"New Job Title",
CALCULATE (
MAX ( 'Table'[Job Title] ),
FILTER ( 'Table', 'Table'[Valid From] = MAX ( 'Table'[Valid From] ) )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rzhou-msft
The query above is giviing me results for all employees even though they have not changed job titles. E.g Employee A is showing New Job Title as CEO and Old Job Title as CEO.
Is there a way to only show employees who have had a change in the job title?
Hi @yaman123 ,
According to your statement, I think you have situations that some names' Old Job Title and New Job Title are the same.
I update my sample, we can see that, User C has same Old Job Title and New Job Title.
I add a filter in my code that I distinct count the Job Title for each Name and we will only summarize Names whose Job Title counts is larger than 1.
Table 2 =
SUMMARIZE (
FILTER (
'Table',
VAR _Count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Job Title] ),
FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
)
RETURN
_Count > 1
),
'Table'[Emp No],
'Table'[Name],
"Valid From to Old Job Title", CALCULATE ( MIN ( 'Table'[Valid From] ) ),
"Old Job Title",
CALCULATE (
MAX ( 'Table'[Job Title] ),
FILTER ( 'Table', 'Table'[Valid From] = MIN ( 'Table'[Valid From] ) )
),
"Valid From to New Job Title", CALCULATE ( MAX ( 'Table'[Valid From] ) ),
"New Job Title",
CALCULATE (
MAX ( 'Table'[Job Title] ),
FILTER ( 'Table', 'Table'[Valid From] = MAX ( 'Table'[Valid From] ) )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The amended query is not giving me the correct results unfortunately.
I have the below sample dataset:
Emp No | Name | Job Title | Valid From | Valid To |
10001 | A | Whey Technician | 15/01/2020 | 02/08/2020 |
10001 | A | Process Technician | 03/08/2020 | 28/02/2021 |
10001 | A | Cheese Technician | 01/03/2021 | 31/10/2021 |
10001 | A | Shift Manager | 01/11/2021 | 28/02/2022 |
10001 | A | Cheese Technician | 01/03/2022 | 31/12/9999 |
10002 | B | Engineering Manager | 19/05/2020 | 31/10/2021 |
10002 | B | Production Manager | 01/11/2021 | 31/12/2021 |
10002 | B | Engineering Manager | 01/01/2022 | 31/12/9999 |
10003 | C | Data Analyst | 03/12/2018 | 31/12/9999 |
Expected Output:
Emp No | Name | Old Job Title | New Job Title | Valid From |
10001 | A | Whey Technician | Process Technician | 03/08/2020 |
10001 | A | Process Technician | Cheese Technician | 01/03/2021 |
10001 | A | Cheese Technician | Shift Manager | 01/11/2021 |
10001 | A | Shift Manager | Cheese Technician | 01/03/2022 |
10002 | B | Engineering Manager | Production Manager | 01/11/2021 |
10002 | B | Production Manager | Engineering Manager | 01/01/2022 |
Hope this helps
Hi @yaman123 ,
Try this code:
Table 2 =
VAR _ADDRANK =
ADDCOLUMNS (
'Table',
"Rank",
RANKX (
FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) ),
'Table'[Valid From],
,
ASC
)
)
VAR _SUMMARIZE =
SUMMARIZE (
_ADDRANK,
[Emp No],
[Name],
[Rank],
"Old Job Title", CALCULATE ( MAX ( 'Table'[Job Title] ) ),
"New Job Title",
MAXX (
FILTER (
_ADDRANK,
[Name] = EARLIER ( [Name] )
&& [Rank]
= EARLIER ( [Rank] ) + 1
),
[Job Title]
),
"Valid From",
MAXX (
FILTER (
_ADDRANK,
[Name] = EARLIER ( [Name] )
&& [Rank]
= EARLIER ( [Rank] ) + 1
),
[Valid From]
)
)
RETURN
SUMMARIZE (
FILTER ( _SUMMARIZE, [New Job Title] <> BLANK () ),
[Emp No],
[Name],
[Old Job Title],
[New Job Title],
[Valid From]
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
83 | |
82 | |
67 | |
49 |
User | Count |
---|---|
135 | |
111 | |
100 | |
65 | |
62 |