topic Re: Measure Alert if not between two values in DAX Commands and Tips

Measure Alert if not between two values

Franco — Wed, 19 Oct 2022 09:28:05 GMT

Hello everybody,
i have the following problem.

The data model is as follows

I need to find a measure that indicates whether the Rolling Avg Sales 3 Months is out of range (fields from/to from table Models)

The Measure "Rolling Avg Sales 3 Months" is

Rolling Avg Sales 3 Months = VAR _lastDate = LASTDATE(DimDate[Date]) RETURN CALCULATE( AVERAGEX(VALUES(DimDate[Month]), [Total Sales]), FILTER( ALL(DimDate), DimDate[Date] <= _lastDate && DimDate[Date] >= DATEADD(_lastDate,-3,MONTH) ) )

thanks for your attention
Franco

Re: Measure Alert if not between two values

Greg_Deckler — Wed, 19 Oct 2022 10:04:13 GMT

Franco Maybe:

Measure = VAR __From = MAX('Table'[From]) VAR __To = MAX('Table'[To]) VAR __RollingAvg = [Rolling Avg Sales 3 Months] RETURN IF( __RollingAvg >= __From && __RollingAvg <= __To, "IN RANGE", "OUT OF RANGE")

Re: Measure Alert if not between two values

tamerj1 — Wed, 19 Oct 2022 10:12:22 GMT

Hi Franco

please try

NewMeasure = VAR From = CALCULATE ( SELECTEDVALUE ( Medels[From] ), CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH ) ) VAR To = CALCULATE ( SELECTEDVALUE ( Medels[To] ), CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH ) ) VAR RollingAvg = [Rolling Avg Sales 3 Months] RETURN IF ( RollingAvg >= From && RollingAvg <= To, "IN RANGE", "OUT OF RANGE" )

Re: Measure Alert if not between two values

Franco — Wed, 19 Oct 2022 12:12:12 GMT

Hi tamerj1 ,

thanks for the answer but if i put your measure in the table, each row is multiplied by each model

Re: Measure Alert if not between two values

Franco — Wed, 19 Oct 2022 12:15:59 GMT

Thanks Greg_Deckler

but even with your measure, the records are repeated for each model.

I only need the green lines....

Re: Measure Alert if not between two values

tamerj1 — Wed, 19 Oct 2022 12:47:01 GMT

Hi Franco
Please use

NewMeasure = VAR From = CALCULATE ( SELECTEDVALUE ( Medels[From] ), CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH ) ) VAR To = CALCULATE ( SELECTEDVALUE ( Medels[To] ), CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH ) ) VAR RollingAvg = [Rolling Avg Sales 3 Months] RETURN IF ( NOT ISBLANK ( RollingAvg ), IF ( RollingAvg >= From && RollingAvg <= To, "IN RANGE", "OUT OF RANGE" ) )

Re: Measure Alert if not between two values

Franco — Wed, 19 Oct 2022 13:03:15 GMT

works great!
Thanks!!!!!