Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

how to compare row values based on multiple criteria

Hi,  I have a table which list employee's job title and job bands across years. I have sorted data by employee id and job year.  I need to find out how employees' job has changed over years.  I hav...
Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    Anonymous 

    Hi, Add the following DAX code as a new column in your table and correct the table and column names as necessary.

    Job Change Status = 
VAR U = [User ID]
VAR Y = [Job Year]
VAR _TABLE = 
FILTER(
    ADDCOLUMNS(
        FILTER (
            GENERATE (
                'merit 18-21',
                SELECTCOLUMNS (
                    'merit 18-21',
                    "2YR", 'merit 18-21'[Job Year],
                    "2BAND", 'merit 18-21'[band],
                    "2JOB", 'merit 18-21'[JobCode2],
                    "2USER", 'merit 18-21'[User ID]
                )
            ),
            'merit 18-21'[User ID] = [2USER] && 'merit 18-21'[Job Year] = [2YR] + 1
        ),
        "Job Change",
            SWITCH(
                TRUE(),
                NOT('merit 18-21'[band] > [2band]) && 'merit 18-21'[JobCode2] = [2JOB], "NO CHANGE",
                'merit 18-21'[band] > [2band], "PROMOTION",
                NOT('merit 18-21'[band] > [2band]) && 'merit 18-21'[JobCode2] <> [2JOB], "LATERAL JOB MOVEMENT",
                "STATUS NOT IDENTIFIED"                
            )
    ),
    'merit 18-21'[User ID] = U && 'merit 18-21'[Job Year] = Y
) 
 RETURN
MAXX(_TABLE,[Job Change])

     

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube, LinkedIn

     



Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks for coming back, Harsh. 

I made a bit update to remove syntax error (see below), the commands works with no error message, but the result is not correct. It doesn't work when there is no previous data (e.g., only 2018 data exist for user id 00001). 

I'm also wondering if MAX formula will work when it deals with a text column (here column JobCode2)?

 

job change =
VAR prevBand = MAXX(filter('merit 18-21', 'merit 18-21'[User ID] =EARLIER('merit 18-21'[User ID]) && 'merit 18-21'[Job Year] = EARLIER('merit 18-21'[Job Year])-1),'merit 18-21'[band])
VAR CurrentBand = MAXX(filter('merit 18-21', 'merit 18-21'[User ID] =EARLIER('merit 18-21'[User ID]) && 'merit 18-21'[Job Year] = EARLIER('merit 18-21'[Job Year])),'merit 18-21'[band])
VAR prevJob = CALCULATE(MAX('merit 18-21'[JobCode2]), filter('merit 18-21', 'merit 18-21'[User ID] =EARLIER('merit 18-21'[User ID]) && 'merit 18-21'[Job Year] = EARLIER('merit 18-21'[Job Year])-1))
VAR currentJob=CALCULATE(MAX('merit 18-21'[JobCode2]), filter('merit 18-21', 'merit 18-21'[User ID] =EARLIER('merit 18-21'[User ID]) && 'merit 18-21'[Job Year] = EARLIER('merit 18-21'[Job Year])))

RETURN
SWITCH(
TRUE(),

prevBand < CurrentBand, "Promotion",
prevJob <> CurrentJob,"Lateral Movement",

"no change")

//prevBand
//CurrentBand
//prevJob
//CurrentJob)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

Can you share some sample data in text format.

You can remove all sensitive information

 

Regards,

Harsh Nathani