Hi, after suggestions on approaches to solving a puzzle.
The data represents a production line product rate. Think cookies per second, measured every 5 minutes but sometimes there are gaps in sensor reading, it's not contiguous.
Occasionally the production line slows or stops due to some sort of event, failure, or lack of resources.
One of the visuals we want to look at is - on the X axis, bin the production rate into segments of 100 units with a range, 0 - 6,000. On the Y axis, count the number of times the production rate was in that segment. A second Y-axis measure is the number of downtime events that occurred while the plant was running at that rate. The data is in 2 different fact tables and have different levels of dimensionality. The common dimensions are a Date dimension and a time dimension (5 minute segments in a day) and a bin rate dimension for the segmentation.
The problem above is not the tricky part. The next question to answer is; what was the production rate at 5, 10, 15 and 20 minutes before the downtime event? The Downtime events and plant rates need to be re-segmented based on the lagged production rate.
I've generated the unique combination of production rate bins for the different lagged rates and it's about 500K rows.
My thought was to use this as a dimension and assign a dimension key to each of the fact tables. Where this falls down is that we have multiple production lines and in one, the production rate is measured in 3 places but we look at downtime events over the whole of that process. This means 1 set of downtime events is binned by 3 different plant rates, but not at the same time.
Other plant rates have their own set of downtime data.
Production rates are not aggregated over the same or multiple production lines, they're only ever looked at in isolation.
I feel this is a DAX problem due to mixed cardinality, but it's complex given the non-contiguous rate data. The volume of data is a secondary concern and may adversely impact the performance of the visual.
EDIT : Resolved -
The time dimension was extended to the full date range of data.
The lagged production rates were added to that fact table.
Then the filter between the time dimension and the downtime event fact table was set to bi-directional (caution noted)
On the visual the Binned and Lagged Production rates now come from the Production rate fact table.
Works as expected and DAX is simple.
