Forum Discussion

Chelly's avatar
Chelly
Frequent Visitor
2 years ago
Solved

Modifying DAX Formula for Excluding

Hello, I'm working with a DAX formula that calculates the number of students in 'A' schools based on specific criteria: CALCULATE(count(table[Student_id]) School_Year is limited to 2019, 2020, or...
  • ray_aramburo's avatar
    2 years ago

    Understood. I would do then the following in Power Query: 

    Create a conditional column that evaluates against the year 2022 or 2023, if the row has year 2022 or 2023 then 1. 

    Then duplicate that query and delete all columns except the student id and the flag you just created. Now, group the table by adding up the values on the flag column. What you will have now is a single student ID with a value. Create a new conditional column that evaluates the sum of the values. If 0 then "Include" else "Exclude". Now, merge both queries through Student ID and use the Include flag to filter the students you need 🙂 

  • AlexisOlson's avatar
    2 years ago

    In DAX, you can do something like this (untested):

    kpi =
    VAR _Summary_ =
        SUMMARIZE (
            table[Student_id],
            table[School_Year]
        )
    VAR _RecentStudents_ =
        DISTINCT (
            SELECTCOLUMNS (
                FILTER (
                    _Summary_,
                    table[School_Year] IN { "2022", "2023" }
                ),
                "Student_id", table[Student_id]
            )
        )
    VAR _Result =
        CALCULATE (
            DISTINCTCOUNT ( table[Student_id] ),
            NOT table[Student_id] IN _RecentStudents_
        )
    RETURN
        _Result