Forum Discussion
Modifying DAX Formula for Excluding
- 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 🙂
- 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
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 🙂