Ok, so I've really been struggling with this one. I've also struggling succinctly laying out the issue, so hopefully this makes even a little bit of sense.
In a database, I have two sets of tables I'm interested in combining in tableau dashboards.
The first is methodology/content related to metrics:
MetricID | MetricName | Definition | Purpose
1 | Exceptions | Explain calc/measure | Explain what the metric is all about, why it's important, business stuff
There are maybe 30 of these. There are some also other tables joined in that break these metrics up by different areas/classifications.
The second is a reporting table housing aggregated performance:
Dimension 1 | Dimenion 2 | Dimension n | Calc 1 | Weight 1 | Calc 2 | etc...
this includes a number of raw calculations done at the DB level, and as needed weights. I then have some custom calcs in Tableau that dynamically calculates weighted performance based on how the data is being aggregated in Tableau. As a standalone data source, it works great.
So I'm trying to create a nice dashboard that combines methodology with data. The thought is that a user who may not be very "data" savvy can flow through the methodology content, read all about the whats and whys, and using dashboard filtering end up on a list of metrics, and a second list of metrics that are known to relate to the first. By selecting the primary and related metrics they can see a scatter plot of how the two metrics relate to each other.
I've gotten close trying to use parameters, but unfortunately those aren't dynamic (I saw this has some votes on the requested features list). This means the final "a ha!" moment for the user of selecting metrics does nothing, and instead they have to re-select metrics from the drop-down. This would all be solved if parameters could be interacted with in a dynamic fashion...but oh well.
The alternative seems to be adjusting the back-end data structure, but I can't seem to figure out a solution that doesn't require either creating a second almost duplicate reporting table that has one calculation per row and a foreign key to a metric on my metric table, or some really hacky
joins that are messy, ruin performance, and aren't worth going into here.
Attached is a generic version of the parameters version mentioned above, but it appears to be a dead-end. At least it may help show what I'm looking at.
Anyone have any idea of how I can either combine these two sources of data (join or blend), or tricks in Tableau to make this work?
And if I'm making no sense, call me out and I'll try again.
Tableau Demo.twbx.zip 45.1 KB