Forum Discussion

Alex_nor's avatar
Alex_nor
Frequent Visitor
4 years ago

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

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.

2 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

     

    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's avatar
      Alex_nor
      Frequent Visitor
      4 years ago

      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.