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 s...
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

     

     

     

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!