Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone, I have 2 table below :
Table 1:
Table 2:
Condition :
1.% New Std PD and Re open vs Target >= 90%
2.If % New Student PD vs New Enrol PD Target < 80% , the bonus will be lowered by 1 level ( Note : If the New Student PD vs New Enrol PD Target is smaller than 80% and the bonus at level 1, the bonus will not be received)
3.If % New Student PD vs New Enrol PD Target < 80% and Campus code is Group 6 or Group 7, the bonus will be lowered by 1 level (Note : If the New Student PD vs New Enrol PD Target is smaller than 80% and the bonus at level 1, the bonus will not be minus 500,000)
Example 1:
Campus NCT has :
% New Std PD and Re open vs Target = 103%
% New Student PD vs New Enrol PD Target = 75%
Bonus of Campus NCT (belong Group 2 ) is level 2: 9,000,000 ( with % New Std PD and Re open vs Target =103%) . However, because % New Student PD vsa New Enrol PD Target is smaller than 80% , so the bonus will be lowered by 1 level . The bonus will be level 1 =7,000,000
Example 2:
Campus BD-BCMb has:
% New Std PD and Re open vs Target = 98%
% New Student PD vs New Enrol PD Target = 50%
Bonus of Campus BD-BCM (belong Group 6) is level 1 : 3,000,000 ( with % New Std PD and Re open vs Target =98%) . However, because % New Student PD vsa New Enrol PD Target is smaller than 80% , so the bonus will be : 2,500,000
Here is my dax :
Can everyone help me to improve this measure when the values of level of table 2 was changed which this measure do not change ?
Note : all of type condition are calculating for March because in April bonus will be change the value. Can the measure apply the conditions for month ?
Dear ,
i updated a bit in the PBIX file :
1- i created a table for bonus levels
2- Updated the measure
https://www.dropbox.com/s/m8lx0d2hrnqfp9q/Bonus%20Campus.pbix?dl=0
/// in the measure Bonus Campus_Test : replace the value if condition is true and false with the appropriate values for your case
Kindly check the updated PBIX file and let me know the approach is correct or no .
Kindly accept as a solution and a kudo is appreciated .
Thanks .
Hi @bhelou ,
The dax that you made was wrong because the new table bonus levels didn't have campus code
What is your concern? You could use SWITCH(TRUE() for the set of nested IFs but that is only syntax sugar, the query plan will be the same.
I assume you have a good reason to use LOOKUPVALUE.
Is it slow, or is it not elegant enough?
here's a "simplified" version of your measure.
Bonus Campus =
VAR NewstdPD = SUM ( 'Campus'[# of New Student PD] )
VAR TargetNewStdPD = SUM ( 'Campus'[# of New Enrol PD Target] )
VAR pct_stdvstarget = DIVIDE ( NewstdPD, TargetNewStdPD )
VAR new_std_pd_reopen_pct = SELECTEDVALUE ( 'Campus'[% New Std PD And Re Open vs Target] )
VAR new_std_pd_new_enrol_pct = SELECTEDVALUE ( 'Campus'[% New Student PD vs New Enrol PD Target] )
VAR CampusCode = SELECTEDVALUE ( Campus[Campus Code] )
VAR CurrentGroup = SELECTEDVALUE ( Bonus[Group] )
VAR K = SELECTEDVALUE ( 'Bonus'[Date] )
VAR datekey = CALCULATE ( MIN ( 'Date'[Date Key] ), 'Date'[Date Key] = VALUE ( K ) )
VAR BonusLevel =
SWITCH (TRUE (),
new_std_pd_reopen_pct >= 1.1, 3,
new_std_pd_reopen_pct >= 1.0, 2,
new_std_pd_reopen_pct >= 0.9, 1
)
VAR Bonus_campus =
LOOKUPVALUE (
'Bonus'[Bonus ],
'Bonus'[Campus], CampusCode,
'Bonus'[Level ], BonusLevel,
'Bonus'[Group], CurrentGroup
)
RETURN
SWITCH (TRUE (),
new_std_pd_new_enrol_pct < 0.7, BLANK(),
new_std_pd_new_enrol_pct >= 0.8, Bonus_campus,
BonusLevel = 3 && CurrentGroup in { 1,2 }, MAX ( 0, Bonus_campus - 6000000 ),
BonusLevel = 3 && CurrentGroup = 3, MAX ( 0, Bonus_campus - 4000000 ),
BonusLevel = 3 && CurrentGroup in { 4,5,6 }, MAX ( 0, Bonus_campus - 3000000 ),
BonusLevel = 3 && CurrentGroup = 7, MAX ( 0, Bonus_campus - 1000000 ),
BonusLevel = 1 && not CurrentGroup in { 6,7 }, 0,
CurrentGroup in { 6,7 } ,MAX ( 0, Bonus_campus - 500000 ),
MAX ( 0, Bonus_campus - 2000000 )
)
I think your LOOKUPVALUE can be replaced with a CALCULATE but I don't fully understand the logic behind your data.
Hi @lbendlin ,
I don't understand how to replace Lookupvalue with Calculate . Can you help me ? ( I attached a power BI file in above )
This should work:
Bonus Campus =
VAR NewstdPD = SUM ( 'Campus'[# of New Student PD] )
VAR TargetNewStdPD = SUM ( 'Campus'[# of New Enrol PD Target] )
VAR pct_stdvstarget = DIVIDE ( NewstdPD, TargetNewStdPD )
VAR new_std_pd_reopen_pct = SELECTEDVALUE ( 'Campus'[% New Std PD And Re Open vs Target] )
VAR new_std_pd_new_enrol_pct = SELECTEDVALUE ( 'Campus'[% New Student PD vs New Enrol PD Target] )
VAR CampusCode = SELECTEDVALUE ( Campus[Campus Code] )
VAR CurrentGroup = SELECTEDVALUE ( Bonus[Group] )
VAR K = SELECTEDVALUE ( 'Bonus'[Date] )
VAR datekey = CALCULATE ( MIN ( 'Date'[Date Key] ), 'Date'[Date Key] = VALUE ( K ) )
VAR BonusLevel =
SWITCH (TRUE (),
new_std_pd_reopen_pct >= 1.1, 3,
new_std_pd_reopen_pct >= 1.0, 2,
new_std_pd_reopen_pct >= 0.9, 1
)
VAR Bonus_campus = CALCULATE(sum(Bonus[Bonus ]),Bonus[Level ]=BonusLevel)
RETURN
SWITCH (TRUE (),
new_std_pd_new_enrol_pct < 0.7, BLANK(),
new_std_pd_new_enrol_pct >= 0.8, Bonus_campus,
BonusLevel = 3 && CurrentGroup in { 1,2 }, MAX ( 0, Bonus_campus - 6000000 ),
BonusLevel = 3 && CurrentGroup = 3, MAX ( 0, Bonus_campus - 4000000 ),
BonusLevel = 3 && CurrentGroup in { 4,5,6 }, MAX ( 0, Bonus_campus - 3000000 ),
BonusLevel = 3 && CurrentGroup = 7, MAX ( 0, Bonus_campus - 1000000 ),
BonusLevel = 1 && not CurrentGroup in { 6,7 }, 0,
CurrentGroup in { 6,7 } ,MAX ( 0, Bonus_campus - 500000 ),
MAX ( 0, Bonus_campus - 2000000 )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |