Count dax formula

[Alex_nor]

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	History
John Rambo	Algebra
John Rambo	Trigonometry
John Rambo	History
Mary Poppins	Algebra
Mary Poppins	History

I would like to create a measure to count the courses but if a student has done the algebra and trigonometry courses will count only as one. Then the result should be 6 courses in total and for exemple two completed courses for Alex Salazar.

Do you have some suggestion?

Thank you.

[daXtreme]

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.

[Alex_nor]

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.