I have the following conditions in a single table (fact table) with over 250 million rows of data across the past 6 months. This table is incrementally refreshed, but using the current calculated column is causing the incremental refresh to take over an hour which is unacceptable by our data quality KPI's. Based on this, I know that I need a measure in place of the calculated column so that during refresh the "column" is not re-calculated causing the refresh to slow down. The current calculated column actually references another table called tbl_Planned that only contains planned values. I would like to discontinue the use of this table if possible since the planned values are in the tbl_Values table along with the unplanned values.
Current Table with Calculated Column:
Table Map: Colors reference columns that connect the tables.
What I need the measure to do in place of the calculated column is locate the Index value of the planned event where the unplanned events occur during that planned time period. Of course this is a very simplified list and there could be hundreds of thousands or even millions of records that occur during different PlannedEvents.
The idea would be to grab the MAXX Index based on the GroupDate and whether or not the Unplanned Event occurred during a Planned Event of that group based on various conditions listed in the DAX below.
This seems like a good idea, but I cannot get the below DAX to work for me...in fact it only returns blank values for all of the conditions.
With the index I can create other measures based off that value to calculate date differences between the various conditions (depicted as _C1~_C4).