This definitely creates a challenge. As you experienced, any field from a secondary data source in a blend must be aggregated. (ATTR() is a special form of aggregation used for non-numeric values. It compares the MIN() and MAX() values of the specified field, and returns the value if they are the same, and an asterisk (*) if they are different.) Because of the aggregation, any fields which are formed by a calculated field incorporating the secondary data source must be a measure. In your case, you want it to be a dimension. I'm guessing this the challenge you encountered before posting the question. (You can see my attempts in Sheets 1, 2, & 3.)
The only solution I could come up with is to create two sheets, one filtered for External hospitals only, with Hospital on columns, and one filtered for Internal hospitals only, with Region on columns. Then, put the two sheets side by side on a dashboard (Dashboard 1 in the attached example).
I don't know if that will be sufficient for you, but that is the best I can do with Tableau 9.2. If/when a future version supports a true join between disparate data sources, this problem can be resolved. v9.3 does have some ability to do this, but I believe it is limited to Excel and text file sources. If I am wrong, I will be overjoyed to be wrong!
I am sure you could accomplish this with an external tool, such as Alteryx.
Hospital blend.twbx 49.4 KB
At least Hospital should be in level of detail to not see *
What I did is first I created new field Status in the first datasource
ATTR([Status (Roll-up Question 2)].[Status])
and then create new calculated field (only with aggregate values)
IF [Status] = 'Internal' then STR(attr(Region))
If you have to sum all hospitals in region 1 for example you have to generate somehow this list in third datasource and start vizualization from it
I mean you have to start with datasource that is with all dimensions that you want to blend and then you can create whit parameters how to be shown
Hope this helps
Book1.twbx 26.9 KB