show values if present in multiple category filter

[Anonymous]

I have a table which has the following structure:

Name	Subject	Marks
David	Math	80
Susan	Physics	76
David	Biology	90
Susan	Math	88
Oliver	Physics	56

I would like to make a filter for Subject where if I select multiple combination of Subjects then the table will only show the name and marks for those which are present in both Subject filter. for example, if in Subject filter I select Math and Biology then it will only show the result for David only because David is the only person who has this combination. It should not show other results just because they are present in Math. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. create a new table

Table 2 = VALUES('Table'[Subject])

2. create a measure with below dax formula

Measure =
VAR tmp =
    SELECTCOLUMNS ( 'Table 2', "Choose Subject", [Subject] )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), [Subject] IN tmp )
VAR tmp2 =
    SUMMARIZE ( tmp1, [Name], "Distinct Count", DISTINCTCOUNT ( 'Table'[Subject] ) )
VAR _a =
    COUNTROWS ( tmp )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( 'Table'[Name] ),
        FILTER ( tmp2, [Distinct Count] = _a )
    )
VAR _b =
    SELECTEDVALUE ( 'Table'[Name] )
VAR _c =
    SELECTEDVALUE ( 'Table'[Subject] )
VAR _d =
    SWITCH ( TRUE (), _a = 1, IF ( _c IN tmp, 1 ), _a > 1, IF ( _b IN tmp3, 1 ) )
RETURN
    IF ( ISFILTERED ( 'Table 2'[Subject] ), _d, 1 )

3. add a table visual and slicer visual

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a lot, worked perfectly.

[amitchandak]

@Anonymous, Seem like you need something like

And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk