Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
UPCBishop
Frequent Visitor

Measure to filter out time based on conditions

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.

UPCBishop_0-1648578797001.png


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

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

View solution in original post

2 REPLIES 2
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1648784187128.png

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.

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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.