Forum Discussion

rebam12's avatar
rebam12
Icon for Helper I rankHelper I
2 years 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
    2 years 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
Icon for Helper I rankHelper I
2 years 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
Icon for Solution Supplier rankSolution Supplier
2 years ago

the easiest option is to model your data better.

I.e subjects as rows not columns