Filter users that did not attended certain courses

[ImPalaBI]

I have a table with users and courses, I want to filter the users that did not attended certain courses (so I can send them an email).

My data looks like this:

STUDENT	COURSES TAKEN
A	Course 1
A	Course 2
B	Course 1
B	Course 2
B	Course 3
C	Course 1

How can I get a list of students that attended Course 1 and Course 2, but did not attended Course 3? In my example table the result will be only Student A.

Also, it would be possible to have a slicer or filter where I can select different courses and get a filtered table? For example a list of students that attended Course 1, but not Courses 2 or 3.  In my example table the result will be only Student C.

Thanks in advance

[v-kelly-msft · Accepted solution]

Hi @ImPalaBI ,

Create a measure as below:

Measure = 
var _table=CALCULATETABLE(VALUES('Table'[COURSES TAKEN]),FILTER(ALL('Table'),'Table'[STUDENT]=MAX('Table'[STUDENT])))
Return
IF("Course 1&Course 2"=CONCATENATEX(_table,[COURSES TAKEN],"&"),MAX('Table'[STUDENT]),BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @ImPalaBI - Can you not use Slicer Visual and under selection Control turn off the Multi-select with CTRL?

Hope this helps.

Cheers,

-Namish B

[ImPalaBI]

Thanks for the reply,

But this won´t help, if a student has taken all Courses, it will appear on all combinations of the Slicer Visual. In the example image you sent, only Student A should appear.