Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Help me customize the function.
There are three conditions for reaching the target - 100%, 95%, 90%.
Depending on this counts the bonus 12%, 8%, 4%.
But if there is no testing, then minus 5% of the bonus.
I am trying to write but some of the conditions are not met. Where is the error?
Solved! Go to Solution.
Hi @Avari_ya ,
Certainly. The issue with your original formula lies in the order and overlap of conditions inside the SWITCH(TRUE(), ...) block. In DAX, SWITCH(TRUE(), ...) evaluates each condition in order, and once it finds the first condition that evaluates to TRUE, it stops evaluating the rest. In your version, some overlapping conditions caused the function to return unintended results.
Here is a revised version of the function that simplifies the logic, checks the percentage thresholds in descending order, and deducts 5% from the bonus if the test score is missing:
Bonus Score Quality =
VAR IBOBMaxBonus = 0.12
VAR ChatMaxBonus = 0.08
VAR MinBonus = 0.04
VAR Penalty = 0.05
VAR LineType = SELECTEDVALUE(PMI_Targets_Agents[Line])
VAR QualityScore = [Target Agent Volume Quality]
VAR Target100 = [Target Agent Quality]
VAR Target95 = [Target Agent Quality 0.95]
VAR Target90 = [Target Agent Quality 0.9]
VAR HasTest = NOT(ISBLANK([Target Agent T&D Test Score]))
RETURN
SWITCH(
TRUE(),
LineType = "IB" && QualityScore >= Target100, IBOBMaxBonus - IF(HasTest, 0, Penalty),
LineType = "IB" && QualityScore >= Target95, ChatMaxBonus - IF(HasTest, 0, Penalty),
LineType = "IB" && QualityScore >= Target90, MinBonus - IF(HasTest, 0, Penalty),
LineType = "IB", 0,
BLANK()
)
This version ensures that the proper bonus rate is selected based on the performance level, and it penalizes agents without test scores by reducing their bonus by 0.05. The conditions are ordered from highest to lowest target level so the logic flows correctly.
Best regards,
Hi @Avari_ya ,
We’re following up once more regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.
If you still need assistance, please let us know.
Thank you.
Hi @Avari_ya ,
Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.
If you're still facing issues, feel free to reach out.
Thank you.
Hi @Avari_ya ,
Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries.
Otherwise, feel free to reach out for further assistance.
Thank you.
Hello @Avari_ya,
Could you please try this approach:
Bonus Score Quality =
VAR MaxBonus = 0.12
VAR MidBonus = 0.08
VAR MinBonus = 0.04
VAR TestPenalty = 0.05
VAR Quality = [Target Agent Volume Quality]
VAR Target100 = [Target Agent Quality]
VAR Target95 = [Target Agent Quality 0.95]
VAR Target90 = [Target Agent Quality 0.9]
VAR HasTest = NOT ISBLANK([Target Agent T&D Test Score])
VAR RawBonus =
SWITCH(TRUE(),
Quality >= Target100, MaxBonus,
Quality >= Target95, MidBonus,
Quality >= Target90, MinBonus,
0
)
VAR FinalBonus =
IF(HasTest, RawBonus, RawBonus - TestPenalty)
RETURN
SWITCH(
SELECTEDVALUE(PMI_Targets_Agents[Line]),
"IB", FinalBonus,
0
)
Hi @Avari_ya ,
Certainly. The issue with your original formula lies in the order and overlap of conditions inside the SWITCH(TRUE(), ...) block. In DAX, SWITCH(TRUE(), ...) evaluates each condition in order, and once it finds the first condition that evaluates to TRUE, it stops evaluating the rest. In your version, some overlapping conditions caused the function to return unintended results.
Here is a revised version of the function that simplifies the logic, checks the percentage thresholds in descending order, and deducts 5% from the bonus if the test score is missing:
Bonus Score Quality =
VAR IBOBMaxBonus = 0.12
VAR ChatMaxBonus = 0.08
VAR MinBonus = 0.04
VAR Penalty = 0.05
VAR LineType = SELECTEDVALUE(PMI_Targets_Agents[Line])
VAR QualityScore = [Target Agent Volume Quality]
VAR Target100 = [Target Agent Quality]
VAR Target95 = [Target Agent Quality 0.95]
VAR Target90 = [Target Agent Quality 0.9]
VAR HasTest = NOT(ISBLANK([Target Agent T&D Test Score]))
RETURN
SWITCH(
TRUE(),
LineType = "IB" && QualityScore >= Target100, IBOBMaxBonus - IF(HasTest, 0, Penalty),
LineType = "IB" && QualityScore >= Target95, ChatMaxBonus - IF(HasTest, 0, Penalty),
LineType = "IB" && QualityScore >= Target90, MinBonus - IF(HasTest, 0, Penalty),
LineType = "IB", 0,
BLANK()
)
This version ensures that the proper bonus rate is selected based on the performance level, and it penalizes agents without test scores by reducing their bonus by 0.05. The conditions are ordered from highest to lowest target level so the logic flows correctly.
Best regards,
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |