Modifying DAX Formula for Excluding

[Chelly]

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

Now, I'm looking to make a modification that would exclude cases where the 'Student_id' is associated with the years 2022 or 2023. I've attempted using
NOT table[Student_id]) in { "2022 ","2023"}
but it hasn't produced the desired result. Can you help me achieve this exclusion in my calculation?

kpi= CALCULATE(count(table[Student_id])
,table[School_Year] IN {"2019","2020","2021"},
 YEAR(table[date])= YEAR(NOW())
)

 

[ray_aramburo · Accepted solution]

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 · Accepted solution]

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

[ray_aramburo · Accepted solution]

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 🙂 

[ray_aramburo]

Try this: 

KPI = CALCULATE(count(table[Student_id], table[School_Year] = 2019 || table[School_Year] = 2020 || table[School_Year] = 2021)

[Chelly]

The current DAX formula falls short of my expectations because 'Student_id' can have both 2019 and 2023 records, and the formula only excludes 2023 and 2022. However, I require 'Student_id' to be entirely excluded,  if they have records from 2023 or 2022.
thanks