Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Help
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    Hi Anonymous 

     

    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.

     

    Final result:

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

PaulOlding's avatar
PaulOlding
Solution Sage
4 years ago

Hi Anonymous 

 

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.

 

Final result:

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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