Forum Discussion

amien's avatar
amien
Helper V
10 years ago

HeadCount in Power Query M

Headcount = CALCULATE (DISTINCTCOUNT(Contracts[id]), FILTER (Contracts, Contracts[Start Date] <= CALCULATE(MAX (Calendar[Date]))), FILTER (Contracts, Contracts[End Date] >= CALCULATE(MIN(Calendar[Dat...
MattAllington's avatar
MattAllington
Community Champion
10 years ago

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's avatar
amien
Helper V
10 years ago

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 :)