Slicer selection must give union result in the grid

[nsamuel]

I have a simple requirement. I have a slicer of selecting courses. The slicer is related to grid. Selecting the course shows the students in the grid. But the requirement is if i select more than two courses then i must get the student names who have enrolled in those courses. if student A is enrolled in course X and Y, and student B is enrolled in course X. then when doing multi select of course X and Y i must only get the name of student A and not student B because he is only enrolled in course Y. Selecting courses C1 and C3 must only give me the name Noel. I have tried selected value but unable to get desired result

[Thim]

As long as it is only the Course filter i would do it like this.

I would make 3 new meassures (They can be put into one, but i like keeping them seperated)

one meassure that shows how many of the filtered courses each student has choosen.

one meassure that shows how many courses you have choosen/filtered.

one meassure that we will use as a filter showing only students that has all filtered courses.

First make this meassure that will show how many of the selected courses each student has signed up for.

Count Courses per Student = COUNT('Table'[Courses])

Then make this meassure that will show how many courses you have selected/Filtered.

Courses Selected = CALCULATE(DISTINCTCOUNT('Table'[Courses]);

ALLEXCEPT('Table';'Table'[Courses])

)

Finally you can use these calculation to make a new meassure you can use as a filter.

Filter = IF([Count Courses per Student]=[Courses Selected];"True";"False")

Now place the last Meassure as a filter on the matrix or page or report Depending on how deep you want the filter to act.

And that should be it.

Hope this will help. 🙂

[OwenAuger · Accepted solution]

Hi @nsamuel 

You can create a measure to use as a visual level filter on the table.

Here is one example of such a measure that should work in your case:

Student has all selected courses = 
VAR CoursesAllselected =
    ALLSELECTED ( YourTable[Courses] )
VAR CoursesCurrentStudent = 
    CALCULATETABLE ( VALUES ( YourTable[Courses] ), ALLEXCEPT ( YourTable, YourTable[Students] ) )
RETURN
    INT ( ISEMPTY ( EXCEPT ( CoursesAllselected, CoursesCurrentStudent ) ) )

Set this measure as a visual level filter for the table, filtered to value 1.

This particular measure compares the Courses for the student(s) in the current filter context (CoursesCurrentStudent) with the overall Courses selected (CoursesAllselected). Then on the last line it tests whether CoursesCurrentStudent includes all courses in CoursesAllselected, using ISEMPTY( EXCEPT (.... ) ). The logical result is converted to an integer, which will be 1 if true.

Sample pbix here.

Regards,

Owen

[nsamuel]

Thank you so much!!!! Also so nice of you for sharing the PBIX file for better understanding!!!!!