Forum Discussion
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.
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 🙂
9 Replies
- Greg_Deckler
Community Champion
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.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.
