Can nobody help with this?
Can you provide some sample data (made up data is fine) showing how your data is structured and what result you are trying to achieve - it's a bit difficult to give specific advice without understanding your situation a bit better.
But a couple of general comments.
1) Data blending will only return data aggregated at the level of the defined relationship fields. So in your many to many case - you can only get aggregates at the level of your one common field. The ATTR() aggregate will return the actual value if there is only one value, otherwise "*". You won't be able to filter on the underlying elements.
2) Joining a many to many like this in your data connection will give you all permutations, as you observe.
It's likely that you will be able to do what you want with a custom SQL connection. If you can provide some sample data and what you hope to see I'm sure someone will post an answer.
Thanks for your response, Richard.
I understand your comments, and the inability of being able to filter the underlying elements of a "*", as you state in comment 1), is indeed my primary problem. It's a real shame that this isn't possible in Tableau! I really would have thought this would have been possible... :(
The only workaround I have managed to come up with is to do a full join using SQL, and then assign percentage proportions to each combination so that when you sum them up you get back to 1, but this is very messy and quickly gets very complicated.
Here is some sample data of what I am trying to blend...
Incident Outage Duration
T10001 Server A 2
T10001 Server B 3
T10001 Server D 5
T10002 Server A 12
T10002 Server C 1
T10003 Server B 0.5
T10003 Server D 1
T10003 Server E 3
T10004 Server A 7
T10005 Server A 2
T10005 Server B 7
T10001 Human error
T10001 Code error
T10002 Power fault
T10003 Code error
T10004 Power fault
T10004 Human error
T10005 Hardware failure
I get a set of results showing the * where there is more than 1 cause per incident, but when I start to use the filters on the causes, it never returns any results for T10001 or T10004. What I would expect to see is when I use the filter on Human Error, it returns the data associated with T10001 and T10004, and when I filter for Power fault I would expect to see data on T10002 and T10004 (all I get is T10002)
Thanks again in advance.
test_tableau_data.xlsx 10.7 KB
I think a SQL join is the correct way to go. I don't quite understand your counting problem - I would have thought you should be able to achieve what you want with distinct counts (COUNTD() - unless of course your datasource is text files, the MS JET engine doesn't support COUNTD(). If that is the problem, you could create an extract.
Or am I still missing the point?