CALCULATETABLE with USERELATIONSHIP depending on whether another fact is also in scope

[Anonymous]

I am implementing a solution for industrial IoT automated manufacturing and facing a challenge with cycle time facts and sensor event facts with respect to date/time filtering.  

The manufacturing process has 6 steps, and I have a cycle time fact for each that contains measures specific to that step.  I have an additional fact table, Production Lot Cycle Time that includes the Start Timestamp and End Timestamp for each production lot, for each of the 6 steps.  There is a 1:1 relationship from the cycle time fact to each of the 6 step-specific facts.  Throughout the end-to-end process, we gather sensor readings, and I have one fact table, Sensor Event, which has Event Type, Event Timestamp, and Event Value. 

Let's just focus on the two fact tables of concern here: Production Lot Cycle Time and Sensor Event.  

Most reports include the cycle time fact and are filtered on the date dimension by some level of the production calendar hierarchy (e.g. production week). For this, I've implemented a calculated table that includes all fact records where their Start Time or End Time is in the filtered date dimension time period.  These reports typically also include Sensor Event measures. Here, we need to show all sensor events from the earliest cycle time start date to the latest cycle time end date.  I have done this by again implementing a calculated table that filters Event Timestamp between min(cycle time start timestamp) and max(cycle time end timestamp).  

My challenge is for detail / drill-through reports that only show Sensor Event measures for a specific date (or week or month...).  These detail reports don't have any measures from the Cycle Time fact, so filtering on min(start) and max(end) timestamps doesn't make sense.  For these pages, I need to join the Sensor Event fact to the Date dimension, using the inactive relationship.  I'm trying to avoid having to expose two different sets of the same Sensor Event measures. 

I'd like to be able to define a condition in the CALCULATEDTABLE expression for Sensor Event that uses the min/max filter if the Cycle Time fact is in the reporting context, otherwise uses a USERELATIONSHIP filter for the inactive date dimension relationship.

Is this possible?

[ValtteriN]

Hi,

This is a bit hard to follow due to the complexity of your question, but in essence you want to have one calculation if a certain value exists in a filter condition and other if it does not. You can achieve this by using CONTAINS: https://docs.microsoft.com/en-us/dax/contains-function-dax

Just place your search result in a variable and test for it in your calculated table using CONTAINS then based on the result either keep the calculation logic or use another.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!