Locking measure values

[sirros_iot]

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

[v-jiascu-msft · Accepted solution]

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

Best Regards,

Dale

[Anonymous]

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

[sirros_iot]

Thank you for the answer! It works!

Best Regards,
Diego