I'm new to Tableau and believe me I did my best trying to solve this issue on my own but to no avail.
I have a problem with user filtering. Well, I have two working solutions, but not the ones I intend to have.
To quickly describe a situation - I have a very similar dataset to the one in the user filtering tutorial:
Quick description of data. I have hundereds of individaul supersotres and their sales values. Each superstore gets a report where they can see their sales and as a benchmark global sales, country sales and supersotre type benchmark sales. The benchmark names are stored in the same column as supersotre names since I unioned the queires in SQL:
select superstorename, sum(sales)
group by superstorename
select countrybenchmark as superstorename, sum(sales)
group by countrybenchmark
[... and so on]
The problem I have is that I want to apply specific user to see more than one category. Let's say I have a manager who should be able to see his store sales, global stores sales, USA store sales and Home&Garden sales.
I can (just like in the tutorial above) go through the list and apply manually each 4 categories to each stores - this works well, but for hundereds of clients it would take me a week to apply correct filters.
The second thing I can do is to set a datasource filter username()=[superstorename] but this will only show superstore values not benchmark values unless I can somehow make this formula return a set of values. - this doesn't work for me since I need to show all 4 categories i.e. superstore, global, country and type benchmarks.
Third solution is to do an additional column, named e.g. superstorenamefilter, which for each superstore will cointain only superstorename value, but then in superstore coulmn it will have correct superstorename, and all necessary benchmarks (see attachement - solution 3). This way datasource filter username()=[superstorenamefilter] will return the correct set of 4 categories. This solution also works for me, but the drawback is, that global benchmark is multiplied n number of times where n is the number of superstores and other benchmarks are duplicated numerous times as well. The extract weights nearly 4 gigs and when I need to update it, it's just way too big.
So my question to the community is:
is there a way to somehow auotmate first solution or edit the formula in second solution that it returns 4 values not just one?
All tricks on the database allowed! I attached an excel worksheet showing how the data looks like and what I mean exactly. I hope this is all clear.
My data is way more complicated than that. This is just an example, but the principles are the same.
Tableau example.xlsx 14.6 KB