3 Replies Latest reply on Jan 23, 2013 3:30 PM by Soumitra Godbole

# How to filter based on multiple values of a single field with AND condition

Example : We have 5 Students (A, B, C, D & E) who register for 6 different Mathematics Course Numbers (MATH101, 102, 103, 104, 105 & 106). The Database with the above 2 fields (Student name and Course Number) is shown below

Student       Course

Name          Number

A                 101

A                 102

A                 104

B                 102

B                 103

B                 104

C                 102

C                 104

C                 106

D                 101

D                 102

D                 105

E                 101

E                 106

We have the Course Filter (Multiple value list) 101, 102, 103, 104 & 105. What we are trying to determine is the Number of students who are registered for multiple course numbers.

Now, if we select 102 & 104 then we should see only 3 Students (A, B & C) and similarly if we select 101 & 102 then we only have 2 students (A & D). I have a solution using Windows_Max which works when we have common courses but it does fail when we select courses that no one has in common Eg:- 102 & 106.

Basically what we are trying to achieve is use Multiple values for the Course Name filter and get the names of Students registered for just those selected Courses (AND condition).

• ###### 1. Re: How to filter based on multiple values of a single field with AND condition

Soumitra,

What about using a of the Total of the distinct count of students and comparing that to the Window_SUM of courses for a given student?  I've attached a workbook that demonstrates this.

Joshua

• ###### 2. Re: How to filter based on multiple values of a single field with AND condition

P.S.

I'm happy to provide explanation of anything if you'd like.

Usually, I'd try to give a little more, but am pressed for time just now.  Please let me know if there is anything that doesn't make sense.  Thanks!

Joshua

• ###### 3. Re: How to filter based on multiple values of a single field with AND condition

Hi Joshua,

Thanks for your efforts and quick response. I replied to your email from my outlook but am not sure if you even received it. My logic was similar to yours with minor changes i.e. equating the Course Count for a Student to the Total Number of Course we select (from the Course filter). I did tweak my approach and it seems to work fine now. Basically i created a calculated field called "Condition Check" with the following formula

If  Countd ( [Course] ) = Total ( Countd ( [Course] ) )  Then 1 Else 0 End

Finally filter the results to only show values that are at least 1. I have enclosed a packaged Tableau Workbook with modified data (names instead of alphabets).