Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
yaman123
Post Patron
Post Patron

Old Job Title and New Job Title Columns

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 

1 ACCEPTED 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.

1.png

 

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.

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@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])

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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.

1.png

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.

2.png

 

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.

1.png

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.

1.png

 

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 amended query is not giving me the correct results unfortunately. 

 

I have the below sample dataset: 

Emp NoNameJob TitleValid FromValid To
10001AWhey Technician15/01/202002/08/2020
10001AProcess Technician03/08/202028/02/2021
10001ACheese Technician01/03/202131/10/2021
10001AShift Manager01/11/202128/02/2022
10001ACheese Technician01/03/202231/12/9999
10002BEngineering Manager19/05/202031/10/2021
10002BProduction Manager01/11/202131/12/2021
10002BEngineering Manager01/01/202231/12/9999
10003CData Analyst03/12/201831/12/9999

 

Expected Output:

 

Emp NoNameOld Job TitleNew Job TitleValid From
10001AWhey TechnicianProcess Technician03/08/2020
10001AProcess Technician Cheese Technician01/03/2021
10001ACheese TechnicianShift Manager01/11/2021
10001AShift ManagerCheese Technician01/03/2022
10002BEngineering ManagerProduction Manager01/11/2021
10002BProduction ManagerEngineering Manager01/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.

1.png

 

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.

Thanks so much @v-rzhou-msft. This gives me what I am looking for 🙂

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.