topic Re: Filtered table help in DAX Commands and Tips

Filtered table help

dgwoodard — Sat, 03 Apr 2021 20:21:48 GMT

Hi all,
I hope that I can gain some advice on how to proceed in calculating a simple moving average with a period value depenant upon the variable parameter from a slicer.

The measure I currently have is as follows but the return is blank. The intention is to plot the moving average over top of the original data points to smooth out the volility. The catch is that I am not working with dates but rather with an indexed data set of various measurements.

DIAM_SMA_100 =

var PeriodValue = SELECTEDVALUE('Period Sample'[Period Sample])

var SelectedValue = SELECTEDVALUE('Table'[Index])

Return

AVERAGEX(

FILTER('Table',

MAX('Table'[Index])=SelectedValue &&

MIN('Table'[Index])= SelectedValue - PeriodValue),

CALCULATE(SUM('Table'[Measured Value])

))

I am still new to DAX and I believe my filter function is filtering evrything out (?). I would appreciate any insight into resolving or of an alternate method.
Thank you!

Re: Filtered table help

AlB — Sat, 03 Apr 2021 20:37:07 GMT

Hi dgwoodard

I'd need to see more of the model but try this:

DIAM_SMA_100 V2 = VAR PeriodValue = SELECTEDVALUE ( 'Period Sample'[Period Sample] ) VAR SelectedValue = SELECTEDVALUE ( 'Table'[Index] ) RETURN CALCULATE ( AVERAGE ( 'Table'[Measured Value] ), 'Table'[Index] <= SelectedValue, 'Table'[Index] >= SelectedValue - PeriodValue )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Filtered table help

dgwoodard — Sat, 03 Apr 2021 21:10:26 GMT

That was quick! Thank you!