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
Try this:
KPI = CALCULATE(count(table[Student_id], table[School_Year] = 2019 || table[School_Year] = 2020 || table[School_Year] = 2021)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