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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
powerbidev123
Solution Sage
Solution Sage

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

I have 2 tables with following details:

 

Table A: EmpId,Level,Name

 

powerbidev123_1-1717142753744.png

 

 

Table B:EmpId,score

 

powerbidev123_4-1717143170095.png

 

 

 

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

powerbidev123_2-1717142919353.png

 

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 

 

2 ACCEPTED SOLUTIONS

Hi @powerbidev123 

 

You can try below calculated column expression.

 

xifeng_L_0-1717148883843.png

 

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~

 

View solution in original post

Thanks very much @xifeng_L  , the formula worked. 

View solution in original post

4 REPLIES 4
xifeng_L
Super User
Super User

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?

 

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

 

Below is the sample data

 

 LevelPost
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

 

 

EmployeeIdScore
118
2-19
310
4-1
50
620
710

 

EmployeeIdLevelPost
118 Assistant Vice President
210 Senior Clerk
310 Senior Clerk
412 Senior Supervisor
58 Clerk
611 Supervisor
710 Senior Clerk

Hi @powerbidev123 

 

You can try below calculated column expression.

 

xifeng_L_0-1717148883843.png

 

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~

 

Thanks very much @xifeng_L  , the formula worked. 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.