Any way to simplify this DAX code?

[olimilo]

Days Elapsed = 
	-- Days elapsed since the End Date up to the current date

	VAR Type1 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekend] = FALSE()
			)
		)
	
	VAR Type2 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekendType2] = FALSE()
			)
		)

	VAR Type3 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekendType3] = FALSE()
			)
		)

	VAR Type4 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekendType4] = FALSE()
			)
		)

	VAR Type5 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekendType5] = FALSE()
			)
		)

	VAR Type6 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekendType6] = FALSE()
			)
		)

	VAR Type7 = 
		COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				Dates[IsWeekendType7] = FALSE()
			)
		)

	VAR TAT = 
		IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 1, Type1,
			IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type2,
				IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type3,
					IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type4,
						IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type5,
							IF(LOOKUPVALUE(Weekdays[WeekendType], Weekdays[Country], 'Active Projects'[Country]) = 2, Type6, Type7)
						)
					)
				)
			)
		)
	
	RETURN SWITCH(
		TRUE(),
		'Active Projects'[CompleteByEndDate] < TODAY(), TAT - 'Active Projects'[Holidays (Elapsed)],
		'Active Projects'[CompleteByEndDate] > TODAY(), -1 * TAT - 'Active Projects'[Holidays (Elapsed)]
	)

It's pretty lengthy but it does the same thing overall, the only difference is that it uses a different column for the final FILTER argument based on the WeekendType category of the country (eg: Sat/Sun weekend, Fri/Sat weekend).

[Phil_Seamark]

Hi @olimilo

Looks do-able.  Any chance you can post some dummy data for your two tables to help us understand your data  little better.

[Phil_Seamark]

Oh and a slightly more radical idea is just to simplify your data model.  So pivot your 7 columns into rows.

So rather than 1 row with 7 columns, pivot (or unpivot in the Query Editor) so you have 7 rows and fewer columns.  This will make your DAX alot simpler.

[olimilo]

Hi @GilbertQ & @Phil_Seamark!

This is the Dates table:

Date	Day Of Week	IsWeekend	IsWeekendType2	IsWeekendType3	IsWeekendType4	IsWeekendType5	IsWeekendType6	IsWeekendType7
1/1/2013	Tuesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/2/2013	Wednesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/3/2013	Thursday	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE
1/4/2013	Friday	FALSE	TRUE	TRUE	TRUE	FALSE	TRUE	FALSE
1/5/2013	Saturday	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE
1/6/2013	Sunday	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE
1/7/2013	Monday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/8/2013	Tuesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/9/2013	Wednesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/10/2013	Thursday	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE

I actually tried to use a simpler data structure, but I've been having difficulty dealing with Dates and Weekend Types (here and here). I've tried to twist and turn the data structure but this is the best that I could do (and it's been the one that is most often demonstrated as an example when counting date ranges).

[Eric_Zhang]

---

@olimilo wrote:

Hi @GilbertQ & @Phil_Seamark!

This is the Dates table:

Date	Day Of Week	IsWeekend	IsWeekendType2	IsWeekendType3	IsWeekendType4	IsWeekendType5	IsWeekendType6	IsWeekendType7
1/1/2013	Tuesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/2/2013	Wednesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/3/2013	Thursday	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE
1/4/2013	Friday	FALSE	TRUE	TRUE	TRUE	FALSE	TRUE	FALSE
1/5/2013	Saturday	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE
1/6/2013	Sunday	TRUE	FALSE	FALSE	TRUE	TRUE	FALSE	FALSE
1/7/2013	Monday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/8/2013	Tuesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/9/2013	Wednesday	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
1/10/2013	Thursday	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE

I actually tried to use a simpler data structure, but I've been having difficulty dealing with Dates and Weekend Types (here and here). I've tried to twist and turn the data structure but this is the best that I could do (and it's been the one that is most often demonstrated as an example when counting date ranges).

---

@olimilo

I think you may not need those weekendtypes. How about a measure as below? You may need an extra lookup table for contries and week end day numbers(1-7).

Days Elapsed = 
	-- Days elapsed since the End Date up to the current date

	VAR weekendday1 = WEEKDAY(TODAY()-1) //lookup weekend number from the lookup table
        VAR weekendday2= Weekday(Today())
        VAR TAT = COUNTROWS(
			FILTER(
				ALL(Dates),
				Dates[Date] >= 'Active Projects'[CompleteByEndDate] &&
				Dates[Date] <= TODAY() &&
				WeekDay(Dates[Date])<>weekendday1 &&
                                WeekDay(Dates[Date])<>weekendday2

			)
	RETURN SWITCH(
		TRUE(),
		'Active Projects'[CompleteByEndDate] < TODAY(), TAT - 'Active Projects'[Holidays (Elapsed)],
		'Active Projects'[CompleteByEndDate] > TODAY(), -1 * TAT - 'Active Projects'[Holidays (Elapsed)]
	)

[GilbertQ]

Hi @olimilo

Can I ask what changes between each Dates[IsWeekendTypeX]?