topic Re: Is there a faster Average? in DAX Commands and Tips

Is there a faster Average?

Anonymous — Thu, 29 Aug 2019 17:17:02 GMT

Running this in DAX Studio shows me two queries that run one that sums up the Usage and I assume the other to count for the division needed to do the Average. . . Can I make this any faster, with calling Marco?

DEFINE
    MEASURE Usage[New KanBan Qty] =
        VAR CurrentDate =
            TODAY ()
        VAR ThreeMthSmoothed =
            CALCULATE (
                AVERAGEX ( Usage, Usage[Smoothed Usage] ),
                FILTER ( Usage, Usage[Usage Date] >= CurrentDate - 90 )
            ) * -1
        RETURN
            (
                ThreeMthSmoothed * MAX ( IMAG[JIT Leadtime] )
                    + (
                        ( STDEV.P ( Usage[Smoothed Usage] ) * [ServiceFactor] )
                            * SQRT ( MAX ( IMAG[JIT Leadtime] ) )
                    )
            )
EVALUATE
ADDCOLUMNS (
    SUMMARIZE ( Usage, Usage[Part Number] ),
    "Stuff", [New KanBan Qty]
)

Re: Is there a faster Average?

Anonymous — Thu, 29 Aug 2019 17:45:23 GMT

It looks like your Usage table is a Fact table with about 2millionn rows. I would get a data table and use that in the filter statement as a start since you never want to filter a fact table..

..  FILTER ( Usage, Usage[Usage Date] >= CurrentDate - 90 )

Re: Is there a faster Average?

Anonymous — Thu, 29 Aug 2019 18:08:11 GMT

Nick you're absolutely correct, I'm a slow learner - - - I already had a date table, changed the filter and it is 400% faster - Thanks!

DEFINE
    MEASURE Usage[New KanBan Qty] =
        VAR CurrentDate =
            TODAY ()
        VAR ThreeMthSmoothed =
            CALCULATE (
                AVERAGEX ( Usage, Usage[Smoothed Usage] ),
                FILTER ( Dates, Dates[Date] >= CurrentDate - 90 )
            ) * -1
        RETURN
            (
                ThreeMthSmoothed * MAX ( IMAG[JIT Leadtime] )
                    + (
                        ( STDEV.P ( Usage[Smoothed Usage] ) * [ServiceFactor] )
                            * SQRT ( MAX ( IMAG[JIT Leadtime] ) )
                    )
            )
EVALUATE
ADDCOLUMNS (
    SUMMARIZE ( Usage, Usage[Part Number] ),
    "Stuff", [New KanBan Qty]
)

Re: Is there a faster Average?

Anonymous — Thu, 29 Aug 2019 18:26:21 GMT

Nothing to worry about, it all comes with time :)

just remember, if you find yourself using Filter( Fact Table..) you made a wrong turn somewhere. Could be in the data model or just your understanding on how this whole dax thing works. You want to iterate the smaller tables, and then send that to the bigger fact tables to be filtered. Good luck!