Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
scaballerom
Helper I
Helper I

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 6
sebouier
Frequent Visitor


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 : 

sebouier_1-1673542613309.png

 

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
Community Champion
Community Champion

Hi @scaballerom 

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

 

SU18_powerbi_badge

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.

 

hi @AlB ,

 

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

scaballerom_1-1673538450283.png

 

BR,

Sara

tamerj1
Super User
Super User

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

Hi @tamerj1 ,

 

I tried your formula, but the following message appears:

scaballerom_0-1673538074423.png

BR,

Sara

 

@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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.