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 it to a dimensional Calendar table.

What I'm trying to do is compute the median of the duration of the tasks for the last three months, so I used the following formula:

The filters that should be added are:
- task_status is Completed
- task_starting_datetime should not be NULL or BLANK
- I need to compute the median for each task key
- the period to be analyzed is three months prior the selected date.
 
 
MedianDurationTaskR3M =
VAR NumberOfDays = 91
VAR CurrentYear = YEAR(TODAY())
VAR MaxDay = MAX('Fact Tasks'[task_starting_datetime])
VAR MinDay = MaxDay-NumberOfDays

VAR FilterContext =
    FILTER (
            ALL('Fact Tasks'),
            YEAR('Fact Tasks'[task_starting_datetime]) = CurrentYear &&
            'Fact Tasks'[task_status]="Completed" &&
            ISBLANK('Fact Tasks'[task_starting_datetime])=FALSE &&
            'Fact Tasks'[task_key] = SELECTEDVALUE('Fact Tasks'[task_key]) &&
            'Fact Tasks'[task_starting_datetime] <= MaxDay && 'Fact Tasks'[task_starting_datetime] > MinDay)

VAR Result =
    CALCULATE(MEDIAN('Fact Tasks'[task_duration_min]),FilterContext)
       
RETURN
Result
 
 
It's working fine, and I get the results that I wanted, but the looking at the performance analyzer, the query is pretty slow, as it needs to load a lot of data in each step. 
 
Here find a sample of what the data should look like:
task keytask_starting_timetask_statustask_durationmedian
101/01/2023 18:32:00Completed2 
201/01/2023 19:39:00Completed3 
301/01/2023 22:31:00Completed4 
102/01/2023 09:21:00Completed1 
202/01/2023 19:21:00Completed3 
303/01/2023 05:55:00Completed5 
102/01/2023 22:44:20Failed61,5
202/01/2023 23:54:20Completed83
302/01/2023 23:58:20Completed75

 

Could someone help me out, please?

 

Thanks in advance for your help.

 

BR,

Sara

6 Replies

  • sebouier's avatar
    sebouier
    Frequent Visitor
    3 years ago
    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 it to a dimensional Calendar table.

    Here's what you should do : 

     

    By splitting your "task_starting_datetime" in one column "dates" and another column "times" in Power Query Editor, it will make your dataset faster to load. 

    Then you can easily link your "task_starting_date" to the calendar/dates table.

    Same for your "task_starting_time" linked to a "Times" table.

     

    Btw, 

     

    TimeKey = Times[Hour]*10000+Times[Minute]*100+Times[Second]

     

     

    It should make the calculations easier.

     

  • 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
      • 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
  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    Hi scaballerom 

    Is this a calculated column? If so, how about creating a measure and using it in a table visual?

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    • scaballerom's avatar
      scaballerom
      Helper I
      3 years ago

      hi AlB ,

       

      It's a measure; it works fine in a table, but the visual takes very long to load.

       

      BR,

      Sara