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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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])

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors