Forum Discussion
Chelly
2 years agoFrequent Visitor
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...
- 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
AlexisOlson
2 years agoSuper User
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