Forum Discussion

amaurey's avatar
amaurey
Regular Visitor
2 years ago
Solved

Using Include and Exclude Slicers to Generate List

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. 

 

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      amaurey
      Regular Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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

         

         

         

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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's avatar
      amaurey
      Regular Visitor
      2 years ago

      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!