cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qsmith83
Responsive Resident
Responsive Resident

Help with Optimizing Measure

Hi, I need help to optimize this measure that returns the working days between two dates. There was no issue when the dataset was a month's worth (~200k rows) but since I added more months (now at 3M+ rows) it is taking too long, sometimes coming up with error "not enough memory to process..." Appreciate any help please.

Let me know if you need access to file (353MB)

Job to Accep Working Days = 
var _d_jbook = DATEVALUE( Minx('FreightForward v2', [JOB_BOOKING_DATETIME]))
var _d_accept = if ( Maxx('FreightForward v2', [ACCEPTANCE_DATETIME]) = blank(), blank(), DATEVALUE( Maxx('FreightForward v2', [ACCEPTANCE_DATETIME] ) ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN if (ISBLANK(_d_accept), blank(), _workdays -1)

 

1 ACCEPTED SOLUTION

Hi @qsmith83 

 

You'll need a couple of extra columns to do this.  One to hold days with "10+" for results > 10, and a second column to hold the sort order for the first column.

They're both very similar to your existing Job to Accep Working Days column, just altering the RETURN part.

Job to Accep Working Days (10max) = 
var _d_jbook = DATEVALUE( [JOB_BOOKING_DATETIME])
var _d_accept = if ( [ACCEPTANCE_DATETIME] = blank(), blank(), DATEVALUE( [ACCEPTANCE_DATETIME] ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN 
if (ISBLANK(_d_accept), 
    blank(),
    IF(_workdays -1 >=10, 
        "10+",
        FORMAT(_workdays -1, "#")
    )
)
Job to Accep Working Days (10max) Order = 
var _d_jbook = DATEVALUE( [JOB_BOOKING_DATETIME])
var _d_accept = if ( [ACCEPTANCE_DATETIME] = blank(), blank(), DATEVALUE( [ACCEPTANCE_DATETIME] ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN 
if (ISBLANK(_d_accept), 
    blank(),
    IF(_workdays -1 >=10, 
        10,
        _workdays -1
    )
)

You then set the sort order for the Job to Accep Working Days (10max) column in the Data view.

 

PaulOlding_0-1635848958094.png

Final result:

PaulOlding_1-1635849247627.png

 

 

View solution in original post

6 REPLIES 6
VahidDM
Super User
Super User

Hi @qsmith83 

 

Can you share a sample of your data in the table format [to be able to copy and paste that]?

 

Appreciate your Kudos!!

 

qsmith83
Responsive Resident
Responsive Resident

Hi @VahidDM , here is sample file with reduced dataset - 1month only

https://drive.google.com/drive/folders/1dmmbhdbzySZBr8oTxj9CWsmztwFQ3f8A?usp=sharing

Hi @qsmith83 

 

Here's the baseline performance of your measure:

PaulOlding_0-1635761663071.png

5.8s to run, but more importantly 5 SE (Storage Engine) queries of which 3 are tables with a large number of rows.  Reducing the number of queries and/or their size should be the aim as that's what will improve performance.

That leads to the first thing you can do - split your datetime fields into a date field and a time field.  The SE queries with 300k rows are because you're using datetime fields in your visual, but the time has no bearing on this calculation.

If you were to use the same measure on a visual with JOB_BOOKING_DATE and ACCEPTANCE_DATE (new date fields) then you'd get this performance:

PaulOlding_1-1635762604604.png

Down to 42ms.  Same number of SE Queries but they're much smaller.

 

A second thing that occurs to me is does this need to be a measure or could it be a calculated column?  Having the working days pre-calculated is always going to be quicker than a measure.

 

 

Hi @PaulOlding , thanks for clarifying the issue. I used custom column before but the expected results were not correct however I've managed to get it to work now and everything works fine.

I have a question if you're able to assist please. I have another measure that displays jobs by working days as per screenshot below but I want to group working days >=10 and show as 10+ i.e. it will sum jobs >=10 so for the screenshot example, the expected result will show value of 167(29+2+4+74+14+2+1+41) for 10+

Here's link to sample file

https://drive.google.com/drive/folders/1dmmbhdbzySZBr8oTxj9CWsmztwFQ3f8A?usp=sharing

Screenshot 2021-11-02 105119.jpg

Hi @qsmith83 

 

You'll need a couple of extra columns to do this.  One to hold days with "10+" for results > 10, and a second column to hold the sort order for the first column.

They're both very similar to your existing Job to Accep Working Days column, just altering the RETURN part.

Job to Accep Working Days (10max) = 
var _d_jbook = DATEVALUE( [JOB_BOOKING_DATETIME])
var _d_accept = if ( [ACCEPTANCE_DATETIME] = blank(), blank(), DATEVALUE( [ACCEPTANCE_DATETIME] ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN 
if (ISBLANK(_d_accept), 
    blank(),
    IF(_workdays -1 >=10, 
        "10+",
        FORMAT(_workdays -1, "#")
    )
)
Job to Accep Working Days (10max) Order = 
var _d_jbook = DATEVALUE( [JOB_BOOKING_DATETIME])
var _d_accept = if ( [ACCEPTANCE_DATETIME] = blank(), blank(), DATEVALUE( [ACCEPTANCE_DATETIME] ))
var _d_end = if (ISBLANK(_d_accept) || _d_jbook > _d_accept, _d_jbook, _d_accept)
var _workdays = CALCULATE( countrows( 'Calendar Job Booking'), 
                    DATESBETWEEN('Calendar Job Booking'[Date], _d_jbook, _d_end), 
                    FILTER('Calendar Job Booking', 'Calendar Job Booking'[WorkingDay] = "yes")
                    )
RETURN 
if (ISBLANK(_d_accept), 
    blank(),
    IF(_workdays -1 >=10, 
        10,
        _workdays -1
    )
)

You then set the sort order for the Job to Accep Working Days (10max) column in the Data view.

 

PaulOlding_0-1635848958094.png

Final result:

PaulOlding_1-1635849247627.png

 

 

@PaulOlding, perfect that worked. Cool ninja stuff with the first measure with no values showing but acting as a 'holding' / 'storage' column. Thanks again.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors