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