Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Franco
Frequent Visitor

Measure Alert if not between two values

Hello everybody,
i have the following problem.

 

The data model is as follows

image.png

 

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)
        )
    )

 

image.png

 

thanks for your attention
Franco

1 ACCEPTED SOLUTION

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" )
    )

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

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" )

Hi @tamerj1 ,

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

image.png

 

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" )
    )

works great!
Thanks!!!!!

Greg_Deckler
Super User
Super User

@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")

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler 

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

 

I only need the green lines....

image.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors