Reduce memory used by DAX Command

[GusLima121]

Greetings,

I need help reducing the DAX Measure used memory so the calculations are shown fully.

I'm using the followin Measure to calculater Overlapping Dates Duration

_Calculate Overlapping Duration =

_minDate = "16/03/2024"

_maxDate = "15/04/2024"

VAR secondsPerHour = 3600

VAR secondsPerDay = secondsPerHour * 24

VAR secondIndex =

SUMMARIZE

(

    GENERATE 

    (

        FILTER

        (

            'SAMPLE DATA', 

            ([END] >= _minDate && 

            [END] <= _maxDate)

            ||

            ([START] >= _minDate && 

            [START] <= _maxDate && 

            [END] >= _maxDate)

            ||

            ([START] <= _minDate && 

            [END] >= _minDate)

        ),

        GENERATESERIES 

        (

            INT ( 'SAMPLE DATA'[START] * secondsPerDay ) + 0,

            INT ( IF('SAMPLE DATA'[END] > _maxDate, _maxDate, 'SAMPLE DATA'[END]) * secondsPerDay ) + 0

        )

    ),

    [Value]

)

VAR numSeconds = COUNTROWS( secondIndex )

VAR tempoTotal = numSeconds / secondsPerHour

VAR hours = INT(tempoTotal)

VAR minutes = (tempoTotal - INT(tempoTotal)) * 0.6

VAR result = hours + minutes

RETURN

result

Tecnically, it's working, but only when i filter the machine or use a very smal date range

I need the calculations to show me at least a full year worth of data.

Edit: My goal with this DAX is to calculate the duration of all overlapping and non-overlapping instances of 'SAMPLE DATA' by Machine

Here's the data: Sample Data 

[AntrikshSharma]

@GusLima121 How big is the 'SAMPLE DATA' table? Instead of table reference use just the columns you need to reduce the total distinct values on which the calculation needs to performed.

instead of FILTER ( 'SAMPLE DATA' ....

use:

SUMMARIZE ( 
    'SAMPLE DATA', 
    'SAMPLE DATA'[START], 
    'SAMPLE DATA'[END] 
)

[GusLima121]

I have a total of 5.3K rows in that table, 3.4K only in 2024, being CC 5021 with 1032 rows.

The reason i filter the Dates is because i need it to return all orders that:

1. Started and ended inside my range
2. Started inside and ended outside
3. Started before and ended after.

So the filters are important to me.

[lbendlin]

Any particular reason for not using INTERSECT()  ?

[GusLima121]

From what i've seen about Intersect, i don't think it'll be of use, im looking for the duration of all overlapping instances correctly (just noticed i didn't include my intent).