topic DAX optimization in DAX Commands and Tips

DAX optimization

scaballerom — Wed, 11 Jan 2023 10:58:02 GMT

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

Re: DAX optimization

tamerj1 — Wed, 11 Jan 2023 12:46:51 GMT

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

Re: DAX optimization

AlB — Wed, 11 Jan 2023 12:49:14 GMT

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.

Re: DAX optimization

scaballerom — Thu, 12 Jan 2023 15:41:41 GMT

Hi tamerj1 ,

I tried your formula, but the following message appears:

BR,

Sara

Re: DAX optimization

scaballerom — Thu, 12 Jan 2023 15:47:39 GMT

hi AlB ,

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

BR,

Sara

Re: DAX optimization

tamerj1 — Thu, 12 Jan 2023 17:05:58 GMT

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

Re: DAX optimization

sebouier — Thu, 12 Jan 2023 17:13:44 GMT

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.