Bad performance in calculating datediff without weekends/holidays

[TimProvelu]

I am trying to perform a datediff in DAX excluding certain days and hours. This datediff should give me the results in hours. I have checked the internet and found a lot of helpful solutions, but could not find anything yet that fits my case well enough to use it.

The datediff I want to perform are values on the same row in my fact table. For date1, the values of the date and time have been split in different colomns. DateTime2 is a date/time column. So I have something similar to this:

Key	Date1	Time1	DateTime2
1	01-02-2020	08:55:00	03-02-2020 07:30:00
2	03-06-2021	03:44:00	18-06-2021 05:00:00

The difficulty with this is that I have to filter a lot of values, including holidays, weekends and hours outside of scheduled hours.
What I did was create a helper table with Power Query in the following way:

For every date (Datum), there is an hour value. Meaning there are 24 datetime values per date. In this table, I flagged everything that is a weekend, holiday or time outside the workschedule within Power Query so I can easily filter these values with DAX.

I then get my date and time values from the records in my fact table and count all the records in the helper table with the appropriate filters. Here is the DAX sample:

DateDiffWithoutWeekends = 

VAR Date1 = MIN(Facttable[Date1])
VAR DateTime1 = MIN(Facttable[Date1])+MIN(Facttable[Time1])
VAR DateTime2 = MIN(Facttable[DateTime2])

RETURN
CALCULATE(
    COUNT(HulpDatumTijd[Datum]),
    FILTER(
            ALL(HulpDatumTijd),
            HulpDatumTijd[Datum]>=Date1 && HulpDatumTijd[Datum]<=DateTime2 &&
            HulpDatumTijd[DatumTijd]>DateTime1 && HulpDatumTijd[DatumTijd]<DateTime2 && HulpDatumTijd[is Weekend]=FALSE
    )
)

This works perfectly for smaller sets of 10s of rows and gives me the correct results. However, it gets extremely slow if I want to do this same calculation for 50 000+ records. Even so slow that I get timeouts in my visuals after 300 seconds. I think that it has something to do that I'm not using Vertipaq in an efficient way because I have to filter so many columns, but I don't understand what else I can improve.

BTW. Just because I'm trying to solve this in DAX, it wouldn't be a problem to do something extra in Power Query to assist the engine with the calculations.

Could you help me out with this issue? Or any tips which might improve the performance of the query?

Thank you very much and I greatly appreciate your help in advance!

[Anonymous · Accepted solution]

The rules are these:

1. You should avoid as much as possible calculated columns in DAX, especially in fact tables. 

2. When you have to put in a calc column, you should refrain as much as passible from using CALCULATE.

3. Do the calculation in the source or in Power Query.

There are too many reasons behind those statements to be able to expand on them here. If you want to know their source, please read The Definitive Guide to DAX by The Italians.

If you really want to do what you should not, you can try this:

DateDiffWithoutWeekends =
// First of all, you should calculate
// things once only, never repeat
// the same calculation
VAR MinDate = MIN( Facttable[Date1] )
var MinTime = MIN( Facttable[Time1] )
VAR MinDateTime1 = MinDate + MinTime
VAR MinDateTime2 = MIN( Facttable[DateTime2] )
RETURN
	COUNTROWS(
		FILTER(
			HulpDatumTijd,
			true()
			&& NOT( HulpDatumTijd[is Weekend] )
			&& HulpDatumTijd[Datum] >= MinDate
			&& HulpDatumTijd[Datum] <= MinDateTime2
			&& HulpDatumTijd[DatumTijd] > MinDateTime1
			&& HulpDatumTijd[DatumTijd] < MinDateTime2
		)
	)