cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jseek Helper I
Helper I

Interative count with date comparison using Direct Query

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?

1 REPLY 1
Microsoft
Microsoft

Re: Interative count with date comparison using Direct Query

@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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.