Need help for improving my dax

[sakuragihana]

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 % 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 :

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 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

        IF(new_std_pd_new_enrol_pct < 0.7,

            BLANK(),

            IF( new_std_pd_new_enrol_pct >= 0.8,

                Bonus_campus,

                IF (

                    new_std_pd_new_enrol_pct < 0.8 && BonusLevel = 3 && CurrentGroup = 1

                    || new_std_pd_new_enrol_pct < 0.8 && BonusLevel = 3 && CurrentGroup = 2,

                    MAX ( 0, Bonus_campus - 6000000 ),

                    IF (

                        new_std_pd_new_enrol_pct < 0.8

                        && BonusLevel = 3

                        && CurrentGroup = 3,

                         MAX ( 0, Bonus_campus - 4000000 ),

                        IF (

                            new_std_pd_new_enrol_pct < 0.8

                            && BonusLevel = 3

                            && CurrentGroup = 4,

                            MAX ( 0, Bonus_campus - 3000000 ),

                            IF (

                                new_std_pd_new_enrol_pct < 0.8

                                && BonusLevel = 3

                                && CurrentGroup = 5,

                                MAX ( 0, Bonus_campus - 3000000 ),

                                IF (

                                    new_std_pd_new_enrol_pct < 0.8

                                    && BonusLevel = 3

                                    && CurrentGroup = 6,

                                    MAX ( 0, Bonus_campus - 3000000 ),

                                    IF (

                                        new_std_pd_new_enrol_pct < 0.8

                                        && BonusLevel = 3

                                        && CurrentGroup = 7,

                                        MAX ( 0, Bonus_campus - 1000000 ),

                                        IF (

                                            new_std_pd_new_enrol_pct < 0.8

                                            && BonusLevel = 1

                                            && CurrentGroup <> 6

                                            && CurrentGroup <> 7,

                                            0,

                                            IF (

                                            CurrentGroup = 6 || CurrentGroup = 7,

                                            MAX ( 0, Bonus_campus - 500000 ),

                                            MAX ( 0, Bonus_campus - 2000000 )

                                            )

                                        )

                                    )

                                )

                            )

                        )

                    )

                )

            )

        )

 

I make a power BI file in this link : https://drive.google.com/file/d/18DuaNV8Fh63Y2Svxy-6-z9pToqCtyMf8/view?usp=share_link 

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 ?

[bhelou]

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 . 

[Utatakakawasaki]

Hi @bhelou , 

The dax that you made was wrong because the new table bonus levels didn't have campus code

[lbendlin]

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?

[Utatakakawasaki]

Hi @lbendlin ,

How can I apply a measure with a datekey in LOOKUPVALUE ? 

[lbendlin]

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.

[Utatakakawasaki]

Hi @lbendlin ,

I don't understand how to replace Lookupvalue with Calculate . Can you help me ? ( I attached a power BI file in above ) 

[lbendlin]

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 )
    )