Forum Discussion

sirros_iot's avatar
sirros_iot
Helper III
8 years ago
Solved

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

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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

    • sirros_iot's avatar
      sirros_iot
      Helper III
      8 years ago

      Thank you for the answer! It works!

      Best Regards,
      Diego

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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