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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
sirros_iot
Helper III
Helper III

Locking measure values

Hello all!

I have this example sheet here..
Data RealTimeData

And I have this measure here...

Measure = 
    CALCULATE( SUM(RealTimeData[intervalo]); FILTER( ALL(RealTimeData); RealTimeData[_ts] = MAX( RealTimeData[_ts])); ALL(RealTimeData); ALLEXCEPT(RealTimeData;RealTimeData[sensor])) 

I would like to get the last value from [intervalo], filtering by [sensor], and using [_ts] as order to get the last one.

With that Measure I have this
sensor        Measure
A                 2
B                 5
C                 6

But this is filtering the value as I am changing the [_ts] range in the page fiter. And I want to have the Measure locked.
One more thing... I would like to see only the values that have like [_ts] < 100. I someone could help me, I would appreciate.

Thanks,
Diego

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi Diego,

 

I modified your measure. It works.

Measure =
VAR maxTs =
    CALCULATE (
        MAX ( RealTimeData[_ts] ),
        FILTER (
            ALLEXCEPT ( RealTimeData, RealTimeData[sensor] ),
            'RealTimeData'[_ts] < 100
        )
    )
RETURN
    CALCULATE (
        MAX ( RealTimeData[intervalo] ),
        FILTER (
            ALLEXCEPT ( RealTimeData, RealTimeData[sensor] ),
            RealTimeData[_ts] = maxTs
        )
    )

Another approach.

Measure 2 =
CALCULATE (
    MAXX ( TOPN ( 1, 'RealTimeData', [_ts], DESC ), [intervalo] ),
    FILTER (
        ALLEXCEPT ( 'RealTimeData', RealTimeData[sensor] ),
        'RealTimeData'[_ts] < 100
    )
)

Locking_measure_values

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi Diego,

 

I modified your measure. It works.

Measure =
VAR maxTs =
    CALCULATE (
        MAX ( RealTimeData[_ts] ),
        FILTER (
            ALLEXCEPT ( RealTimeData, RealTimeData[sensor] ),
            'RealTimeData'[_ts] < 100
        )
    )
RETURN
    CALCULATE (
        MAX ( RealTimeData[intervalo] ),
        FILTER (
            ALLEXCEPT ( RealTimeData, RealTimeData[sensor] ),
            RealTimeData[_ts] = maxTs
        )
    )

Another approach.

Measure 2 =
CALCULATE (
    MAXX ( TOPN ( 1, 'RealTimeData', [_ts], DESC ), [intervalo] ),
    FILTER (
        ALLEXCEPT ( 'RealTimeData', RealTimeData[sensor] ),
        'RealTimeData'[_ts] < 100
    )
)

Locking_measure_values

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Dale,

 

I have similar issue...

 

I have the data as shown below( this is just a sample)

AsOfDate SectorName Actual Plan Periodicity Level

7/1/2018 Sector1 10 8 YEP 0

7/2/2018 Sector2 20 18 YEP 1

2/1/2019 Sector1 30 25 YEP 1

5/1/2019 Sector1 40 38 DAILY 2

5/1/2019 Sector3 50 49 DAILY 2

5/1/2019 Sector1 60 55 YEP 1

5/1/2019 Sector2 40 45 YEP 1

 

I would like to get the SUM(Actual) and display in the CARD for a very first time when the report is loaded and this value should not be changed even when applied filter or slice & dice. It should be locked.

This Sum(Actual) is calculated based on Latest AsOfDate and Periodicity='YEP'( these two conditions are fixed).

Besides above, they got filter for this report based on SectorName, but this filter is only for Report to slice & dice. 

 

Highly appreciate for your help.

 

thanks

Nagaraj

 

 

 

Thank you for the answer! It works!

Best Regards,
Diego

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors