Create DAX to show 2 levels up or down as per score

[powerbidev123]

I have 2 tables with following details:

Table A: EmpId,Level,Name

Table B:EmpId,score

Levels table based on which 2 levels up and down can be determined:

Problem statement: 

For Employee 2 if score =-19(because score<=0) then shift the employee to 2 levels up i.e. if his current level is 10 which is senior clerk then shift the level to 12 which is senior supervisor .

For employee 3 if score=10 (because score>0) then shift the employee to 2 levels down i.e  if his current level is 10  which is senior clerk then shift the level to 8 which is clerk

Please ask if there are any doubts related to the table structure 

[xifeng_L · Accepted solution]

Hi @powerbidev123 

You can try below calculated column expression.

New Post = 
VAR Score = CALCULATE(SUM('Table B'[Score]),'Table B'[EmployeeId]=EARLIER('Table A'[EmployeeId]))
VAR NewLevel = IF(Score>0,'Table A'[Level]-2,'Table A'[Level]+2)
RETURN
CALCULATE(MAX('LevelTable'[Post]),'LevelTable'[ Level]=NewLevel)

Demo - Create DAX to show 2 levels up or down as per score.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[xifeng_L]

Can provide some sample data? (not the screenshort).

And what is your expected result?

 - add new column to show the new level for each employee? 

 - replace the original data with the new level?

[powerbidev123]

Expected result is to show the new level in a new column.

Below is the sample data

Level	Post
4	Management Trainee
6	Office Assistant
8	Clerk
9	Clerk
10	Senior Clerk
11	Supervisor
12	Senior Supervisor
13	Officer
14	Senior Officer
15	Assistant Manager
16	Manager
17	Senior Manger
18	Assistant Vice President
19	Vice President
20	Senior Vice President
21	Senior Vice President
22	Executive Vice President

EmployeeId	Score
1	18
2	-19
3	10
4	-1
5	0
6	20
7	10

EmployeeId	Level	Post
1	18	Assistant Vice President
2	10	Senior Clerk
3	10	Senior Clerk
4	12	Senior Supervisor
5	8	Clerk
6	11	Supervisor
7	10	Senior Clerk

[xifeng_L · Accepted solution]

Hi @powerbidev123 

You can try below calculated column expression.

New Post = 
VAR Score = CALCULATE(SUM('Table B'[Score]),'Table B'[EmployeeId]=EARLIER('Table A'[EmployeeId]))
VAR NewLevel = IF(Score>0,'Table A'[Level]-2,'Table A'[Level]+2)
RETURN
CALCULATE(MAX('LevelTable'[Post]),'LevelTable'[ Level]=NewLevel)

Demo - Create DAX to show 2 levels up or down as per score.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[powerbidev123 · Accepted solution]

Thanks very much @xifeng_L  , the formula worked.