Forum Discussion
sakuragihana
Helper IV
3 years agoNeed help for improving my dax
Hello everyone, I have 2 table below : Table 1: Table 2: Table 2 is explained below : Condition : 1.% New Std PD and Re open vs Target >= 90% 2.If % Ne...
lbendlin
Super User
3 years agohere'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.
Utatakakawasaki
3 years agoFrequent Visitor
Hi lbendlin ,
I don't understand how to replace Lookupvalue with Calculate . Can you help me ? ( I attached a power BI file in above )
- lbendlin3 years ago
Super User
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 ) )