Forum Discussion
show values if present in multiple category filter
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.
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.
3 Replies
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.- amitchandak
Super User
Anonymous, Seem like you need something like
And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk
