dax query

[rebam12]

i have several different columns like subject columns like this 

username  email               english    maths     scienece       computer     

abc      abc@gmail.com          0-25%      76-100%                  0-25%

like engish is 0-25% 

maths is 76-100% 

sceince is 0

and computer is 0-25%

and another table 

date	username	email
8/21/2024	abc	abc@gmail.com
7/21/2024	abc	abc@gmail.com
7/11/2024	abc	abc@gmail.com

so i want 

1. Total hours assigned to the student according to how many sections the have completed up to their last login date

so here last login date is 8/21/2024

means i want to count columns like abc person compleete 3 subjects except sciecnce/

so i want 3 figure here ..

[rajendraongole1]

Hi @rebam12 -Create a measure to get the last login date for each student

LastLoginDate =

CALCULATE(

    MAX(LoginHistory[date]),

    FILTER(

        LoginHistory,

        LoginHistory[username] = MAX(StudentSubjects[username])

    )

)

Create calculated column to count completed subjects: is more than 0%

CompletedSubjects =

VAR EnglishCompleted =

    IF (

        NOT(ISBLANK(StudentSubjects[english])) &&

        StudentSubjects[english] <> "0",

        1,

        0

    )

VAR MathsCompleted =

    IF (

        NOT(ISBLANK(StudentSubjects[maths])) &&

        StudentSubjects[maths] <> "0",

        1,

        0

    )

VAR ScienceCompleted =

    IF (

        NOT(ISBLANK(StudentSubjects[science])) &&

        StudentSubjects[science] <> "0",

        1,

        0

    )

VAR ComputerCompleted =

    IF (

        NOT(ISBLANK(StudentSubjects[computer])) &&

        StudentSubjects[computer] <> "0",

        1,

        0

    )

RETURN

    EnglishCompleted + MathsCompleted + ScienceCompleted + ComputerCompleted

Hope this helps.

[rebam12]

is this necessary to create var for each subjects ? what if there is 15 subjects then bit difficult to create 15 variables .... is there any other alternative for this ?

[rajendraongole1 · Accepted solution]

Hello @rebam12 - you can try the below logic

CompletedSubjects1 =

VAR LastLogin =

    CALCULATE(

        MAX(LoginHistory[date]),

        FILTER(

            LoginHistory,

            LoginHistory[username] = MAX(StudentSubjects[username])

        )

    )

VAR CompletedCount =

    SUMX(

        {

            IF (

                CONTAINSSTRING(StudentSubjects[english], "0-25%") ||

                CONTAINSSTRING(StudentSubjects[english], "26-50%") ||

                CONTAINSSTRING(StudentSubjects[english], "51-75%") ||

                CONTAINSSTRING(StudentSubjects[english], "76-100%"),

                1,

                0

            ),

            IF (

                CONTAINSSTRING(StudentSubjects[maths], "0-25%") ||

                CONTAINSSTRING(StudentSubjects[maths], "26-50%") ||

                CONTAINSSTRING(StudentSubjects[maths], "51-75%") ||

                CONTAINSSTRING(StudentSubjects[maths], "76-100%"),

                1,

                0

            ),

            IF (

                CONTAINSSTRING(StudentSubjects[science], "0-25%") ||

                CONTAINSSTRING(StudentSubjects[science], "26-50%") ||

                CONTAINSSTRING(StudentSubjects[science], "51-75%") ||

                CONTAINSSTRING(StudentSubjects[science], "76-100%"),

                1,

                0

            ),

            IF (

                CONTAINSSTRING(StudentSubjects[computer], "0-25%") ||

                CONTAINSSTRING(StudentSubjects[computer], "26-50%") ||

                CONTAINSSTRING(StudentSubjects[computer], "51-75%") ||

                CONTAINSSTRING(StudentSubjects[computer], "76-100%"),

                1,

                0

            )

        },

        [Value]

    )

RETURN

    CompletedCount

Hope this helps.

[ajohnso2]

the easiest option is to model your data better.

I.e subjects as rows not columns