Does anyone has a solution for this or its a dead end?
I believe this would be much simpler if your data was structured such that each customer had one row, with multiple columns for each organization type they belong to--which seems unnatural, since Tableau generally handles TALL data very well.
However, I could be completely wrong and there is likely a way to get what you want without restructuring your data--I'm just blanking on this right now. Hopefully someone smarter than me will chime in!
I am sure someone has come across this problem. Sounds like a common problem and an obvious one in many to many type cases.
Certainly, they have--there are examples of these types of problems all over the forum. Its just that it is pretty common to reshape data before bringing into Tableau as part of a solution, as well. I'm not saying there isn't a way to do it--I'm sure there is but I have been unable to get the "usual" methods to work because the data structure won't allow us to do things like:
IF CONTAINS(Orgname, "Boston") AND CONTAINS(Orgname, "LongRoom") then Person END
OR a filter like: CONTAINS(Orgname, "Boston") AND CONTAINS(Orgname, "LongRoom")
...Because each person has three rows, rather than one. I was unable to use sets or multiple parameters, as well. But I may be missing something obvious here, too.
I just believe it would be much simpler re-shaped. But, as I said, I could be totally wrong and way off-base. I'll let someone else chime in before I go off trying to reshape the data for you. I'm not an expert on setting up data sources--mine come to me pre-compiled for the most part.
How can I reshape the data? Like put it in a cross-tab format?
There are a few ways--there is a Tableau Reshaping tool for Excel, but it typically takes Wide Data and makes it tall, so I'm not sure if it can be used for this. Most folks use Custom SQL in Tableau, or the Database Admin will reshape it for them, if they're lucky.
What I am proposing is one record for each Person in your data.
But lets see if anyone has any other interesting approaches before going that route (assuming you aren't time crunched)
Have a look at the attached. I used a combination of the Total() and index() function to filter the Persons that are members of all the Orgname selected.
Testfile edit mc.twbx 67.1 KB
Thanks Michael. Could you explain me conceptually what you did?
Still here, i just posted yesterday just before leaving the office
So the goal here is to show the persons where the count of Orgname is equal to the Count of Orgname Selected.
If you look in the ValidateCalculation sheet you can see;
-Count Orgname Selected: this is obtained with the TableCalculation TOTAL(COUNTD([Orgname])) computed using Table Down.
So every row gets a number corresponding to the Distinct count of Orgname in the Table. You can see this number change according to the number of Orgname selected in the quick filter.
-index: the index function is computed using Person, Orgname restarting every Person. So if the Person is member of all the Orgname selected, the max index will be equal to the value of [Count Orgname Selected]
-index(MAX): not needed , i was just testing things
-Person Filter: This is use to identify the Person mentionned above. if [Count Orgname Selected] = [index] then 1 else 0 end .
Using this as a filter (selecting the 1) would be enough to identify the Person, but you would get the single row where [Count Orgname Selected]= [index] . But if you what to show all the Orgname then you need the next Filter.
-Person Orgname Filter: WINDOW_MAX( [Person Filter] ) = 1 . The goal here is to set at True all the rows in the Person,Orgname partition where the [Person Filter] have a 1. So for each Person,Orgname partition if the MAX value of [Person Filter] is 1 the set the row at True.
So in Sheet 4 i keep the Person and Orgname on the Rows, i need to keep the Person Filter on the row also because it is needed by the Person Orgname Filter , but i hide the header. And I use the Person Orgname Filter as the filter and keep only the True values.
Thank you for explaining your process--very helpful.
Thanks a lot Michel. Really appreciate putting your effort.