topic Re: Help me to find the dax in DAX Commands and Tips

Help me to find the dax

sakuragihana — Sat, 04 Feb 2023 17:39:07 GMT

Hello everyone,

I got a difficult problem. I have a table :

Bonus =Number of New Student x Bonus of group

Example : Employee B has 90 new students enrolled. Bonus for B are calculated as:

Please help me to find a dax for this calculation. Thank you so much

Re: Help me to find the dax

tamerj1 — Sun, 05 Feb 2023 09:41:05 GMT

Hi sakuragihana
Please refer to attached sample file with the solution. You need to restructure the 'Bonus' table as per below screenshots.

Bonus Amount = SUMX ( 'Table', VAR NumOfStudents = 'Table'[Number of Students] VAR BonusTable = FILTER ( Bonus, Bonus[Lower Limit] <= NumOfStudents ) VAR BonusTable1 = ADDCOLUMNS ( BonusTable, "@Students", VAR TableBefore = FILTER ( BonusTable, [Bonus] < EARLIER ( [Bonus] ) ) VAR StudentsBefore = SUMX ( TableBefore, [Upper Limit] ) RETURN NumOfStudents - StudentsBefore ) VAR BonusTable2 = FILTER ( BonusTable1, [@Students] > 0 ) RETURN SUMX ( BonusTable2, MIN ( [Upper Limit], [@Students] ) * [Bonus] ) ) + 0

Re: Help me to find the dax

sakuragihana — Mon, 06 Feb 2023 04:17:36 GMT

Hi tamerj1 ,

I have a table below

Bonus for new student enroll

Condition 1	Apply for Area code MiddeArea and NorthArea
Condition 2	% Act vs Target of Campus is greater than and equal 80%
Condition 3	# of New Student PD is greater than and equal 5

Bonus =Number of New Student PD x Bonus of group

Jame belong to Campus ADV which Area Code is MiddeArea and % Act vs Target 161% and # of New Student PD is 30. All of 3 conditions are satisfied.

Please help me to apply 3 conditions for dax to calculate bonus for staff Jame

Re: Help me to find the dax

tamerj1 — Mon, 06 Feb 2023 10:23:49 GMT

sakuragihana
I knew it! That was a trap 😅

Just kidding 😁

In order to help you faster please create a sample PBIX file containing a data model with similar relationships and share it with me.

Re: Help me to find the dax

sakuragihana — Mon, 06 Feb 2023 11:00:19 GMT

Hi @ tamerj1,

Thank you for your help, I share PBIX file: https://drive.google.com/file/d/19AzUu3NehGDvZPWXJpiT-xaGKOPg4tf4/view?usp=share_link

Re: Help me to find the dax

tamerj1 — Mon, 06 Feb 2023 11:10:31 GMT

sakuragihana
Ok but how should the report look like?

Re: Help me to find the dax

tamerj1 — Mon, 06 Feb 2023 11:25:13 GMT

sakuragihana
Please refer to attached sample file amended with the solution

Bonus Amount = VAR FilteredStaff = FILTER ( Staff, RELATED ( Campus[Area Code] ) IN { "MiddeArea ", "NorthArea" } && RELATED ( Campus[# of New Student PD] ) >= 0.8 ) RETURN SUMX ( FilteredStaff, VAR NumOfStudents = Staff[# of New Student PD] VAR BonusTable = FILTER ( Bonus, Bonus[Lower Limit] <= NumOfStudents ) VAR BonusTable1 = ADDCOLUMNS ( BonusTable, "@Students", VAR TableBefore = FILTER ( BonusTable, [Bonus] < EARLIER ( [Bonus] ) ) VAR StudentsBefore = SUMX ( TableBefore, [Upper Limit] ) RETURN NumOfStudents - StudentsBefore ) VAR BonusTable2 = FILTER ( BonusTable1, [@Students] > 0 ) RETURN SUMX ( BonusTable2, MIN ( [Upper Limit], [@Students] ) * [Bonus] ) ) + 0

Re: Help me to find the dax

sakuragihana — Tue, 07 Feb 2023 01:04:40 GMT

Hi @ tamerj,

Report just need to calculate bonus for staff.

Re: Help me to find the dax

tamerj1 — Tue, 07 Feb 2023 02:20:29 GMT

sakuragihana

Have you seen the solution at the top?

Re: Help me to find the dax

sakuragihana — Tue, 07 Feb 2023 03:25:25 GMT

Yes, I saw that solution at the top.

Re: Help me to find the dax

tamerj1 — Tue, 07 Feb 2023 04:13:02 GMT

sakuragihana
So this is not what you're looking for?

Re: Help me to find the dax

sakuragihana — Tue, 07 Feb 2023 04:17:32 GMT

tamerj1,

Yes, I need a solution for apply 3 conditions for calculating bonus of staff.

The solution at the top is not enough.

Re: Help me to find the dax

tamerj1 — Tue, 07 Feb 2023 04:20:29 GMT

sakuragihana
Can you please indicate in this screenshot which row are not calculated correctly along with the clarification perhaps I can better understand your requirement.

Re: Help me to find the dax

sakuragihana — Tue, 07 Feb 2023 04:31:55 GMT

tamerj1 ,

The condition in Filter maybe wrong :

Campus[# of New student PD ] > = 5 ( not >=0.8 )

and Campus[%Act vs Target] >=0.8

Number of Student	Bonus	Total
10	50000	500,000
15	100000	1500,000
5	200000	1000,000
30		3000,000

Bonus of Jame = 3.000,000

Re: Help me to find the dax

tamerj1 — Tue, 07 Feb 2023 04:58:34 GMT

sakuragihana
Yes you are right. There was a typo mistake "MiddeArea " and the formula referenced the wrong column [# of New Student PD].

Bonus Amount = VAR FilteredStaff = FILTER ( Staff, RELATED ( Campus[Area Code] ) IN { "MiddleArea", "NorthArea" } && RELATED ( Campus[% Act vs Target] ) >= 0.8 ) RETURN SUMX ( FilteredStaff, VAR NumOfStudents = Staff[# of New Student PD] VAR BonusTable = FILTER ( Bonus, Bonus[Lower Limit] <= NumOfStudents ) VAR BonusTable1 = ADDCOLUMNS ( BonusTable, "@Students", VAR TableBefore = FILTER ( BonusTable, [Bonus] < EARLIER ( [Bonus] ) ) VAR StudentsBefore = SUMX ( TableBefore, [Upper Limit] ) RETURN NumOfStudents - StudentsBefore ) VAR BonusTable2 = FILTER ( BonusTable1, [@Students] > 0 ) RETURN SUMX ( BonusTable2, MIN ( [Upper Limit], [@Students] ) * [Bonus] ) ) + 0

Re: Help me to find the dax

sakuragihana — Tue, 07 Feb 2023 06:43:48 GMT

tamerj1 ,

Thank you so much !

Re: Help me to find the dax

sakuragihana — Fri, 10 Feb 2023 01:10:03 GMT

Hi tamerj1 ,

I have a change request from the company, bonus for staffs have many conditions more.

In table Bonus:

The bonus table is explained as follows:

1. Bonus for new student enrollment

Bonus 1 =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

Example:

Conditions apply for type NEW ENROLLMENT

1. Area code : New Area & 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

2. Bonus for new revenue

Bonus 2 = New Revenue PD x Bonus of group

Conditions apply for type NEW REVENUE

1. Area code : HCMC & SouthArea

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

3.New Revenue PD is greater than and equal 50,000,000

Similar as above, Conditions apply for type NEW REVENUE

1. Area code : New Area & VT Area

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

3.New Revenue PD is greater than and equal 30,000,000

3. Bonus for re enrollment

Bonus 3 = # Student Re Enroll x % The corresponding bonus level according to the table

Conditions apply for type Re Enrollment

1. Area code : all of area

2.% Re Enroll Student following percentage as :

Example : Staff E have 30 student re enrollment and % Re Enroll Student is 120%

Bonus 3 = 30 x 20,000 = 600,000

Total Bonus = Bonus 1+ Bonus 2+Bonus 3

All of type condition are calculating for January because in February bonus will be change the value. Can the measure apply the conditions for month ?

I make a power BI file with table data and bonus table in this link : https://drive.google.com/file/d/1N3bYrYKtcwPUffFFsJWEe_GpJee3rfT7/view?usp=share_link

Can you help me to apply all of conditions for dax to calculate the bonus of staff ?