Forum Discussion

rebam12's avatar
rebam12
Helper I
1 year ago
Solved

dax query

i have several different columns like subject columns like this 

 

 

username  email               english    maths     scienece       computer     

abc      [email protected]          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 usernameemail
8/21/2024 abc[email protected]
7/21/2024 abc[email protected]
7/11/2024 abc[email protected]

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

Data Protection
Need Help
Show and Tell
Tips and Tricks
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      rebam12
      Helper I
      1 year ago

      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 ?

      • ajohnso2's avatar
        ajohnso2
        Solution Supplier
        1 year ago

        the easiest option is to model your data better.

        I.e subjects as rows not columns