Power BI DAX Query

Question

I am designing a DAX to calculate, there are two categories of subjects, one is compulsory and other is normal. My DAX needs to check, if the marks are within the range for allocation of Satutory marks.The Statutory Marks are fixed at 5.

As an example if I have 5 Subjects, with marks 13 for Compulsory and 16,15,16,17,25 for the normal ones.

The DAX should allocate 4 marks to the compulsory and bring it to 17[Passing Marks] and remaining 2 should go to 16/16 so that they are pass. All other would remain as they are. I have compiled a DAX, not sure were I am wrong.

MehrajCalc_Measure = 
VAR Marks_obtained = MAX(Test_Analytics[TOTAL_MarksObtained_FAS])  // Considering the maximum marks obtained within the current context
VAR Min_Marks = MAX(ExamTbl_MIN_PassMarks[Diet_categoryInfo.Diet_minMarks])
VAR StatueMarks = 5

VAR Compulsory_Marks = 
    CALCULATE (
        SUM ( Test_Analytics[TOTAL_MarksObtained_FAS] ),
        Test_Analytics[examScore.Diet_subjectType] = "compulsory" &&
        Marks_obtained >= 12 && Marks_obtained < Min_Marks
    )

VAR NormalMarks =     
    CALCULATE (
        SUM ( Test_Analytics[TOTAL_MarksObtained_FAS] ),
        Test_Analytics[examScore.Diet_subjectType] = "normal" &&
        Marks_obtained >= 12 && Marks_obtained < Min_Marks
    )

VAR Remaining_Statue_Marks = MAX(StatueMarks - Compulsory_Marks, 0)

VAR Remaining_Statue_Marks_Normal = IF(Remaining_Statue_Marks > 0, MAX(0, Remaining_Statue_Marks - NormalMarks), Remaining_Statue_Marks)

VAR StatueMarksToAdd = 
    IF(
        Remaining_Statue_Marks_Normal > 0,
        IF(Min_Marks - Marks_obtained > Remaining_Statue_Marks_Normal, Remaining_Statue_Marks_Normal, Min_Marks - Marks_obtained),
        0
    )

RETURN 
    IF(
        Marks_obtained >= 12 && Marks_obtained < Min_Marks,
        Marks_obtained +  StatueMarksToAdd,
        Marks_obtained
    )

Answer 1

This should work for you, logically

MehrajCalc_Measure =
VAR Marks_obtained = MAX(Test_Analytics[TOTAL_MarksObtained_FAS])
VAR Min_Marks = MAX(ExamTbl_MIN_PassMarks[Diet_categoryInfo.Diet_minMarks])
VAR StatueMarks = 5

VAR Compulsory_Marks =
    CALCULATE (
        SUMX(
            FILTER(Test_Analytics, Test_Analytics[examScore.Diet_subjectType] = "compulsory"),
            IF(Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 && Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks, Test_Analytics[TOTAL_MarksObtained_FAS], 0)
        ),
        ALLEXCEPT(Test_Analytics, Test_Analytics[examScore.Diet_subjectID])
    )

VAR Normal_Marks =
    CALCULATE (
        SUMX(
            FILTER(Test_Analytics, Test_Analytics[examScore.Diet_subjectType] = "normal"),
            IF(Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 && Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks, Test_Analytics[TOTAL_MarksObtained_FAS], 0)
        ),
        ALLEXCEPT(Test_Analytics, Test_Analytics[examScore.Diet_subjectID])
    )

VAR Remaining_Statue_Marks = MAX(StatueMarks - Compulsory_Marks, 0)

VAR Remaining_Marks_To_Distribute =
    VAR Normal_Marks_Needs_Boost =
        CALCULATE(
            SUMX(
                FILTER(Test_Analytics, Test_Analytics[examScore.Diet_subjectType] = "normal"),
                IF(Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 && Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks - Remaining_Statue_Marks, Min_Marks - Test_Analytics[TOTAL_MarksObtained_FAS], 0)
            ),
            ALLEXCEPT(Test_Analytics, Test_Analytics[examScore.Diet_subjectID])
        )
    RETURN
        IF(Normal_Marks_Needs_Boost > Remaining_Statue_Marks, Remaining_Statue_Marks, Normal_Marks_Needs_Boost)

VAR Marks_To_Add_To_Current_Row =
    IF(
        Test_Analytics[examScore.Diet_subjectType] = "compulsory" &&
            Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 &&
            Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks,
        MIN(Remaining_Statue_Marks, Min_Marks - Test_Analytics[TOTAL_MarksObtained_FAS]),
        IF(
            Test_Analytics[examScore.Diet_subjectType] = "normal" &&
                Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 &&
                Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks,
            MIN(Remaining_Marks_To_Distribute, Min_Marks - Test_Analytics[TOTAL_MarksObtained_FAS]),
            0
        )
    )

RETURN
    Test_Analytics[TOTAL_MarksObtained_FAS] + Marks_To_Add_To_Current_Row

I have replaced, SUMwith SUMX, made use of ALLEXCEPT and introduced a new variable named Remaining_Marks_To_Distribute. I also hope that you will be able to decipher the changes.

One Advise, if Mehraj is a name of a person, the norm of software solution is never to use individual names as part of variable names.

DAX MODIFIED AFTER SAMPLE DATA

MehrajCalc_Measure =
VAR Marks_obtained = MAX(Test_Analytics[TOTAL_MarksObtained_FAS])
VAR Min_Marks = MAX(ExamTbl_MIN_PassMarks[Diet_categoryInfo.Diet_minMarks])
VAR StatueMarks = 5

VAR Compulsory_Marks =
    CALCULATE (
        SUMX(
            FILTER(Test_Analytics, Test_Analytics[examScore.Diet_subjectType] = "compulsory"),
            IF(Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 && Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks, Test_Analytics[TOTAL_MarksObtained_FAS], 0)
        ),
        ALLEXCEPT(Test_Analytics, Test_Analytics[examScore.Diet_subjectID])
    )

VAR Normal_Marks =
    CALCULATE (
        SUMX(
            FILTER(Test_Analytics, Test_Analytics[examScore.Diet_subjectType] = "normal"),
            Test_Analytics[TOTAL_MarksObtained_FAS]
        ),
        ALLEXCEPT(Test_Analytics, Test_Analytics[examScore.Diet_subjectID])
    )

VAR Remaining_Statue_Marks = MAX(StatueMarks - Compulsory_Marks, 0)

VAR Remaining_Marks_To_Distribute =
    VAR Normal_Marks_Needs_Boost =
        CALCULATE(
            SUMX(
                FILTER(Test_Analytics, Test_Analytics[examScore.Diet_subjectType] = "normal"),
                IF(Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks - Remaining_Statue_Marks, Min_Marks - Test_Analytics[TOTAL_MarksObtained_FAS], 0)
            ),
            ALLEXCEPT(Test_Analytics, Test_Analytics[examScore.Diet_subjectID])
        )
    RETURN
        IF(Normal_Marks_Needs_Boost > Remaining_Statue_Marks, Remaining_Statue_Marks, Normal_Marks_Needs_Boost)

VAR Marks_To_Add_To_Current_Row =
    IF(
        Test_Analytics[examScore.Diet_subjectType] = "compulsory" &&
            Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 &&
            Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks,
        MIN(Remaining_Statue_Marks, Min_Marks - Test_Analytics[TOTAL_MarksObtained_FAS]),
        IF(
            Test_Analytics[examScore.Diet_subjectType] = "normal" &&
                Test_Analytics[TOTAL_MarksObtained_FAS] >= 12 &&
                Test_Analytics[TOTAL_MarksObtained_FAS] < Min_Marks,
            MIN(Remaining_Marks_To_Distribute, Min_Marks - Test_Analytics[TOTAL_MarksObtained_FAS]),
            0
        )
    )

RETURN
    Test_Analytics[TOTAL_MarksObtained_FAS] + Marks_To_Add_To_Current_Row

Compulsory Subject (14 Marks): Marks obtained = 14 (original) + 3 (from the statue) = 17 Marks

Normal Subjects: 15 Marks: Marks obtained = 15 (original) + 2 (from the statue) = 17 Marks 16 Marks: Marks obtained = 16 (original) + 1 (from the statue) = 17 Marks 16 Marks: Marks obtained = 16 (original) + 1 (from the statue) = 17 Marks 17 Marks: Marks obtained = 17 (original) (already meets the passing marks)