topic Re: Reduce memory used by DAX Command in DAX Commands and Tips

Reduce memory used by DAX Command

GusLima121 — Thu, 20 Jun 2024 12:11:55 GMT

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

Re: Reduce memory used by DAX Command

lbendlin — Thu, 20 Jun 2024 01:21:20 GMT

Any particular reason for not using INTERSECT() ?

Re: Reduce memory used by DAX Command

GusLima121 — Thu, 20 Jun 2024 12:09:02 GMT

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

Re: Reduce memory used by DAX Command

AntrikshSharma — Thu, 20 Jun 2024 12:26:35 GMT

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] )

Re: Reduce memory used by DAX Command

GusLima121 — Thu, 20 Jun 2024 15:04:36 GMT

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:

Started and ended inside my range
Started inside and ended outside
Started before and ended after.

So the filters are important to me.