Forum Discussion

GusLima121's avatar
GusLima121
Frequent Visitor
2 years ago

Reduce memory used by DAX Command

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 

4 Replies

    • GusLima121's avatar
      GusLima121
      Frequent Visitor
      2 years ago

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

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      GusLima121
      Frequent Visitor
      2 years ago

      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.