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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.