Interative count with date comparison using Direct Query

[jseek]

Hello!

I am currently switching a model over to direct query and encounting a problem with one formula, but it's an important one. My current model calculates the average number of people working during any selected timeframe. This is calculated by taking a daily count of all people employed and dividing it by the number of days. here is the formula that works in PBI NOT in DQ mode:

Average Active Assignments = 
roundup(
	divide(
		sumx(
			CALCULATETABLE(Assignments,ALL(Dates),Assignments[HeadcountFlag]=1),
			COUNTROWS(
				FILTER(
					Dates,
					Dates[Date] >= Assignments[Worker Start Date] && Dates[Date] < Assignments[Worker End Date] && Dates[Date]<=now()+1
				)
			)
		),
		CALCULATE(
			COUNTROWS(Dates),
			FILTER(
				DATES,
				Dates[Date]<=NOW()+1
			)
		)
	),
	0
)

The problem I am finding is that DQ does not allow you to compare the dates with each other, only one column is allowed for a TRUE/FALSE comparison. I've been trying to find a way around this and have yet to find something. Does anyone know of a work-around?

[v-caliao-msft]

@jseek

We can create measure in Direct Query model. In your scenario, please also share us some example for your data model, so that we could build some testing data to reproduce your issue here.

In addition, here are some issues based on the formula and the error message that you have shared:

1. For Calculatetable function, I don’t think with All(dates) as a filter will work(if dates indicates a table here);
2. Have you checked the table relationship after change to Direct Query?

Regards,

Charlie Liao