Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Alex_nor
Frequent Visitor

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 2
daXtreme
Solution Sage
Solution Sage

 

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:

Alex_nor_0-1660899981793.png

Alex_nor_1-1660900066140.png

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.