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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 have created a new column called job change, the DAX command below doesn't work. I am stuck with the value function here, I know for each job year and each employee id, there is only one line of data existing in DB, but I don't know how to extract JobCode2 value. 

 

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 = value('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=value('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
if(prevBand < CurrentBand, "Promotion",
if(prevJob<>CurrentJob,"Lateral Movement","no change"))

 

 

 

 

row by row comparison.PNG

1 ACCEPTED SOLUTION

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

 

Fowmy_0-1597733280899.png

 

________________________

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

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

@Anonymous - Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg, 

I want to achieve is to compare employee's job code and band year by year, 

if the band has increased from previous year to current, that means the employee receives a promotion,

if the band has remained the same/decreased, and the job code remain the same, the employee goes through a laternal job movement, 

if band remained the same/decreased and job code remains the same, the employee's job has not changed. 

 

I know it's easy to the embedded if functoin in Excel, but given everything is automated in Power BI so far, i don't want to add a manual input formula here.

@Anonymous 

Your 2nd and 3rd conditions are the same I believe, can you check?

________________________

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

My apologies for the typo in my statement. It is corrected below. However, that doesn't explain why the DAX formula fails, i have tested it, it looks the value() part has caused a syntax error. 

 

if the band has increased from previous year to current, that means the employee receives a promotion,

if the band has remained the same/decreased && the job code has changed from previous year, the employee goes through a laternal job movement, 

if band remained the same/decreased and job code remains the same, the employee's job has not changed. 

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

 

Fowmy_0-1597733280899.png

 

________________________

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

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Tried replicating the same but getting below error

 

 

DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

HI @Anonymous ,

 

 

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

 

 

Try troubleshooting removing the commented value one by one and see if you are getting the correct values.

 

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

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)

Hi @Anonymous ,

 

Can you share some sample data in text format.

You can remove all sensitive information

 

Regards,

Harsh Nathani

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors