How about the attached?
Just a slight modification of your Color Identifier field, and then set the Compute using for this new field to be member_name, and ctrl-drag-and-dropped onto the filter shelf, keeping only when true, and then set the Color Identifier pill that is on the Color shelf to the Attribute aggregation.
membership_jm_edit.twbx 44.2 KB
That's pretty amazing and exactly what I wanted! I just wish I understood the principle so I could apply it generally. About me, I'm an average user, but I 'm enthusiastic. Is there anything you say in a couple sentences about the theory behind your solution? Any other posts that elaborate on your method? Most of all, thank you!
Let me break down Joe's extra calculated field for you. The entire calculation is this:
TOTAL(SUM(IIF([Choose a Member Name to Find His Co-Members]=[member_nick_name],1,0)))>0
Breaking that down, the inner expression:
IIF([Choose a Member Name to Find His Co-Members]=[member_nick_name],1,0)
returns 1 if the member is the chosen one and 0 otherwise.
on it's own would say add up the value of the previous expression over all underlying rows in the database for each row in the view in turn. In this case there is probably only one database row for each view row, so this will again just return 1 or 0, but the aggregate is needed for the next function:
is a "table calculation" function which says evaluate the enclosed aggregate expression (i.e the SUM() in this case) over all rows in the "partition", rather than for each row in turn. Table calculations are quite a difficult topic to explain succinctly, but in this case, with the "Compute Using" set to [member_name], it means calculate the total SUM() over all members of each club. So for clubs which have the chosen person as a member this will evaluate to 1 for each member of the club (because in total there is one member with that name) and for the the other clubs it will evaluate to 0 for each member (because there are no members with that name).
Finally, Joe tacked this on the end:
That turns the whole expression into a comparison which tests to see if (the total number of members of the current row's club with the chosen name) is greater than zero. So the overall expression returns a true/false value: true for clubs which include the chosen member, false for other clubs.
That expression can be dropped on Level of Detail (at which point the Compute using can be set) and then copied onto the filter shelf.
Hopefully that helps.
Thank you Richard. I think I understand, but it's really about the journey more than the destination meaning I don't mind being a student of you two. Thanks again Joe! I love what Tableau can allow us to do.