Forum Discussion

scaballerom's avatar
scaballerom
Helper I
3 years ago

DAX optimization

Hi all,   I have a table of about 2M records, with a column, task_starting_datetime (dd.mm.yyyy HH:mm:ss), which is my reference date. I need the specific time on the datetime, so it's hard to link...
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi scaballerom 
Please try

MedianDurationTaskR3M =
VAR NumberOfDays = 91
VAR CurrentYear =
    YEAR ( TODAY () )
VAR MaxDay =
    MAX ( 'Fact Tasks'[task_starting_datetime] )
VAR MinDay = MaxDay - NumberOfDays
VAR Result =
    CALCULATE (
        MEDIAN ( 'Fact Tasks'[task_duration_min] ),
        ALLEXCEPT ( 'Fact Tasks', 'Fact Tasks'[task_key] ),
        YEAR ( 'Fact Tasks'[task_starting_datetime] ) = CurrentYear,
        'Fact Tasks'[task_status] = "Completed",
        ISBLANK ( 'Fact Tasks'[task_starting_datetime] ) = FALSE,
        'Fact Tasks'[task_starting_datetime] <= MaxDay,
        'Fact Tasks'[task_starting_datetime] > MinDay
    )
RETURN
    Result
  • scaballerom's avatar
    scaballerom
    Helper I
    3 years ago

    Hi tamerj1 ,

     

    I tried your formula, but the following message appears:

    BR,

    Sara

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      scaballerom 
      Hi Sara,

      Not sure why you're filtering ALL( 'Table' ) then filtering for the SELECTEDVALUE('Fact Tasks'[task_key]). It seems to me that this way you will end up with the original table that was there in the original filter context. Am I missing something? Please try one of the following

      MedianDurationTaskR3M =
VAR NumberOfDays = 91
VAR CurrentYear =
    YEAR ( TODAY () )
VAR MaxDay =
    MAX ( 'Fact Tasks'[task_starting_datetime] )
VAR MinDay = MaxDay - NumberOfDays
VAR FilterContext =
    FILTER (
        'Fact Tasks',
        YEAR ( 'Fact Tasks'[task_starting_datetime] ) = CurrentYear
            && 'Fact Tasks'[task_status] = "Completed"
            && ISBLANK ( 'Fact Tasks'[task_starting_datetime] ) = FALSE
            && 'Fact Tasks'[task_starting_datetime] <= MaxDay
            && 'Fact Tasks'[task_starting_datetime] > MinDay
    )
VAR Result =
    CALCULATE ( MEDIAN ( 'Fact Tasks'[task_duration_min] ), FilterContext )
RETURN
    Result
      MedianDurationTaskR3M =
VAR NumberOfDays = 91
VAR CurrentYear =
    YEAR ( TODAY () )
VAR MaxDay =
    MAX ( 'Fact Tasks'[task_starting_datetime] )
VAR MinDay = MaxDay - NumberOfDays
VAR FilterContext =
    FILTER (
        CALCULATETABLE (
            'Fact Tasks',
            ALLEXCEPT ( 'Fact Tasks', 'Fact Tasks'[task_key] )
        ),
        YEAR ( 'Fact Tasks'[task_starting_datetime] ) = CurrentYear
            && 'Fact Tasks'[task_status] = "Completed"
            && ISBLANK ( 'Fact Tasks'[task_starting_datetime] ) = FALSE
            && 'Fact Tasks'[task_starting_datetime] <= MaxDay
            && 'Fact Tasks'[task_starting_datetime] > MinDay
    )
VAR Result =
    CALCULATE ( MEDIAN ( 'Fact Tasks'[task_duration_min] ), FilterContext )
RETURN
    Result