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 agoWhat 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?
- Utatakakawasaki3 years agoFrequent Visitor
- lbendlin3 years ago
Super User
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.
- Utatakakawasaki3 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 )