Dax Optmisation

[VizsWork]

Hi All 

Help!!!!! How can I make this dax code faster its too slow and i dont like it

HeadCount 12M (ColleagueType) =

VAR MonthSelected = SELECTEDVALUE(Calender[End of Month],EOMONTH(UTCTODAY(),-1))

VAR SummarizedTable =

    CALCULATE(SUMX(SUMMARIZE (

        FACTTABLE,

       FACTTABLE[RepMonth],

        /*FACTTABLE[Employee Type],*/

      "EmployeeDistinctCount",

        CALCULATE (

            DISTINCTCOUNT ( FACTTABLE[Employee Number] ),

            FILTER ( FACTTABLE, FACTTABLE[Name] = "People Operation" ))),

                [EmployeeDistinctCount]),ALLEXCEPT(FACTTABLE,Calender[End of Month]),

DATESBETWEEN(Calender[End of Month],EOMONTH(MonthSelected,-12),MonthSelected))

VAR MonthCount = CALCULATE((COUNTX(SUMMARIZE (

        FACTTABLE,

        FACTTABLE[RepMonth],

        "EmployeeDistinctMonth",

        CALCULATE (

            DISTINCTCOUNT ( FACTTABLE[RepMonth - Copy]),

            FILTER ( FACTTABLE, FACTTABLE[Name] = "People Operation" ))),[EmployeeDistinctMonth])),DATESBETWEEN(Calender[End of Month],EOMONTH(MonthSelected,-12),MonthSelected))

  VAR MovingAverage = DIVIDE(SummarizedTable,MonthCount,0)   

Return

MovingAverage

[v-frfei-msft]

Hi @VizsWork ,

We can use GROUPBY instead of  SUMMARIZE. Please refer to the third - party blog.

https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

[PattemManohar]

@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]

[VizsWork]

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

[pstueven]

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]

thanks your reply

[VizsWork]

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