cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aweptimum
Helper II
Helper II

Filter Tables based on Selected Range in Unrelated Table

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:

Aweptimum_0-1612966131475.png

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!

1 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.

View solution in original post

2 REPLIES 2
Aweptimum
Helper II
Helper II

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?

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors