topic Help me customize the function. in DAX Commands and Tips

Help me customize the function.

Avari_ya — Sun, 13 Apr 2025 11:03:41 GMT

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?

Bonus Score Quality =

VAR IBOBMaxBonus = 0.12

VAR ChatMaxBonus = 0.08

VAR MinBonus = 0.04

VAR Neutral = 0.07

VAR Neutral2 = 0.03

VAR calc =

SWITCH(

SELECTEDVALUE(PMI_Targets_Agents[Line]),

"IB", SWITCH(TRUE(),

[Target Agent Volume Quality] < [Target Agent Quality 0.9], 0,

[Target Agent Volume Quality] >= [Target Agent Quality] && NOT(ISBLANK([Target Agent T&D Test Score])), IBOBMaxBonus,

[Target Agent Volume Quality] >= [Target Agent Quality 0.9] && NOT(ISBLANK([Target Agent T&D Test Score])), MinBonus,

[Target Agent Volume Quality] >= [Target Agent Quality 0.95] && NOT(ISBLANK([Target Agent T&D Test Score])), ChatMaxBonus,

[Target Agent Volume Quality] >= [Target Agent Quality 0.9] && NOT(ISBLANK([Target Agent T&D Test Score])), MinBonus,

[Target Agent Volume Quality] >= [Target Agent Quality] && ISBLANK([Target Agent T&D Test Score]), Neutral,

[Target Agent Volume Quality] >= [Target Agent Quality 0.95] && ISBLANK([Target Agent T&D Test Score]), Neutral2

Re: Help me customize the function.

DataNinja777 — Sun, 13 Apr 2025 14:45:42 GMT

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,

Re: Help me customize the function.

Sahir_Maharaj — Mon, 14 Apr 2025 19:24:29 GMT

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 )

Re: Help me customize the function.

v-veshwara-msft — Sun, 04 May 2025 15:27:51 GMT

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.

Re: Help me customize the function.

v-veshwara-msft — Mon, 12 May 2025 04:41:46 GMT

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.

Re: Help me customize the function.

v-veshwara-msft — Thu, 15 May 2025 14:21:16 GMT

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.