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

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 ?

rajendraongole1's avatar
rajendraongole1
Super User
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.