11 Replies Latest reply on Feb 11, 2017 4:34 AM by Simon Runc

# Creating Filter Based on Sets with Intersecting Data

Version: 9.3.1

Packaged Workbook: Attached

Challenge:

First time poster, so please advise if I break etiquette. I have looked for the answer to this specific question to no avail, on and off these forums. I am attempting to create sets, and then create a filter off of those sets. The sets have intersecting members (one record can belong to multiple sets). I currently have a calculated field using IF statements which results in only the first IF statement claiming the intersecting members. I understand why it is not conceptually working – just not sure how to remedy it. I appreciate any insight you all may have.

Workbook:

Currently

• Select “John’s Favorites” in the quick filter: Blue, Gold, Green, Mauve, Orange, and Purple
• Select “Jane’s Favorites” in the quick filter: Red, Silver, Teal, Yellow

Goal

• Select “John’s Favorites” in the quick filter: Blue, Gold, Green, Mauve, Orange, and Purple
• Select “Jane’s Favorites” in the quick filter: Orange, Purple, Red, Silver, Teal, Yellow
• ###### 1. Re: Creating Filter Based on Sets with Intersecting Data

hi Cameron,

Absolutely spot on with the forum etiquette!! - Tableau Version Number, Example Workbook, and Clear Question...the big 3!! I wish I had a better answer for you!!

As you've eluded to the problem here is that you want the dimension to be different things depending on the selection, and as an IF statement exits on a true condition this causes the problem (the table below shows how Orange can't be defined as both John and Jane at the same time)

Can I ask what you are trying to achieve (in your real world version), as there might be other ways we can do this (for example we could create the formula to use the 'In Either Set', which always shows, and then selecting the additon of John/Jane would add their results...but would mean the end-user would always have the extra option in the drop-down).

In the attached I've used a parameter to get the result you want...just not sure if this is an option in your real world case.

If you can let me know on the above we can explore a solution.

2 of 2 people found this helpful
• ###### 2. Re: Creating Filter Based on Sets with Intersecting Data

Cameron,

This is the best etiquette I have ever seen among over 1000 posts!!!!

Version: 9.3.1

Packaged Workbook: Attached

About solution, I think you need to use parameter.

[Filter by Param]

if [Parameter 1]="Jane's Favorite" then

(if [Jane's Favorite Colors] then "show" else "hide" end)

elseif [Parameter 1]="John's Favorite" then

(if [John's Favorite Colors] then "show" else "hide" END)

end

Filter "show" you can see the list based on selected parameter.

Thanks,

Shin

3 of 3 people found this helpful
• ###### 3. Re: Creating Filter Based on Sets with Intersecting Data

Cameron Taylor, I have no answer but I agree with Simon and Shin, GREAT STARTING POST!  You did everything we like, nice job

• ###### 4. Re: Creating Filter Based on Sets with Intersecting Data

Version: 9.3.1

Packaged Workbook: Revised, Attached

Thank you very much - the parameter is the perfect solution for two sets. Now I've complicated the issue with two extra stipulations: more than two sets built off the dimension, and a set built off a different dimension. I solved the first by combining the original combined set and the third set, then combining the new combined set with the fourth set. The second is tougher because sets based on different dimensions cannot be used to form a combined set. Any ideas on how to circumvent this limitation?

In terms of the real world challenge, it is similar to the following fictional scenario. Data (four fields): mobile apps, mobile app type (i.e. gaming, business, etc.), app owner, and app user. I want the parameter filter to list each app user and their set of favorite apps. The tricky part is that one of the app users bases his favorite apps off of app type, whereas the rest of the app users base their favorites off of app owner. So the only remaining wrinkle is how to include both type of users in the same parameter filter.

Workbook:

Currently

• Select John's, Jane's, Ashley's, or Tim's Favorites in the parameter filter: their favorite colors

Goal

• Select John's, Jane's, Ashley's, or Tim's Favorites in the parameter filter: their favorite colors
• Select Bob's Favorites in the parameter filter: his favorite shirts (and corresponding colors)
1 of 1 people found this helpful
• ###### 5. Re: Creating Filter Based on Sets with Intersecting Data

Thank you very much, Shin. That solution works perfectly for two sets based off of the same dimension. Please feel free to check out my reply to Simon if you want an extra challenge.

• ###### 6. Re: Creating Filter Based on Sets with Intersecting Data

What Toby said! Minor edit: "... everything we like, and usually need..."

--Shawn

• ###### 7. Re: Creating Filter Based on Sets with Intersecting Data

Thanks, Toby. Now I know how to create a filter from multiple sets with intersecting data AND who Gareth Bale is. I'm not sure which is more important.

• ###### 8. Re: Creating Filter Based on Sets with Intersecting Data

I'm not sure I understand your new request very well or not, but could you check attached file.

I'm only assuming "AND" scenarios, but if you are talking about "OR" scenario, that's different story.

Let me know if that's the case.

Thanks,

Shin

• ###### 9. Re: Creating Filter Based on Sets with Intersecting Data

Those are the correct groupings, but I would like to essentially combine those two filters.

So the filter would have these options:

• John's Favorite Colors
• Jane's Favorite Colors
• Ashley's Favorite Colors
• Tim's Favorite Colors
• Bob's Favorite Shirts
• ###### 10. Re: Creating Filter Based on Sets with Intersecting Data

I figured it out - workbook attached. I was also able to get rid of all combination sets without losing any functionality. Please let me know if they are actually necessary for some other reason.

Thank you all very much for your help.

1 of 1 people found this helpful