Dax Optmisation

Question

Hi All&nbsp;Help!!!!! How can I make this dax code faster its too slow and i dont like it&nbsp;HeadCount 12M (ColleagueType) =VAR MonthSelected = SELECTEDVALUE(Calender[End of Month],EOMONTH(UTCTODAY(),-1))VAR SummarizedTable =&nbsp;&nbsp;&nbsp; CALCULATE(SUMX(SUMMARIZE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FACTTABLE,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FACTTABLE[RepMonth],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /*FACTTABLE[Employee Type],*/&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "EmployeeDistinctCount",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DISTINCTCOUNT ( FACTTABLE[Employee Number] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( FACTTABLE, FACTTABLE[Name] = "People Operation" ))),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [EmployeeDistinctCount]),ALLEXCEPT(FACTTABLE,Calender[End of Month]),DATESBETWEEN(Calender[End of Month],EOMONTH(MonthSelected,-12),MonthSelected))VAR MonthCount = CALCULATE((COUNTX(SUMMARIZE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FACTTABLE,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FACTTABLE[RepMonth],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "EmployeeDistinctMonth",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DISTINCTCOUNT ( FACTTABLE[RepMonth - Copy]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( FACTTABLE, FACTTABLE[Name] = "People Operation" ))),[EmployeeDistinctMonth])),DATESBETWEEN(Calender[End of Month],EOMONTH(MonthSelected,-12),MonthSelected))&nbsp;&nbsp; VAR MovingAverage = DIVIDE(SummarizedTable,MonthCount,0)&nbsp;&nbsp;&nbsp;ReturnMovingAverage

pstueven · Answer

It might be helpful to tell people what the code actually does or should do. This way not everybody needs to go through it.

vizswork · Answer

I want to be able to do a monthly distinct count of employee number for any year and so I can calculate the average employee for the year.
To archive this, I created a summarized table and grouped it by month with each row contain the distinct employee for the month after which I summed each row to give me the year total

vizswork · Answer

thanks your reply

pattemmanohar · Answer

VizsWork Could you please throw some sample test data and expected output, so that it will be really helpful to suggest an appropriate solution.

vizswork · Answer

RepMonthCountNov-187Dec-186Jan-197Feb-197Mar-196Average (Sum by month/no of Month)7&nbsp;&nbsp;Distint Month count5Distint Employee count (Incorrect Result)10&nbsp;&nbsp;

Forum Discussion

Dax Optmisation

7 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX

RepMonth	Count
Nov-18	7
Dec-18	6
Jan-19	7
Feb-19	7
Mar-19	6
Average (Sum by month/no of Month)	7

Distint Month count	5
Distint Employee count (Incorrect Result)	10