Need help to apply a condition for dax

sakuragihana — Wed, 15 Feb 2023 08:32:24 GMT

Hello everyone, I have a report to calculate bonus of staff as :

Bonus for new student enrollment

Bonus =Number of New Student PD x Bonus of group

-Apply for condition type NEW ENROLLMENT :

1. Area code : HCMC, SouthArea

2.% Act vs Target of Campus is greater than and equal 80%

3.# of New Student PD is greater than and equal 5

Another conditions apply for type NEW ENROLLMENT

1. Campus code : NewArea, VT Area

2.% Act vs Target of Campus is greater than and equal 75%

3.# of New Student PD is greater than and equal 3

Dax for calculating this report:

------------------------------------------------------------------------------------------------------------------------------------

Bonus_new_enroll =

VAR dateselect = SELECTEDVALUE('ASA Bonus Starget'[Date key])

VAR datekey = CALCULATE(MIN('Calendar'[Date Key]), 'Calendar'[Date Key]=VALUE(dateselect))

VAR area = SELECTEDVALUE('Campus'[Area Code])

VAR new_students = 'ASA Overview PD'[# of New Student PD]

var Pct_target = 'ASA Overview PD'[% of New Student PD vs Campus Target]

VAR filtered_areas =

CALCULATETABLE(

DISTINCT('Campus'[Area Code]),

OR (

'Campus'[Area Code] IN { "HCMC", "SouthArea"} && Pct_target >0.8,

'Campus'[Area Code] IN {"NewArea", "VT Area" } && Pct_target >0.75

)

VAR bonus_1_t =

FILTER (

'ASA Bonus Starget',

'ASA Bonus Starget'[Min] <= new_students

&& 'ASA Bonus Starget'[Area Code Campus] = area

&& 'ASA BOnus Starget'[Type] = "NEW ENROLLMENT"

&& VALUE('ASA Bonus Starget'[Date key]) = datekey

)

VAR bonuses_calc_1 =

SUMX (

ADDCOLUMNS (

bonus_1_t,

"@bonus_calc1",

VAR current_bonus = [Bonus 1]

VAR prev_limit_running = SUMX ( FILTER ( bonus_1_t, [Bonus 1] < current_bonus ), [Max] )

VAR students_diff = new_students - prev_limit_running

VAR students = IF ( students_diff < 0, BLANK (), students_diff )

RETURN

MIN ( [Max], students ) * [Bonus 1]

[@bonus_calc1]

)

VAR bonus_new_enrollment = IF ( area in filtered_areas, bonuses_calc_1 )

Return bonus_new_enrollment

---------------------------------------------------------------------------------------------------------------------------------------

I use this dax to calculate bonus for staff. But I have a problem , the condition [% Act vs Target of Campus] is greater than and equal 80% is applied for a whole of campus not for everyone belong of campus.

Example :

The dax just calculate for staff who is [% Act vs Target] 107% and not calculate for 2 staffs . I need the dax apply for whole campus which [% Act vs Target] is greater than 80% (as Campus code ADV is 161%) , not for [% Act vs Target] of staff is greater than 80%

Similarly, the condition [% Act vs Target of Campus] is greater than and equal 75% is applied for a whole of campus.

I post plib in this link : https://app.powerbi.com/groups/me/reports/ac39c2eb-d6d9-46d6-9f87-3fe72272633e?ctid=d1005fc5-bff9-42...

Can everyone help me to fix that dax ?

Re: Need help to apply a condition for dax

Anonymous — Wed, 15 Feb 2023 07:32:36 GMT

Hi sakuragihana,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Notice: please exclude sensitive data before sharing.

Regards,

Xiaoxin Sheng

Re: Need help to apply a condition for dax

sakuragihana — Wed, 15 Feb 2023 08:35:58 GMT

Hi Anonymous ,

Please use this link : https://app.powerbi.com/groups/me/reports/ac39c2eb-d6d9-46d6-9f87-3fe72272633e?ctid=d1005fc5-bff9-42d5-81e5-1f3fcb089799&pbi_source=linkShare

Dax in sheet Bonus Sale Performance . Although, the dax of bonus_new_revenue and the dax of bonus_re_enroll is the same , I need the dax apply for all of staff who satisfy the conditions . Can you help me to fix that dax ?

Re: Need help to apply a condition for dax

Anonymous — Wed, 22 Feb 2023 07:29:54 GMT

Hi sakuragihana,

In fact, the shared report link does not help, I can't view these shared contents.

Perhaps you can create some dummy data with similar structure and Dax expressions in a sample pbix file and share here. They will help us test to coding formula and troubleshoot on your scenario.

Regards,

Xiaoxin Sheng

Re: Need help to apply a condition for dax

sakuragihana — Wed, 22 Feb 2023 07:33:21 GMT

Hi Anonymous ,

Can you give me your email ? So I will share a report link for you

topic Re: Need help to apply a condition for dax in DAX Commands and Tips

Need help to apply a condition for dax

Re: Need help to apply a condition for dax

Re: Need help to apply a condition for dax

Re: Need help to apply a condition for dax

Re: Need help to apply a condition for dax