Measure to filter out time based on conditions

[UPCBishop]

Hello Everyone,

Problem:

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:

Index	GroupDate	StartTime	EndTime	Description	Column_PlannedID
1	Group1.44644	3/22/2022 08:00:00	3/22/2022 08:15:23	UnplannedEvent	3
2	Group1.44644	3/22/2022 08:07:34	3/22/2022 08:11:55	UnplannedEvent	3
3	Group1.44644	3/22/2022 08:09:11	3/22/2022 08:49:54	PlannedEvent	3
4	Group2.44644	3/22/2022 08:06:25	3/22/2022 08:27:44	UnplannedEvent	5
5	Group2.44644	3/22/2022 08:10:01	3/22/2022 08:40:37	PlannedEvent	5

Table Map:
Colors reference columns that connect the tables.

Idea:

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

Any and all help would be greatly appreciated.

Part of the calculated column DAX.

Measure =
VAR _PlanStart = MAXX(FILTER(tbl_Values, RELATED(tbl_Groups[Description]) = "UnplannedEvent"), tbl_Values[StartTime])

VAR _PlanEnd = MAXX(FILTER(tbl_Values, RELATED(tbl_Groups[Description]) = "UnplannedEvent"), tbl_Values[EndTimeNow])



VAR _C1 = MAXX(FILTER(tbl_Values, tbl_Values[StartTime] < _PlanStart && tbl_Values[EndTimeNow] > _PlanStart && tbl_Values[EndTimeNow] <= _PlanEnd), tbl_Values[Index])



VAR _C2 = MAXX(FILTER(tbl_Values, tbl_Values[StartTime] < _PlanStart && tbl_Values[EndTimeNow]> _PlanEnd), tbl_Values[Index])



VAR _C3 = MAXX(FILTER(tbl_Values, tbl_Values[StartTime] >= _PlanStart && tbl_Values[StartTime]<= _PlanEnd && tbl_Values[EndTimeNow] > _PlanEnd), tbl_Values[Index])



VAR _C4 = MAXX(FILTER(tbl_Values, tbl_Values[EndTimeNow] > _PlanStart && tbl_Values[EndTimeNow] < _PlanEnd && tbl_Values[StartTime] >= _PlanStart && tbl_Values[StartTime] <= _PlanEnd), tbl_Values[Index])



RETURN

COALESCE(_C1, _C2, _C3, _C4)

Desired outcome:

Index	GroupDate	StartTime	EndTime	Description	Measure_PlannedID
1	Group1.44644	3/22/2022 08:00:00	3/22/2022 08:15:23	UnplannedEvent	3
2	Group1.44644	3/22/2022 08:07:34	3/22/2022 08:11:55	UnplannedEvent	3
3	Group1.44644	3/22/2022 08:09:11	3/22/2022 08:49:54	PlannedEvent	3
4	Group2.44644	3/22/2022 08:06:25	3/22/2022 08:27:44	UnplannedEvent	5
5	Group2.44644	3/22/2022 08:10:01	3/22/2022 08:40:37	PlannedEvent	5
6	Group2.44644	3/22/2022 11:01:08	3/22/2022 11:29:17	UnplannedEvent	7
7	Group2.44644	3/22/2022 11:00:00	3/22/2022 11:30:29	PlannedEvent	7

[v-xiaotang]

Hi @UPCBishop  

I've checked your measure expression, and find it uses data from another table 'tbl_Groups', could you share some data of all related tables? Otherwise, I cannot test the measure....

Also, you can test the measure by returning a single variable.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[UPCBishop · Accepted solution]

Hello @v-xiaotang, 

I was actually able to get this working over the weekend with a few changes to our data warehouse tables...namely adding start and end time columns specfically for planned events. 

With the new columns and a few data clean up tasks I was able to get the measure going and working as expected. 

Thank you for reaching out to provide assistance!