Using Include and Exclude Slicers to Generate List

[amaurey]

I have ~10,000 students who have the option of registering for 300 sessions. I would like to be able to use slicer 1 to select session numbers (examples: ME101, ME102, ME103) and generate a list of students who have taken those selected sessions. Slicer 2 would be used to select one session (not picked in slicer 1) and exclude the students who have attended that session from the list.

I'm having trouble creating the exclude slicer. I've tried a few options suggested in the forums, but can't seem to get it to work for my situation. 

[amitchandak]

@amaurey , You need have two session tables, one connected Sessions and one disconnected SessionsInd

You need these measures

Included Students =
CALCULATE(
DISTINCTCOUNT(StudentsSessions[StudentID])
)

Excluded Students =
CALCULATE(
DISTINCTCOUNT(StudentsSessions[StudentID]),
ALL(Sessions),
Filter(StudentsSessions, NOT StudentsSessions[SessionID] IN VALUES(SessionsInd[SessionID])
))

Final Measure =
Countx(Values(StudentsSessions[StudentID]), if( isblank([Excluded Students]) && not(isblank([Included Students])) , [StudentID], blank()))

Final measure, you need to use in visual

Power BI Exclude selected Slicer value, Independent Table: https://youtu.be/lOEW-YUrAbE

[amaurey]

Thank you for your reply. This is very similar to other solutions I've attempted (I've actually previously watched the same video before posting here) and I'm still stuck. 

Can you clarify the following:

1. For the slicers, should they be using fields from the same data table or should one slicer be using a field from the disconnected table?
2. Where do I place the Included Students and Excluded Student measures?
3. The Final Measure is not a filter, but should be used as a column in the final table?

Thank you!

[Anonymous]

Hi @amaurey ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create another table

Table 2 = 'Table'

Create a measure

Filter = 
IF(
    SELECTEDVALUE('Table 2'[Session Number]) IN VALUES('Table'[Session Number]),
    0,
    1
)

Apply the measure to the exclude slicer

The include slicer use the data from table and the exclude slicer use the datta from table 2
Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amaurey]

Hi Albert, 

Thanks for you help, but it's not quite what I'm looking for. I need both slicers to filter the bottom table in different ways, and not the other slicers. My use case is I need to create a list of students who have taken a set of courses (ME102, ME103 for example) and then remove students from the list who have taken ME104. 

[Anonymous · Accepted solution]

Hi @amaurey ,
Based on your description, I performed a new operation
Sample data

Create another table based on the original

Table 2 = 'Table'

Create two measures

Exclude Selection = 
VAR selValTable = VALUES('Table'[Student name])
VAR selValTable1 = VALUES('Table 2'[Student name])
RETURN
IF(
    ISFILTERED('Table'[Student name]),
    COUNTROWS(
        EXCEPT(selValTable1,selValTable)
    ),
    COUNTROWS('Table 2')
)

Filter = COUNTROWS('Table 2') - 'Table'[Exclude Selection]

Apply the filter to the visaul

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly