Filter Tables based on Selected Range in Unrelated Table

[Aweptimum]

Hello,

I've got two time-history tables (named IO and Obj) that track different values over the same time period using UNIX timestamps. I needed one of them to filter the other on timestamp range selected, but their timestamps don't match. To get around this, I've made a calculated timestamp table that both are related to. The relationship looks like this:

However, the problem is that there's two kinds of data in the IO history table that are recorded in separate rows with a flag denoting what type. I have a filter on this flag, but selecting one or the other filters the timestamp in the IO table and consequently shows only a portion of the Obj table. Really what I need is to be able to is get rid of the relationships and filter the Obj history by the selected IO range, not by matching timestamps as I am doing now. If I can do that, then I don't need the UNIX table anymore.

I've searched around and can't really find a good measure/calculated-column that serves as the range filter I need. It seems it would be as easy as adding a calculated column to the Obj history that is set to a 1 when a row's timestamp is between the filtered bounds of the IO and a 0 when outside of the bounds, and then adding a filter to the Obj History visual to only show rows with a value of 1 in that calculated column. I just don't know how to do this with unrelated tables.

Any help is much appreciated!

[Aweptimum]

Well, I got something that should work in principle. I added upper and lower bound measures for the IO table that correctly get the filtered max/min of the timestamp column. I then added a column in the Obj table that sets a 1 if the row's timestamp is within these bounds, otherwise a 0. However, this column doesn't seem to update with the measures. Anyone got a hack around this?

[Aweptimum · Accepted solution]

Yeah, I did some googling and this calculcated column remaining static is intended behavior. Very frustrating. However! I tested a hunch and took my In_Range calculated column and turned it into a measure, replacing the Obj History[timestamp] with MAX/MIN(Obj History[timestamp]). On it's own, this would do nothing useful. I then added the measure to my Obj chart as a visual level filter that only shows values when the measure is 1.

The result: Filtering the IO history filters the Obj history to the corresponding range and it looks beautiful. I think the only drawback is there's a bit more latency between slicer selection and the Obj plots updating, but the upside is filtering the IO table on any dimension besides time doesn't force the bounds to update (and subsequently the Obj history).

Still, I wish this kind of stuff wasn't so unintuitive, I feel like I'm over-loading the measure logic instead of using it as intended.