DAX optimization

[scaballerom]

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 key	task_starting_time	task_status	task_duration	median
1	01/01/2023 18:32:00	Completed	2
2	01/01/2023 19:39:00	Completed	3
3	01/01/2023 22:31:00	Completed	4
1	02/01/2023 09:21:00	Completed	1
2	02/01/2023 19:21:00	Completed	3
3	03/01/2023 05:55:00	Completed	5
1	02/01/2023 22:44:20	Failed	6	1,5
2	02/01/2023 23:54:20	Completed	8	3
3	02/01/2023 23:58:20	Completed	7	5

Could someone help me out, please?

Thanks in advance for your help.

BR,

Sara

[sebouier]

---

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.

[AlB]

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]

hi @AlB ,

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

BR,

Sara

[tamerj1]

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]

Hi @tamerj1 ,

I tried your formula, but the following message appears:

BR,

Sara

[tamerj1]

@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