Forum Discussion
Count dax formula
Hello all, I have a table that is called “Education” and it looks like this: Students Courses Alex Salazar Algebra Alex Salazar Trigonometry Alex Salazar Histor...
define table T =
SELECTCOLUMNS(
{
("Alex Salazar", "Algebra"),
("Alex Salazar", "Trigonometry"),
("Alex Salazar", "History"),
("Alex Salazar", "English"),
("Alex Salazar", "Physics"),
("John Rambo", "Algebra"),
("John Rambo", "Trigonometry"),
("John Rambo", "History"),
("Mary Poppins", "Algebra"),
("Mary Poppins", "History")
},
"Student", [Value1],
"Course", [Value2]
)
measure T[# Courses] =
// <------------- HERE'S YOUR MEASURE -------------->
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
DISTINCT( T[Course] ),
"@Id",
IF( T[Course] in { "trigonometry", "algebra" },
// In the set of original courses none should
// start with ~. This will obviously be true.
"~DummyId",
T[Course]
)
)
)
)
EVALUATE
var CourseFilter = {"algebra", /* "english", */ "trygonometry"}
var StudentFilter = {"Alex Salazar"}
return
CALCULATETABLE(
ADDCOLUMNS(
VALUES( T[Student] ),
"# Courses",
[# Courses]
),
treatas(
CourseFilter,
T[Course]
),
TREATAS(
StudentFilter,
T[Student]
)
)This DAX query proves the measure works as expected.
Thank you for your answer. This measure is working good but i forgot to write that I would like to count/summarize the total of completed courses and again counting "algebra" and "trigonometry" as 1 per student.
With the actual measure I got this:
As you see, when all the students are selected is showing the number of existing courses and not the sum of all courses that have been completed.