Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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.
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 🙂