HeadCount in Power Query M

[amien]

Headcount = CALCULATE (DISTINCTCOUNT(Contracts[id]), FILTER (Contracts, Contracts[Start Date] <= CALCULATE(MAX (Calendar[Date]))), FILTER (Contracts, Contracts[End Date] >= CALCULATE(MIN(Calendar[Date]))))

I have this code in DAX for HeadCount. Works fine, but when you start selecting dimensions, there is a pause of 3-4 seconds before the result is shown. I wonder if doing this in M would be faster.

Anyone got an example of this? 

[MattAllington]

How big is your contracts table?  Filter on data tables can be slow. There is a better way - I know because Marco Russo challenged me with a homework question last week after I wrote this post. http://exceleratorbi.com.au/double-calculate-solves-sumx-problem/

I havent had a chance to sit and think it through, but note the comment from Andrey at the above post - that could be a better answer. 

If you note a better result, please post back. 

[amien]

The table looks now like this:

id, startime, endtime

there are like 200.000 records. I guess it needs to be unpivot in some way. In did this with another bi tool, and it generated 40mil records. This was a record for each day for each id as long as it was running. But a month M-calculation will also be fine i guess.

I will test the Andrey code 🙂