Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |